Is there a way to get more information about parallel hash / sort spills from XML query plan

GokhanVarol 2013-03-20 15:46:12

Is there any trace flag to turn on which then will put more information into the query plan or towards messages about which thread caused spilling ( or not even the per thread how much spill did occur) and how much the spill was in Bytes or something that will give more visibility?
Thank you
SQLkiwi 2013-03-21 10:16:28
The simple answer is no.

You can use DMVs to see tempdb usage.

The underlying cause is that one or more threads, for one or more correlated values of CntyCd results in a hash build-side memory requirement that exceeds the amount available to that thread.

The nature of nested loops means the execution plan would have to contain an array of performance information (one per CntyCd) and this would not be practical for loops with a large number of input-side rows. In any case, the facility does not exist today. You could go some way to determining which value causes the spill by executing the query with the same plan forced for each CntyCd.

It is quite possible the hash would still spill even if you dedicated the entire server memory to the hash table for this query. I hope it goes without saying that would not be a very sensible ambition.

I would look to rewrite the query to eliminate the hash join without introducing sorts. I would need a statistics-only copy of the database tables in question and a full copy of the query text (truncated in the XML plan at the moment). It is usually possible to find an efficient nested loops replacement given indexing freedom, which would avoid the enormous memory grants and spills.

I cannot promise to provide a working solution even if the information is provided, however. My free time for community work is limited, and this is the sort of query tuning I usually charge several hundred dollars per hour for.

GokhanVarol 2013-03-21 20:00:19
Thank you so much ++ 🙂
I attached a SQL 2012 database backup, Database has partitioned empty tables. I put the code into procs (the only procs there)

PROCEDURE: Info01 — Usage (rows, UsedMB) information on the real database, partition level and table level
PROCEDURE: Code01 — Me trying to do all the tricks to reduce hash join by passing cntycd in a nested loop
PROCEDURE: Code02 — Cleaned the code in Code01

link text

GokhanVarol 2013-03-22 18:49:13
Hi Paul
This attached query gave a parallel plan, I exaggerated the statistics on the temp table.
GregGonzalez 2013-03-23 14:18:37
Gokhan,
In the future please take care to post followups like this as comments, not answers, and attach large queries, do not inline them. Thanks.
GokhanVarol 2013-03-23 14:22:53
Greg, How would you be able to reply / comment back with a attachment or large text since they do not fit into comment section, what is the alternative?
GregGonzalez 2013-03-23 15:16:33
Either attach it to the original answer, or create a new answer and then "convert to comment".