Detecting And Fixing Memory Spills To TempDB In SQL Server

High-volume systems on SQL Server often suffer from hidden performance issues that do not appear in normal query tuning discussions. One such major issue is memory spills to TempDB. These spills silently degrade performance, increase I/O pressure, worsen concurrency, and make predictable query performance nearly impossible.

This article explains how to detect, analyse, and fix memory spills to TempDB using practical examples, real execution plan interpretation, and production-ready solutions. It is written for senior developers and DB engineers who want deeper performance stability in OLTP or hybrid workloads.

Understanding What Memory Spills Are

SQL Server allocates memory for query operations such as:

Sorting
Hashing
Aggregations
Joins (especially Hash Joins)

When SQL Server cannot allocate enough query workspace memory, the engine is forced to spill the intermediate data to TempDB.

This happens during operators like:

Sort
Hash Match (Build)
Hash Aggregate
Window Spool
Index Spool

These operators require memory grants, and if the estimated memory is insufficient, a spill occurs.

Why Memory Spills Are Dangerous

Memory spills are not just small inefficiencies. They can seriously impact system stability.

Key Effects

Slow Query Execution
Disk I/O is far slower than in-memory operations.
TempDB Overload
The biggest cause of TempDB contention in large systems.
Unpredictable Latency
SQL Server performs well until memory pressure hits, then becomes extremely slow.
Blocked Or Deadlocked Workloads
Heavy TempDB I/O causes resource waits that block other sessions.

How SQL Server Allocates Memory Grants

SQL Server extends workspace memory through a component called the Query Memory Grant Framework.

Simplified:

Query Request
    |
    +-- Memory Grant Estimate (based on row estimates + stats)
    |
    +-- If memory available:
    |        Grant memory
    |        Execute normally
    |
    +-- If memory NOT available:
             Spill to TempDB

An incorrect memory estimate is the root cause of spills.

Visual Diagram: How Memory Spills Occur

          +---------------------------+
          | Query Starts Execution    |
          +-------------+-------------+
                        |
                        v
             +----------+-----------+
             | Memory Grant Needed  |
             +----------+-----------+
                        |
          +-------------+-------------+
          | Enough Memory Available?  |
          +------+------+-------------+
                 | Yes                     | No
                 |                         |
                 v                         v
     +-----------+---------+      +--------+---------+
     | Process In Memory   |      | Spill To TempDB  |
     +----------------------+      +------------------+

How To Detect Memory Spills

SQL Server provides multiple ways to detect spills:

Execution plans
Actual execution plan warnings
sys.dm_exec_query_stats
sys.dm_db_session_space_usage
sys.dm_exec_query_memory_grants
Extended Events

We deep-dive into each.

Method 1: Detecting Spills In Execution Plans

When a spill occurs, SQL Server marks the operator with a yellow warning symbol.

The Actual Execution Plan will show:

Hash Warning
Sort Warning
Spill Level

Example Warning

Warning: Operator used tempdb to spill data during execution. 
Spill level: 2.

Example Plan Snippet

Hash Match (Aggregate)
  Actual Number of Rows: 12000000
  Estimated Number of Rows: 10000   <-- Bad estimate
  Number of Hash Spills: 8

A tell-tale sign is:

EstimatedRows << ActualRows

This mismatch triggers under-estimated memory grants.

Method 2: Using sys.dm_exec_query_stats

This DMV provides information on memory grants and spills.

SELECT 
    qs.total_spills,
    qs.last_spills,
    qs.execution_count,
    qs.total_worker_time,
    qs.last_worker_time,
    qs.plan_handle
FROM sys.dm_exec_query_stats qs
ORDER BY qs.total_spills DESC;

Method 3: Checking sys.dm_exec_query_memory_grants

This shows waiting or granted memory.

SELECT 
    session_id,
    requested_memory_kb,
    granted_memory_kb,
    used_memory_kb,
    max_used_memory_kb,
    is_next_fetch,
    wait_time_ms
FROM sys.dm_exec_query_memory_grants
ORDER BY wait_time_ms DESC;

If used_memory_kb > granted_memory_kb, spilling is likely.

Method 4: Monitoring TempDB Usage During Execution

SELECT 
    session_id,
    internal_objects_alloc_page_count,
    internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage;

Internal object allocations spike during spills.

Method 5: Extended Events

The best XE events for spills are:

sort_warning
hash_spill_details
workfile_created
worktable_created

Example

CREATE EVENT SESSION MemorySpills
ON SERVER
ADD EVENT sqlserver.hash_spill_details
ADD EVENT sqlserver.sort_warning
ADD EVENT sqlserver.workfile_created
ADD EVENT sqlserver.worktable_created
ADD TARGET package0.ring_buffer;
GO
ALTER EVENT SESSION MemorySpills ON SERVER STATE = START;

Real Example: A Query That Spills

Problem Query

SELECT 
    CustomerId,
    SUM(Amount) AS TotalAmount
FROM Sales
GROUP BY CustomerId
ORDER BY TotalAmount DESC;

Why It Spills

Large Sort
Large Hash Aggregate
Poor cardinality estimates

How To Fix Memory Spills

There is no single fix. Multiple strategies exist depending on root cause.

We break the solutions into:

Fix statistics and cardinality
Rewrite the query
Add indexes
Use rowstore or columnstore
Tune the operator choice
Increase query memory grants
Increase server memory
Pre-aggregate or stage data

Strategy 1: Fix Poor Cardinality (Statistics Updates)

Stale or missing stats cause under-estimates.

Fix 1A: Update statistics

UPDATE STATISTICS Sales WITH FULLSCAN;

Fix 1B: Enable auto stats

ALTER DATABASE MyDB SET AUTO_UPDATE_STATISTICS ON;

Strategy 2: Rewrite Query To Reduce Memory Need

Example Fix

Replace ORDER BY aggregate with pre-aggregation.

Original:

SELECT CustomerId, SUM(Amount) 
FROM Sales
GROUP BY CustomerId
ORDER BY SUM(Amount);

Rewrite:

WITH Agg AS (
    SELECT CustomerId, SUM(Amount) AS TotalAmount
    FROM Sales
    GROUP BY CustomerId
)
SELECT *
FROM Agg
ORDER BY TotalAmount;

Strategy 3: Use Proper Indexes To Reduce Memory Operations

Indexes reduce the need for Sort or Hash.

Example Index

CREATE INDEX IX_Sales_CustomerId_Amount
ON Sales(CustomerId, Amount);

This enables streaming aggregates instead of hashing.

Strategy 4: Enable Batch Mode On Rowstore (SQL Server 2019+)

Batch mode dramatically reduces memory and improves parallelism.

ALTER DATABASE SCOPED CONFIGURATION 
SET BATCH_MODE_ON_ROWSTORE = ON;

Strategy 5: Force Operator Changes

Force Ordered Aggregation

OPTION (ORDER GROUP);

Force Merge Join Instead Of Hash

OPTION (MERGE JOIN);

Strategy 6: Use Resource Governor (Rare Case)

Increase workspace memory for specific workloads.

ALTER RESOURCE POOL HighMemPool
WITH (MAX_MEMORY_PERCENT = 40);

Strategy 7: Increase Server Max Memory

If spills happen globally, increase SQL Server memory.

EXEC sp_configure 'max server memory (MB)', 64000;
RECONFIGURE;

Strategy 8: Pre-Aggregate Or Stage Data

For heavy OLAP queries, pre-aggregating reduces memory requirements.

INSERT INTO SalesDailySummary
SELECT Date, CustomerId, SUM(Amount)
FROM Sales
GROUP BY Date, CustomerId;

End-To-End Example: Before And After Spill Fix

1. Initial Query (Bad)

SELECT CustomerId, SUM(Amount)
FROM Sales
GROUP BY CustomerId
ORDER BY SUM(Amount);

Execution Plan:

Hash Aggregation
Sort
12 spills

2. Fixes Applied

Updated stats
Added index on CustomerId
Rewrote ORDER BY using subquery

3. Final Query (Optimized)

WITH Agg AS (
    SELECT CustomerId, SUM(Amount) AS TotalAmount
    FROM Sales
    GROUP BY CustomerId
)
SELECT CustomerId, TotalAmount
FROM Agg
ORDER BY TotalAmount;

Execution Plan:

Stream Aggregate
No Sort
No Spills

Runtime improvement:

Before: 28 seconds
After: 1.2 seconds

Advanced Technique: Using Memory Grant Feedback (SQL Server 2017+)

SQL Server learns from spills and increases memory on next execution.

To check:

SELECT * FROM sys.dm_exec_query_stats
WHERE last_grant_updated IS NOT NULL;

If this kicks in, SQL Server automatically adjusts memory grants.

Advanced Technique: Forcing Minimum Memory Grants

In some cases, the query always spills because SQL Server is too conservative.

Use:

OPTION (MIN_GRANT_PERCENT = 10);

This should be used with caution.

Production Checklist For Eliminating Spills

What To Validate

Statistics are fresh
Cardinality estimates match actual rows
No large Hash Match or Sort without indexes
TempDB is healthy
Proper indexing strategy
Batch Mode is enabled
Query rewrite options considered
Memory Grant Feedback allowed

What To Monitor

wait stats (especially IO_COMPLETION, PAGEIOLATCH_*)
TempDB file growth
spills in execution plans
query memory grant DMV

When Spills Are Acceptable

Spills are unavoidable for:

adhoc analytical queries
unexpected high cardinality
memory-intensive ETL operations

But for OLTP workloads, spills should be near zero.

Summary

Memory spills to TempDB are one of the most damaging performance problems in SQL Server. They silently degrade throughput and increase latency across the entire system. Detecting and fixing spills requires a combination of:

reading execution plans
correcting statistics
adding indexes
rewriting queries
using batch mode
ensuring TempDB and memory settings are correct

When addressed properly, the performance improvement can be dramatic, often improving queries from minutes to seconds.