All 7 Hash Indexes & Hash Join Operations questions
More in Hash Indexes & Hash Join Operations
What is a hash index, and how is it different from the more common B-tree index used for regular disk based tables? How does the BUCKET_COUNT setting affect the performance of a hash index on a memory optimized table? In what situations does SQL Server's query optimizer typically prefer a hash join over a nested loop join or a merge join? What are the differences in how a hash index and a memory optimized nonclustered index handle range queries and equality lookups? How would you troubleshoot a query that is unexpectedly using a slow hash join when a much faster index seek and nested loop join should be possible?
Advertisement
SQL Server › Hash Indexes & Hash Join Operations

What happens when a hash join does not have enough memory available to build its hash table entirely in memory, and how does this affect performance?

Advanced

Answer

When the hash table does not fit in the memory that was granted, SQL Server spills excess data to disk in a process called a hash spill, which is significantly slower than an entirely in memory hash join since it introduces additional disk I/O, and is often a sign that the optimizer's row estimates were inaccurate or that the server's available memory is insufficient for the workload.
-- Check for hash spills in an execution plan's warnings
SET STATISTICS IO ON;
SELECT * FROM LargeTable1 t1 JOIN LargeTable2 t2 ON t1.Id = t2.Id;
Real-world example A team investigating a slow report discovers the execution plan shows a hash spill warning, revealing that inaccurate statistics had caused SQL Server to underestimate the memory needed for the join, and updating statistics resolves the issue.

Common follow-ups: How do you identify a hash spill warning in an execution plan?;What steps can reduce the likelihood of a hash spill occurring?

Query Optimization & Plans;SQL Server Architecture & Editions
All 7 Hash Indexes & Hash Join Operations questions