SQL Server 2005 doing full table Sort before Index Scan

Question

I'm having a problem with ordering performance in my SQL Server 2005 database. Lets say 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 to execute. The execution plan of this query is as follows (can't post images yet):

select (cost: 0%) -> Sort (cost: 11%) -> Clustered Index Scan (cost: 89%)

If I remove the 'order by' clause, the query will complete in ~4 ms, like expected.

Why is the SQL Server doing a sort before getting the requested rows? It doesn't make sense to me. Why not getting the 7 rows first and sorting only those? Am I missing something, like a database configuration, or is this an expected behavior?

I could use an inner select, like the below, to force the engine to get the rows first and then order, which would return the rows in ~6 ms, but since we are using the EF this wouldn't be a good solution for us (we could sort the results in memory, but we are using LoadWith options for some of the entities that generate SQL code with sorts, and that code is also suffering from the same 'order by' problem).

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

I have tested some indexing is the sorted columns, which fastened thing, but only because the columns are already sorted. Seems like a clunky solution...

Answer 1

Firstly, I don't know why it's doing it! Having said that, here are a couple of things you have possibly already tried.

• Query Hints: Have you tried using any of the various query hints documented 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? These are the things I usually try.

• Update statistics: Either by running UPDATE STATISTICS tablename or EXEC sp_updatestats to update stats for the whole database.

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