Index not being used for date predicate

Question

I'm doing abit of self learning on indexes & performance in a SQL Server. I'm using stackoverflow's database as my sample DB for this activities.

For my testing, I'm using the Users table & create a non-clustered index on the CreationDate column which is a datetime data type.

I then did a simple query to search for users where the creation date is between certain dates. However I notice optimizer uses the primary key clustered index instead of the non-c index. Any reason why optimizer behaves that way? Is it because the predicate is a range of dates?

I then tried to force the optimizer to use index and I notice the optimizer does uses the non-c index but it also perform a keylookup on the PK. Any reason for this behaviour?

Answer 1

For exact answer to your question we need to see your SELECT list that you are missing in your question.

Your index with any SELECT list will be used only when your WHERE condition is selective enough, for example you are looking for some precise date(s). In that case Server will use your index to retrieve some index rows and return to the base table (clustered index) to get other fields from your SELECT list. It will do a small number of lookups.

Your actual WHERE condition is not selective, as you see from your execution plan you just select 99% of your table, and your SELECT list contains other fields, not only CreationDate and id - the only two columns that can be found in your non-clusterd index. This means that to get them Server must return to clustered index so many times how many rows are in your result, 2365865 times, and they will not be continuos reads but expensive random reads. So it opts to read the table only one time.

The cause here is not "the predicate is a range of dates", but "the range is so wide that covers the whole table" + "some of the field from your SELECT are not present in your index". Try another range that contains only some days, not 7 years, and your index will be used even if some fields from your SELECT are missing in it. Alternatively, change your index to include all the missing fileld from your SELECT, and this index will be used even for 7 years because there is no need to make lookups at all.