Suggestions for improving performance of query

S S 2016-05-15 00:53:09

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?

SQLkiwi 2016-05-20 16:43:42
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.