SQL Server 2005 makes a full Sort table before index scan

Question

I had a problem with order performance in my SQL Server 2005 database. Suppose I have the following query:

select id, versionId, orderIndex from Forms_Page where versionId = 'AFCF4921-31B4-44C1-B3A7-913910F7600E' order by orderIndex 

This query will return 7 rows and take ~ 23 seconds. The execution plan for this request is as follows (so far the images cannot be sent):

select (cost: 0%) → Sort (cost: 11%) → Clustered index scan (cost: 89%)

If I delete the order by clause, the request will complete in ~ 4 ms, as expected.

Why does SQL Server do a sort before retrieving the requested rows? That doesn't make sense to me. Why not get the first 7 rows and sort only those? Am I missing something like database configuration, or is this the expected behavior?

I could use the internal selection, as shown below, to get the engine to get the rows first and then arrange them to return the rows in ~ 6 ms, but since we use EF, this will not be a good solution for us (we could sort the results in memory, but we use the LoadWith options for some objects that generate SQL sort code, and this code also suffers from the same problem "in order").

 select * from( select id, versionId, orderIndex from Forms_Page where versionId = 'AFCF4921-31B4-44C1-B3A7-913910F7600E' ) T order by T.orderIndex 

I tested some indexing - these are sorted columns that are pinned, but only because the columns are already sorted. Sounds like clumsy decisions ...

Answer 1

Firstly, I do not know why this is so! Having said that, here are a few things you may have already tried.

• Query Tips: Have you tried any of the hints described here? http://msdn.microsoft.com/en-us/library/ms181714.aspx . In particular, the RECOMPILE option.

• What about the usual steps of DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS ? This is what I usually try.

• Update statistics: either by running the UPDATE STATISTICS tablename or EXEC sp_updatestats to update statistics for the entire database.

Finally, and perhaps not appropriate in your case, but there is a good explanation of the table hints: http://blog.sqlauthority.com/2009/11/19/sql-server-understanding-table-hints-with-examples/