This query is taking nearly 12 minutes to run on our MSSQL Server making it the longest, I will be happy to recieve some feedback on how can I optimize it.

avatar image By Kaloyan 1 asked Oct 31, 2013 at 12:43 PM
more ▼
(comments are locked)
avatar image Jonathan Kehayias Oct 31, 2013 at 09:08 PM

The anonymized plan you posted removed the query from the plan so it's impossible to offer suggestions for how to optimize it. Can you post a non-anonymized plan?

10|10000 characters needed characters left

1 answer: sort voted first

The hash match left outer join is processing a lot of rows. If it isn't possible to reduce the workload by better indexing, being more selective in the query, performing aggregation early, or some other improvement that we would need the query text to advise about, you could look at enabling parallel execution.

There is a residual predicate on the Table 3 index scan. You might improve performance a little bit by providing a seek-able index for that part of the query. There is no residual on the much larger Table 4 index scan (which returns every row in the table). The speed of this part of the query plan is likely limited by the number of processors available right now.

Hash Join

The next part of the plan has more serious issues:

Nested Loops Join

The table spool is rewound (replayed) 48 million times, and there is a residual predicate on the Nested Loops join iterator itself, which is evaluated on all 575 million rows!

You need to look very carefully at the logic in this area of the query to see how you can give the optimizer better information to work with, and/or a better option for the execution engine. The anonymized plan doesn't show the specific predicates, so it's hard to know why this particular arrangement was chosen, but you need to improve this as a priority.


The final part of the plan that bothers me is shown above. The very large input to the first aggregate is one of the reasons this query requires a memory grant of almost 2GB. It is quite unusual to see three aggregation operations in a row (I include Distinct Sort as an aggregation). You need to look carefully at the query logic responsible for these operations too. Not only is the operation intrinsically expensive, the query may have to wait for ~2GB memory to be freed up.

There are many serious problems with the query plan you uploaded. If you want more detailed help, you will have to provide more detail, as Jonathan requested.

sp.png (18.4 kB)
sp.png (20.2 kB)
sp.png (16.1 kB)
avatar image By SQLkiwi ♦ 6.6k answered Nov 05, 2013 at 06:25 AM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question



asked: Oct 31, 2013 at 12:43 PM

Seen: 502 times

Last Updated: Nov 05, 2013 at 06:25 AM