SQL Server 2008 datetime index performance bug

We are using SQL Server 2008 R2, and have a very large (100M+ rows) table with a primary id index, and a datetime column with a nonclustered index. We are seeing some highly unusual client/server behavior based upon the use of an order by clause specifically on a indexed datetime column.

I read through the following post:
https://stackoverflow.com/questions/1716798/sql-server-2008-ordering-by-datetime-is-too-slow
but there is more going on with the client/server than what is begin described here.

If we run the following query (edited to protect some content):

select * 
from [big table] 
where serial_number = [some number] 
order by test_date desc

The query times-out every time. In the SQL Server Profiler the executed query looks like this to the server:

exec sp_cursorprepexec @p1 output,@p2 output,NULL,N'select * .....

Now if you modify the query to, say this:

declare @temp int;
select * from [big table] 
where serial_number = [some number] 
order by test_date desc

The the SQL Server Profiler shows the executed query looks like this to the server, and it WORKS instantly:

exec sp_prepexec @p1 output, NULL, N'declare @temp int;select * from .....

As a matter of fact, you can even put an empty comment ('--;') instead of a unused declare statement and get the same result. So initially we were pointing to the sp pre-processor as the root cause of this issue, but if you do this:

select * 
from [big table] 
where serial_number = [some number] 
order by Cast(test_date as smalldatetime) desc

It works instantly as well (you can cast it as any other datetime type), returning the result in milliseconds. And the profiler shows the request to the server as:

exec sp_cursorprepexec @p1 output, @p2 output, NULL, N'select * from .....

So that somewhat excludes the sp_cursorprepexec procedure from the full cause of the issue. Add to this the fact that the sp_cursorprepexec is also called when no 'order by' is use

There's no mystery, you get a good(er) or (really) bad plan at basically random because there is no clear cut choice for the index to use. While compelling for the ORDER BY clause and thus avoid the sort, you non-clustered index on the datetime column is a very poor choice for this query. What would make a much better index for this query would be one on (serial_number, test_date). Even better, this would make a very good candidate for a clustered index key.

As a rule of thumb time series should be clustered by the time column, because the overwhelming majority of requests are interested in specific time ranges. If the data is also inherently partitioned on a column with low selectivity, like it seems to be the case with your serial_number, then this column should be added as the leftmost one in the clustered key definition.

StackExchange Database Administrators Q#25761, answer score: 6