SQL Server Rules for Optimizing Sort?

Question

I have a convoluted MSSQL 2008 query, and the show plan has 40% of the work in a sort operation (there are several mil rows and the query does a top 15000), the rest of the plan is index seeks and scans.

If I remove the TOP clause, the query goes down to a second. But I need the TOP. What are some rules of thumb for smartly optimizing this SORT need?

Wait, the order by is actually a subquery (linq-to-sql made this trash query). — Snowy, Jul 08 '11 at 14:56
Can you show us an [execution plan](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan)? — Justin, Jun 04 '13 at 14:55

score 0 · Answer 1 · answered Jul 07 '11 at 19:22

0

All of the columns you sort on should be indexed. This alone shoud decrease the need for SQL Server to spend time sorting while executing the query (because they will already be sorted via the index)

The only downside is that more indexes = slower inserts.

answered Jul 07 '11 at 19:22

Neil N

24,862
16
85
145

True, but indexes can't be guaranteed to be used -- especially if the `ORDER BY` includes columns wrapped in functions. – OMG Ponies Jul 07 '11 at 19:29

SQL Server Rules for Optimizing Sort?

1 Answers1