Why doesn't SQL Server use an index for a very similar date query?

Question

I have a table on SQL Server with approximately 1 million rows. It has an identifier (PK), status (int), and a datetime column. I also created an index in the datetime column.

Now I have discovered an effect that I do not understand.

SELECT status FROM table WHERE dateTime BETWEEN '2010-01-01T00:00:00' AND '2010-01-02T12:00:00' 

This statement returns 3664 rows. It runs for about 150 ms, and the execution plan shows that it performs an index search with a key search.

Now, if I change it as follows (just change the hour from 12 to 13):

 SELECT status FROM table WHERE dateTime BETWEEN '2010-01-01T00:00:00' AND '2010-01-02T13:00:00' 

This statement returns 3667 rows. It works for about 600 ms, and the billing plan shows that it uses the primary key!

I just don't get it. For 3667 and more lines, it always uses the primary key, even if the search is much faster.

Is there any explanation?

Answer 1

status not included in the datetime index, so to get this value you need to search for keys for each corresponding row.

As the range grows (and, consequently, the number of search queries), it calculates that it will scan the entire (covering) clustered index faster, avoiding the search. Perhaps wrong in your case. The point at which it switches from one plan to another is called a polling point .

You should check if the actual number of rows with the actual number of rows is calculated (perhaps some rows that would correspond to the range have been deleted since the last statistics update).

Or maybe index scans are more expensive than cost assumptions due to high fragmentation or for some other reason, the assumptions made do not reflect the actual relative performance in your environment.