Howcome this estimated plan has a Serial Desired Memory close to 550GB?

GokhanVarol 2014-08-20 19:37:15

This is related to a query I posted earlier which spilled to tempdb which has 500GB space, filled it and failed. I am attempting to reduce the memory requirements of the same query by giving index seek hints. What I don't understand is this Query only has 1 hash join instead but now it's Serial Desired Memory is close to 550GB increased from 210GB. How is this value calculated?
Thank you

Aaron Bertrand 2014-08-20 21:57:29
As Paul suggested on the other question, please post actual (post-execution) plans if you can.
SQLkiwi 2014-08-20 22:09:50
Also, isn't tempdb filling simply due to the size of the final temporary table you are creating? What leads you to believe hash spills are filling tempdb?
GokhanVarol 2014-08-20 23:50:43
I have been monitoring dm_db_session_space_usage and dm_db_session_space_usage dmv's and user allocation has not been more than few GB's but internal allocation was constantly increasing during the execution (At one point internal allocation – deallocation difference was over 400GB and later on query failed reporting out of space in tempdb). Unfortunately this query has not completed yet and I don't have the actual execution plan. The query was actually getting around 20GB memory grant on the server since we use resource governor to limit memory grants. My understanding was hash join only required memory to build the hash table which are the sum of the upper dataflows in the estimated plan and summing the data sizes on those dataflows did not add up to either tempdb internalalloc space usage or requested memory grant. I have been more confused after removing 2 out of 3 hash joins and replacing them with loop joins the requested memory grant jumped to 550GB range.