I'm looking for a way to improve the efficiency of the sort for the attached plan. There is a clustered index which includes all the columns which are returned, and the index is created with the columns in the order whcih they are sorted in.
By Andy 11 asked May 07, 2013 at 12:43 PM
The section of the plan you are interested in is:
The plan was not captured from a SQL Server 2012 instance, so there is no indication if the slightly low input cardinality estimation to the sort resulted in an insufficient memory reservation and a spill to physical disk. If this query is much slower than expected, trace the Sort Warnings event using Profiler to determine if any of the sorts are spilling.
Eliminating the sort
The sort is on (C1, C2, C3) with C5 as an additional output. The question mentions that a table has a clustered index on (C1, C2, C3) and from context it seems most likely that statement refers to T2 in the anonymized plan.
In any event, eliminating the sort requires that we provide the query processor with an efficient way to satisfy all the joins in the subtree (tables T1-T5) preserving (C1, C2, C3) key order; or alternatively by sorting a much smaller number of rows higher in the subtree - as the optimizer has chosen to do with the joins between T1, T4, and T5.
As things stand, the optimizer has chosen hash joins between the (T1, T4, T5) subtree, T3, and then T2. Hash join does not preserve any key order (from either input) so an explicit sort is required before applying the (presumably
To remove the sort, you would need to provide suitable indexing on tables T2 and T3 such that an order-preserving Merge or Nested Loops join is selected instead of Hash. To help determine the sort order needed, you could try forcing merge joins temporarily with an
Ultimately, removing the expensive-looking sorts is a technical exercise that requires a detailed analysis of the available index orders and the order-preserving properties of the available query plan operators.
If you are able to provide a plan that is not anonymized, and DDL for the tables and indexes concerned, I might be able to offer more specific advice and/or reasons why the optimizer chooses the explicit sorts in your case.
If the query does not have to be all one statement, you might also consider materializing the subtree below the sort in a temporary table. This will give the optimizer accurate cardinality information for the sort, avoiding any spills you might be encountering. It all depends on what exact problem you are looking to solve here :)
By SQLkiwi ♦ 6.6k answered May 07, 2013 at 02:22 PM