Hash spills, can it be avoided?

GokhanVarol 2013-03-19 14:27:17

The attachment QueryWithSmallMemoryGrantAndSpillsOnHashJoin.pesession constains a parallel query plan where hash join spills which I tried faking some (a lot of) rows in QueryWithFakeRowsIntoHashJoinButStillSpills.pesession but Hash spills still occured.

1-) How can I justify the memory needs to be reserved per hash join, is is equally divided between threads?
2-) Can I avoid hash spills in this query, if I do is it worth the gain?
3-) Can I control the hash spills with forcing the optimizer grant more memory coming into hash with any traceflag or any other way?
Thank you

alt text

SQLkiwi 2013-03-20 07:06:47
1. Yes, memory grant available to an operator is equally divided between threads
2. If the server can satisfy an increased memory grant (from the current ~6GB!) then yes, the spill might be avoided. The current spill is only level 1, compared with level 2 previously so the impact of completely avoiding the spill may not be so marked. The only way to know for sure is to test it.
3. There is no trace flag I am aware of that would increase memory grant here. The main tools at our disposal are to increase the estimated number of rows and/or the estimated average size of each row.

The query in question is far outside the optimizer's model, as you can tell by the difficulties you continue to encounter with parallelism and memory grant. Without access to at least a statistics-only copy of the tables concerned, and more likely test-system access, it is quite difficult to make specific suggestions.

My own personal experience with systems that needed queries similar to yours is that the design was one that would always require unsupported SQL syntax tricks and CLR use to produce reasonable performance, as well as demanding an extremely fast (and expensive) I/O subsystem.

Ultimately, this type of system ends up being hard work to write, debug, and maintain. If the company in question is prepared to make that compromise, they will likely have to accept that a great deal of money will have to be spent on consultants with very specific expertise.

GokhanVarol 2013-03-20 15:14:20
In a Hash join does it take into the consideration of the full row size to reserve memory or does it have a fixed hash size (16 bytes or so) multiplied by the row size to reserve the memory. In my test system I have 30GB of memory allocated for this sql server instance, It received 5.7GB of memory grant and Max used memory is only 2.8 and the estimates I generated coming into the hash join are huge numbers (in billions).
SQLkiwi 2013-03-21 09:48:39
It's based on the full row size, since the hash buckets have to contain all the data needed for output from the build side. There is a limit to how much of the available memory can be used for memory grants (about 25% by default). This can be adjusted using Resource Governor. The problem in your case is the very large grant is being split 6 ways and one thread happens to encounter a data set that exceeds its allocation. I still think hash join is the wrong choice for this query.
GokhanVarol 2013-03-20 15:14:24
What I wanted to find out is if I totally avoid the spill vs did not even bother how much of a difference it makes in duration, since my tempdb is in ssd's and our production tempdb is on a SAN which has SSDs with hot data and our tempdb is on Tier 1 storage I wanted to find out if putting the extra effort reserving lot's more memory has a good return.
SQLkiwi 2013-03-21 09:50:44
It would certainly be more noticeable with tempdb on SAN spinning disk compared with SSDs. It seems odd to me to tune queries in a test environment that has such different physical performance characteristics from production. Also strange that production would use fast disk for data files but not tempdb.