How can I reduce the impact on the sort operations?

Eddie 2014-02-08 04:54:58

SQLkiwi 2014-02-08 06:57:29
There's only so much to be said based on an anonymized estimates-only query plan. If you can provide a non-anonymized plan with post-execution information, ideally run from Plan Explorer directly, that would be helpful.

In general, you can avoid sorts by providing helpful indexing, and by careful analysis of the query plan to determine any operators (like hash join) that do not preserve order. A skilled query tuner can sometimes determine alternative indexing or query syntax that will make maximum use of preserved ordering.

Minimizing the impact of any sorts that are needed comes down to sorting as little as necessary, and ensuring the sort is performed in memory rather than spilling sort runs to tempdb disk. You can monitor the Sort Warnings profiler event to check if your sorts are spilling to disk. In SQL Server 2012, these warnings are included in the post-execution query plan.

Your query appears to feature multiple aggregates, at least one of which is a distinct aggregate. SQL Server 2012 contains an optimization that might help you there. Regardless, it is sometimes possible to rewrite the query using partial aggregations to reduce the need for spooling and sorting:

http://blogs.msdn.com/b/sqlqueryprocessing/archive/2008/09/22/distinct-aggregation-considered-harmful.aspx
http://sqlblog.com/blogs/paul_white/archive/2011/12/04/is-distinct-aggregation-still-considered-harmful.aspx

It's possible your query contains a ROLLUP or CUBE instead, in which case the links above are not relevant. It's hard to guess from an anonymized plan.

Aside from the huge spools and sorts, there are a number of other concerning features of this estimated execution plan:

  1. The merge join is an inefficient many-to-many join.
  2. The plan executes over large sets but is limited to using a single processing unit.

Estimated cost percentages are just that – estimates. Don't focus your tuning efforts exclusively on them.