Why spill to TempDB

Can somebody tell me, why SQL server spills to TempDB in this example (plan 15) after the HASH JOIN to t_vertraege (second join from right side)?

Both - estimated data and estimated records are slightly greater than the actual. Ok, [kontokorrent_einzel] is a big table (6.5 mio records), so the data size is about 1 GB, but the server has 128 GB, so this should not be a problem.

BTW: the query runs ~ 15 min and I have no idea, how to make it faster. Originally it were all INNER JOINs, but (although I have actual statistics) it "believes" that it will return only ~200k when I use INNER JOINs. Without the forced HASH JOINs the query will need ~ 30 min (because it makes ~6 mio NESTED LOOPs to each of the both joins to t_termine)

tmpFA02.pesession (187.3 kB)
avatar image By Thomas Franz 16 asked Jun 09, 2016 at 01:47 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

...why SQL server spills to TempDB in this example...

The answer lies on the Plan Tree tab, which shows the number of rows processed by each thread at the hash join in question:

alt text

Memory is reserved for the hash join on the basis of the expected number and size of the rows. This reserved memory is acquired at the beginning of query execution. It cannot be increased once execution has started, no matter how much free memory the instance might have available at that time. This is by design.

For a row-mode parallel hash join, the memory grant is also evenly split between the hash table instances. With four threads, this means 25% each. Where the rows are skewed, with more rows ending up on one thread than another, the thread that exceeds its allowance will spill. Memory cannot be shared between threads.

In your plan, rows are allocated to hash join threads using hash partitioning of the vertragsig column (the join key). This information is shown on the exchange (Parallelism) operators below each input to the hash join.

When the next service pack for 2014 ships, it will likely include the plan improvements shipped for 2012 SP3, which shows more detailed spill information, including which threads spilled in a parallel execution.

IMPORTANT

You should not use join hints like LEFT HASH JOIN. This does not simply force a hash join, it also prevents the optimizer from reordering joins or swapping inputs, exactly as if you had added an OPTION (FORCE ORDER) hint.

If you want to force only hash joins for this query, use OPTION (HASH JOIN) instead.

This partly explains why your query is so slow: you are forcing SQL Server to access the tables in a particular order, and forcing it to build the hash table on the much larger inputs. This is wasteful (reserving 5.6GB of memory) and inefficient.

Remove the LEFT HASH JOIN hints and replace them with OPTION (HASH JOIN).

sp.png (3.1 kB)
avatar image By SQLkiwi ♦ 6.6k answered Jun 14, 2016 at 12:57 PM
more ▼
(comments are locked)
avatar image Thomas Franz Jun 17, 2016 at 09:21 AM

Thanks for your answer, I wasn't aware, that SQL server assigns the memory to the different threads).

Using OPTION (HASH JOIN) over LEFT HASH JOIN does not help - the query will run ~ 30 min (instead ~15) with the option.

avatar image SQLkiwi ♦ Jun 18, 2016 at 07:12 AM

"Does not help" is too vague. Please upload a new plan (ideally as a new question, since your original has been answered) captured with OPTION (HASH JOIN) instead of LEFT HASH JOIN.

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
x166
x6

asked: Jun 09, 2016 at 01:47 PM

Seen: 144 times

Last Updated: Jun 18, 2016 at 07:13 AM