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:
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
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
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
When Spills Are Acceptable
Spills are unavoidable for:
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:
When addressed properly, the performance improvement can be dramatic, often improving queries from minutes to seconds.