Spills and Spools: What SQL Server Stages in TempDB

A spool is a planned operator. A spill is an event during execution. A spool can save work. A suitable index can make that work unnecessary altogether. A spill, on the other hand, shows that a sort or a hash join had to make do with too little memory.

This article shows both cases using our own lab measurements on SQL Server 2022 and 2025: how to recognize them in the plan, when an index helps, and what it costs.

Two terms, one place

Both use TempDB: spools create work tables or a temporary index there, and spills move parts of the sort or hash data there. A plan can contain a spool without a spill and a spill without a spool. For both cases, our lab has actual execution plans.

Sort and hash: when the memory grant is not enough

Before a query starts, SQL Server grants it memory for sorts and hash tables (memory grant). The basis is the estimated row count and row width. If the grant is not enough during execution, the operator moves parts of its data to TempDB and reads them back later. That is the spill.

In the lab, we deliberately limited the grant to measure the effect in isolation:

On SQL Server 2022, the sort without a spill received 65,912 KB of memory, and with a limited grant only 560 KB. The plan then reports 19,362 pages each written to TempDB and read back. For the hash join, it was 125,000 KB versus the minimum requirement of 1,024 KB and 36,160 pages each in TempDB.

The hash spill ran faster on 2025 than on 2022. That is not a version ranking: The runs took place in separate time windows with different compatibility levels on the same shared SSD, and we did not investigate the cause. What is documented is the difference between a sufficient and a limited grant on the same version.

What to check for a spill:

• Estimated versus actual rows at the sort or hash operator and at the operators before it. A large deviation is the most common cause.
• Granted versus used memory in the plan properties (MemoryGrantInfo: GrantedMemory, MaxUsedMemory).
• Whether the work can be avoided. A sort that an index already delivers needs no memory.
• From SQL Server 2019 (compatibility level 150), Memory Grant Feedback can adjust the grant for repeated executions in row mode as well. That helps with recurring queries, not with the first execution.

The index that saves the sort

With an index that already delivers the rows in the desired order, the Sort operator disappears. With the same limited grant, the runtime dropped to 58 ms on both versions, with no sort and no spill. The price: this wide index takes up about 40 MB, making it almost as large as the table itself. Whether that pays off is decided by the entire workload, not by this one query.

The useful spool: read once, use many times

A Lazy Table Spool stores an intermediate result on first demand and outputs it again on further passes. In our example, a nested loops join returns the same filtered set of orders (Amount < 10) for each of 50 customers. The spool is filled once and reused 49 times.

Here the spool saves the repeated work. Removing it with a hint makes the query slower.

It looks different once a narrow index on Amount is added: then the repeated access is cheap in itself. The optimizer kept the spool anyway (29.7 ms); without the spool, it ran slightly faster at 25.5 ms. This difference of a few milliseconds is not a reliable advantage. But it shows that after an index change, you should reassess a spool instead of treating it as a given.

Eager Index Spool: SQL Server builds itself an index, on every execution

An Eager Index Spool is usually a clear signal of a missing index. If there is no access path, SQL Server reads the entire table, builds a temporary index from it in TempDB, and then searches in that. After the query, this index is gone again. On the next execution, everything starts over.

Our example looks for the most recent order for each of 200 customers:

SELECT COUNT_BIG(*) AS n, SUM(CONVERT(bigint, x.Amount)) AS total
FROM dbo.Customers AS c
CROSS APPLY (
    SELECT TOP (1) o.Amount
    FROM dbo.Orders AS o
    WHERE o.CustomerId = c.Id
    ORDER BY o.Stamp DESC, o.Id DESC
) AS x
WHERE c.Id <= 200;

Without a suitable index, the plan contains an Eager Index Spool: built once, reused 199 times, 80,000 rows output in total. With an index that matches the search, the sort order, and the result column, the spool disappears:

CREATE INDEX IX_Orders_Customer_Stamp
ON dbo.Orders (CustomerId, Stamp DESC, Id DESC)
INCLUDE (Amount);

The result stayed identical, and the index access is confirmed in the actual plan. The NO_PERFORMANCE_SPOOL hint did not remove the Eager Index Spool on either version. That matches the analysis by Paul White (see Sources): against an Eager Index Spool, the missing index helps, not a hint.

The protective spool: necessary, not harmful

Not every spool is a tuning candidate. If an UPDATE changes a column through whose index it also finds the affected rows, a row could be picked up more than once (the “Halloween problem”). An Eager Table Spool then provides Halloween protection by separating reading from changing.

In our lab, an UPDATE increased the value Score while reading through the index on Score. The plan contained an Eager Table Spool, even with NO_PERFORMANCE_SPOOL. Exactly 500 rows were each increased once. This spool ensures a correct result. “Eager” describes only how it is filled, not its purpose: an Eager Index Spool and a protective spool are two different cases.

What the matching index costs

An index saves read work and costs on every change. For the index from the Eager Index Spool example, we measured both:

The write measurement is a small block in one transaction, not a throughput test. It shows the price of this one index, not a general limit. Whether it pays off depends on how often the query runs and how much is written to the table.

How to proceed

1. Get the actual plan. Only it shows spills. In SSMS, use “Include Actual Execution Plan”; alternatively, take the plan shape from Query Store and the TempDB usage from the query below.
2. Spool or spill? Spool: a separate operator. Spill: a warning on the sort or hash operator.
3. For a spill: compare estimated and actual rows, check the memory grant. Improve the estimate (statistics, query shape) or avoid the sort with a suitable index.
4. For an Eager Index Spool: derive the index that SQL Server builds for itself every time. Equality columns first, then the sort order, needed columns as INCLUDE.
5. For a Lazy spool or protective spool: understand which work it saves or which correctness it ensures. Do not remove it with a hint without having measured the effect.
6. Measure the total cost. Read time of the query, write cost and space of the index, effect on the rest of the workload.

Query: Which queries use the most TempDB?

From SQL Server 2017, Query Store records per execution how many TempDB pages a query used. That lets you find the candidates for spills and spools without waiting for the next incident.

/* PSG Stack · SQL Server TempDB · Query 3
   Query Store: Which queries use the most TempDB?

   - Read-only. Run it in the user database whose Query Store
     you want to analyze (not in TempDB).
   - Prerequisite: Query Store is turned on for the database.
   - avg/max_tempdb_space_used counts 8 KB pages per execution
     (spills, spools, #temp tables, sorts).
   - Adjust the time window below: @from and @to in UTC.
   - Permissions: VIEW DATABASE STATE
     (from SQL Server 2022, VIEW DATABASE PERFORMANCE STATE is enough).
     Without this permission, the query fails with error 262.
   - If Query Store is off for the database (or the query runs in master or TempDB),
     the result is empty, without an error.
   - SQL Server 2017 and later. Tested in the PSG lab on 2017, 2019, 2022, and 2025.
*/
DECLARE @from datetimeoffset = DATEADD(HOUR, -24, SYSUTCDATETIME());
DECLARE @to datetimeoffset = SYSUTCDATETIME();

SELECT TOP (25)
    q.query_id,
    p.plan_id,
    SUM(rs.count_executions)                                                        AS executions,
    CAST(SUM(rs.avg_tempdb_space_used * rs.count_executions) * 8 / 1024.0
         / NULLIF(SUM(rs.count_executions), 0) AS decimal(12,2))                    AS tempdb_mb_per_exec,
    CAST(MAX(rs.max_tempdb_space_used) * 8 / 1024.0 AS decimal(12,2))               AS tempdb_mb_max,
    CAST(SUM(rs.avg_tempdb_space_used * rs.count_executions) * 8 / 1024.0 AS decimal(14,1)) AS tempdb_mb_total,
    CAST(SUM(rs.avg_duration * rs.count_executions) / 1000.0
         / NULLIF(SUM(rs.count_executions), 0) AS decimal(12,1))                    AS duration_ms_per_exec,
    MAX(rs.last_execution_time)                                                     AS last_executed,
    OBJECT_NAME(q.object_id)                                                        AS module,
    LEFT(qt.query_sql_text, 4000)                                                   AS sql_text
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
JOIN sys.query_store_query AS q ON q.query_id = p.query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
WHERE rsi.start_time < @to
  AND rsi.end_time   > @from
GROUP BY q.query_id, p.plan_id, q.object_id, qt.query_sql_text
HAVING SUM(rs.avg_tempdb_space_used * rs.count_executions) > 0
ORDER BY SUM(rs.avg_tempdb_space_used * rs.count_executions) DESC
OPTION (MAXDOP 1);

If needed, sort by tempdb_mb_per_exec instead of the total: the total shows what burdens TempDB overall, while the value per execution shows individual large consumers. For the conspicuous plans, open the stored plan in Query Store and check whether a spool or a sort or hash operator is the cause. Whether a spill actually occurred is shown only by the actual plan of a new execution.

Sources

• Microsoft: Logical and Physical Showplan Operator Reference (Spool, Sort, Hash Match)
• Microsoft: Troubleshoot Memory Grant Issues in SQL Server
• Microsoft: Query Hints (Transact-SQL) (MAX_GRANT_PERCENT and NO_PERFORMANCE_SPOOL)
• Microsoft: Index Architecture and Design Guide
• Paul White: The Eager Index Spool and The Optimizer
• Microsoft Tech Community: Fixing problematic index spools

More TempDB topics