Can anybody think of a way to optimise the Sorts in the plan attached?
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 RANK or DENSE_RANK) windowing function.
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 OPTION (MERGE JOIN) hint. This will typically introduce sorts before the merge joins where necessary, allowing you to see more clearly the reasons the optimizer chose hash join instead. Be sure to check the merge joins to see if any would be the much less efficient many-to-many join type.
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 🙂