A tempdb spill happens when a query operator — usually a sort or hash — needs more working memory than SQL Server granted it, so it writes intermediate data out to tempdb on disk to continue.
The result is a fast in-memory operation turning into a slow on-disk one. The root cause is almost always a bad cardinality estimate, so the real fix is fixing the estimate — not just adding memory.
A sort or hash warning in an execution plan is the engine telling you it ran out of memory mid-query. It is one of the clearest, most actionable signals in tuning — and it almost always points back to estimation.
What a spill is
Before a query runs, SQL Server reserves a fixed memory grant based on how many rows (and how wide) it estimates each operator will handle. If the operator then has to process more data than that grant allows, it cannot resize the grant mid-flight — so it spills: it writes intermediate results to tempdb on disk and works from there.
Sort warnings vs. hash warnings
- Sort warning — a sort operation did not fit in memory and spilled. (Index-creation sorts are excluded; this is about sorts within a query.)
- Hash warning — a hash operation's build input did not fit in memory, so it was split into partitions and spilled.
Since SQL Server 2016, the execution plan includes detailed spill information, so you can see exactly which operator spilled and by how much. As Redgate's Simple Talk puts it bluntly: never ignore a sort warning.
Why spills hurt
Disk is far slower than memory. Work that should have happened in RAM instead reads and writes intermediate data to tempdb, and the penalty scales with the size of the spill. A single small spill may be harmless; a large or repeated one can dominate a query's runtime.
The root cause: bad estimates
Spills are a symptom; the disease is a wrong row estimate. If SQL Server expects 1,000 rows and gets 10 million, the memory grant is sized for 1,000 — and the rest spills. The usual culprits:
- Outdated or missing statistics — the most common cause.
- Parameter sniffing — a plan built for one parameter value, run with another.
- Underestimated row size, or a query pattern the optimizer struggles to estimate — for example a table variable on a large set.
The spill, and the estimate that caused it, are both visible in the execution plan — look for the warning icon and a large estimated-vs-actual gap on the spilling operator.
How to fix it
Fix the estimate, not just the symptom. Because spills come from bad row estimates, the durable fixes are the ones that make the estimate accurate: update or add statistics, add a supporting index, or rewrite the query. Per practitioner guidance, the best course is to examine the query and statistics and solve why SQL Server is estimating wrong — for example with a new index or a rewrite — rather than just forcing a bigger memory grant, which treats the symptom and can starve other queries of memory.
How automated analysis uses spill signals
A spill warning is a high-value clue in the evidence an AI analysis reasons over: it points directly at an estimation problem and narrows the likely fix to statistics, indexing, or a rewrite. SprocOptimizer collects execution plans — including spill warnings and the estimate/actual gaps behind them — as part of its analysis, and validates any proposed fix for identical results and measured improvement before promotion.
Frequently asked questions
A tempdb spill happens when an operator in a query plan — usually a sort or hash — needs more working memory than SQL Server granted it, so it writes intermediate data out to tempdb on disk to keep processing. A fast in-memory operation becomes a slow on-disk one, which is why spills hurt performance.
Sort and hash warnings indicate a spill: a sort or hash operation did not fit in the granted memory. The underlying cause is almost always a bad cardinality estimate — outdated or missing statistics, parameter sniffing, or an underestimated row size — that led SQL Server to grant too little memory for the actual amount of data.
Disk I/O is dramatically slower than memory access. When an operation spills, the work that would have happened in fast RAM instead reads and writes intermediate results to tempdb on disk, adding significant overhead. The bigger the spill, the larger the penalty.
Fix the estimate, not just the symptom. Because spills usually stem from bad row estimates, the durable fixes are updating or adding statistics, adding a supporting index, or rewriting the query so the optimizer estimates correctly and grants enough memory. Simply forcing a larger memory grant treats the symptom without addressing why the estimate was wrong.
Primary sources & further reading
- Redgate Simple Talk — Never Ignore a Sort Warning in SQL Server.
- Brent Ozar — TempDB Spills.
- MSSQLTips — Correct tempdb spills caused by outdated statistics.
Trace the spill back to its cause
SprocOptimizer reads spill warnings and the estimates behind them, pinpoints whether it's statistics, indexing, or sniffing, and validates the fix — on-premises, with no row-level data leaving your network.
Request a Demo Read the Optimization Guide