Suggestions for improving performance of query

This query takes about 6-7 hours to run on an 8 core 2.2 GHz, 12 GB RAM server. Anyway to optimize the hash joins? Why paralleism is not invoked on this query?

avatar image By S S 1 asked May 15, 2016 at 12:53 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

There's really no way to say anything helpful on the basis of an old-style anonymized plan (update your version of Plan Explorer!) with only estimates. Ideally, we would need a non-anonymized plan, with the query run directly from Plan Explorer. I realize this might be challenging for a query that runs for 6-7 hours, but the closer you can get to that ideal the better.

You're using an old version of SQL Server that will not report hash spills, but actual row count information might give some ideas whether that is occurring. There does not seem to be any immediately obvious reason this query could not use parallelism. On a test system, you could try the query with undocumented and unsupported trace flag 8649 enabled. This will produce some sort of parallel plan if at all possible. Compare the estimated cost of that parallel plan with the 12,391.5 estimated cost of the provided plan to see if the optimizer chose not to use parallelism for cost reasons. It was likely wrong about that, by the way.

If the query cannot be run from Plan Explorer, try to capture STATISTICS IO information manually, and add that to your question.

avatar image By SQLkiwi ♦ 6.6k answered May 20, 2016 at 04:43 PM
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

Topics:

x641
x455
x109
x89
x4

asked: May 15, 2016 at 12:53 AM

Seen: 60 times

Last Updated: May 20, 2016 at 04:43 PM