TempDB Spills: The Silent Memory Performance Killer

What a Spill Actually Is

Certain operators — sorts, hash joins, hash aggregates — need working memory to do their job. Before a query runs, the optimizer estimates how much memory that will take and requests a memory grant. If the estimate is too low and the operator runs out of the memory it was given, it doesn't fail — it writes its intermediate working set out to tempdb and continues from disk. That's a spill.

⚠ The dangerous part is exactly that it doesn't fail. The query still returns correct results. It's just an order of magnitude slower than it should have been, and nothing in the application layer tells you why — the only trace is in the execution plan and in the server's wait statistics.

Why the Estimate Is Wrong in the First Place

Memory grants are sized off the optimizer's row-count and row-width estimates — the same estimates that drive every other plan choice. The usual suspects are familiar to anyone who has tuned a query before:

Stale statistics — the optimizer thinks a table has 10,000 rows; it actually has 10 million.
Table variables — pre-2019 compatibility, the optimizer assumes one row for a @TableVariable regardless of actual content (see our temp tables vs. table variables article).
Parameter sniffing — a plan compiled for a narrow parameter value gets reused for a much wider one, and the memory grant sized for the narrow case is far too small.
Complex predicates — multiple correlated WHERE conditions the optimizer can't accurately estimate the combined selectivity of.
Wide implicit conversions — a data type mismatch that forces the optimizer to guess at row width.

Finding Spills in the Execution Plan

Turn on the actual execution plan and look at Sort or Hash Match operators. In SQL Server Management Studio, a spill shows as a warning icon (a yellow triangle) directly on the operator, and hovering reveals the detail:

-- The plan XML contains this when a spill occurs
<Warnings>
    <SpillToTempDb SpillLevel="1" />
</Warnings>

You can also query it directly for a running or recently completed statement using the query store or the plan cache:

SELECT
    qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE CAST(qp.query_plan AS NVARCHAR(MAX)) LIKE '%SpillToTempDb%';

Catching Spills as They Happen

For ongoing monitoring rather than a one-off check, the sort_warning and hash_warning extended events fire in real time whenever a spill occurs — far cheaper than scanning plan XML after the fact:

CREATE EVENT SESSION [TempDBSpills] ON SERVER
ADD EVENT sqlserver.sort_warning,
ADD EVENT sqlserver.hash_warning
ADD TARGET package0.event_file(SET filename = N'TempDBSpills');

ALTER EVENT SESSION [TempDBSpills] ON SERVER STATE = START;

Fixing the Root Cause

Root cause	Fix
Stale statistics	`UPDATE STATISTICS ... WITH FULLSCAN` on the affected tables; check auto-update thresholds on large tables
Table variable with a large row count	Switch to a `#temp` table so the optimizer has real statistics to work with
Parameter sniffing	`OPTION (RECOMPILE)` for genuinely variable-shaped queries, or query store plan forcing for a known-good plan
Implicit conversion inflating estimated row width	Match data types explicitly between joined/compared columns
Genuinely large sort/hash that memory can't reasonably cover	Add a supporting index so the engine can avoid the sort entirely, rather than sizing a bigger grant for it

The best fix for a sort spill is often to not sort at all. If a query does ORDER BY on a column with no supporting index, SQL Server has to materialize and sort the full result set. An index that already delivers rows in the required order eliminates the sort operator — and the spill risk that came with it — completely.

When It's Genuinely a Memory Problem

Not every spill is a bad estimate — sometimes the server is simply under memory pressure, and grants that would otherwise be adequate get squeezed by concurrent workload. Distinguish the two with sys.dm_exec_query_memory_grants: if requested_memory_kb is reasonable but granted_memory_kb is being throttled below it under concurrency, that's a workload/capacity issue, not a bad estimate — and the fix is Resource Governor memory limits or more physical memory, not query tuning.

SELECT
    requested_memory_kb,
    granted_memory_kb,
    ideal_memory_kb,
    used_memory_kb,
    query_cost,
    wait_time_ms
FROM sys.dm_exec_query_memory_grants;

The Bottom Line

A tempdb spill is a query that ran correctly but slowly, with the only evidence hidden inside the execution plan. Most spills trace back to the optimizer estimating the wrong row count or row width — fix the statistics, the data types, or the table-variable-vs-temp-table choice behind the estimate, and the spill (and the disk I/O that came with it) disappears on its own.