Hash join spills to disk even though there is plenty of memory granted for the query

We are running SQL Server 2014 Enterprise Sp2 with 1.5TB of memory and 64 cores. There is close to 17 GB memory granted to this single threaded query, max memory used is 2.2gb, even though the hash join has plenty of memory available it seems like it's not using it and spilling to disk. Any idea why? Thank you in advance.

Same question posted in SQLPerformance. The Query plan and images are posted there

I want to understand the Memory fractions. It shows the hash join has 52.48% of the memory grant (total 17GB+) which is close to 9GB. But the maximum memory used of the plan is a lot less which is 2.2+GB and Hash join is the only spill in this query. Is there something I am missing about the memory fractions, are they not accurate?

The 17GB memory grant is across the entire query. That 17GB isn't given in its entirety to a single operator. Each operator gets a fraction of the overall grant, and that amount is not dynamically reallocated during query execution.

If you go into the query's XML and search for MemoryFractions (or hover your mouse over operators in Plan Explorer), you'll see more information about the fractions assigned to each operator.

It shows the input side of the join gets 52.48%, but not the output side. If you have more memory required for the data coming out of the operator, you'll spill to disk.

To avoid the spill, tune indexes, tune the query, use the new min memory grant hint in 2016, etc.

StackExchange Database Administrators Q#174026, answer score: 7