Understanding SQL Server Spills: Causes & Solutions

In SQL Server, a spill to TempDB occurs when an operator in a query execution plan requires more working memory than was granted by the server. When this happens, SQL Server writes intermediate data out to TempDB on disk to continue processing. This disk-based overflow is what we refer to as a spill.

The operators most commonly affected by spills include:

Sort
Hash Match (for joins or aggregates)
Window functions
Table Spool (in certain scenarios)

What a Spill Really Means

During query execution, operators such as sorts, hashes, and aggregates need memory to hold rows temporarily. SQL Server estimates the required memory upfront and provides a memory grant before the query begins. If the actual memory needed exceeds this grant, the operator spills data to TempDB. Disk I/O is significantly slower than memory access, so spills introduce performance overhead.

Why Spills Matter

Spills are typically a symptom of inefficiency because they:

Dramatically slow down the affected query due to disk operations
Increase contention on TempDB, which can impact other workloads on the server
Often point to underlying problems such as inaccurate cardinality estimates

While a small, one-time spill might be tolerable (especially on infrequent large reports), large, frequent, or repeated spills deserve attention and tuning.

Common Causes of Spills

Spills usually stem from one or more of these issues:

Bad cardinality estimates — SQL Server underestimates the number of rows flowing through an operator
Outdated or missing statistics — Leading to poor row count predictions
Parameter sniffing / parameter sensitivity — A plan cached for one parameter value performs poorly for others
Underestimated row size — Especially with wide rows or large data types
Insufficient memory grant — Due to incorrect estimates or server-wide memory pressure
Query design — Unnecessary sorts, overly large joins, or Aggregations that process too much data

How Spills Appear in Execution Plans

In an actual execution plan (not estimated), look for these indicators:

Warning message: “Operator used tempdb to spill data during execution”
Specific warnings: Hash Warning or Sort Warning
Properties on the operator (Sort or Hash Match) showing:
- Spill Level
- Spilled Data
- Number of Rows Read
- Granted Memory vs. Used Memory
- Requested Memory
A yellow warning triangle icon on the affected operator

Simple Example of a Sort Spill

Imagine SQL Server estimates a Sort operator will handle 10,000 rows and requests memory accordingly. During execution, 10 million rows arrive instead. The sort operator cannot hold everything in memory, so it writes sorted runs to TempDB and later merges them. This is a classic sort spill.

Types of Spills

Sort spill — The sort operator writes partially sorted runs to disk and performs a multi-pass merge.
Hash spill — A hash join or hash aggregate cannot fit its hash table in memory, so it partitions the data to TempDB and processes it in multiple phases.

How to Reduce or Eliminate Spills

Here are practical steps to address them:

Improve cardinality estimates
- Update statistics
- Create more targeted statistics
- Investigate skewed data or problematic predicates/joins
Optimize indexing
- Add covering or supporting indexes to eliminate sorts or reduce data volume
- Match index order to the requested sort order when possible
Reduce data volume earlier
- Apply filters sooner
- Select only needed columns
- Rewrite queries to push reductions before expensive operators
Address parameter sensitivity
- Compare estimated vs. actual row counts across parameter values
- Consider OPTION (RECOMPILE), local variables, or plan guides when suitable
Review memory grant behavior
- Examine granted vs. used memory in plans
- Check for RESOURCE_SEMAPHORE waits
- Leverage Memory Grant Feedback (available in newer SQL Server versions) for repeated executions

Not Every Spill Is a Problem

A small spill on an occasional large reporting query may be acceptable. Spills become more concerning when:

The query executes frequently
The spill size is large (many pages spilled)
Multiple queries spill simultaneously
TempDB is already under pressure
Actual execution time is significantly worse than expected

Quick Checklist When You Spot a Spill

Locate the operator with the warning
Compare estimated rows vs. actual rows
Review memory-related properties (granted, used, requested)
Determine if the operator could be eliminated with better indexing or query changes
Validate statistics and parameter behavior

In One Sentence

A spill to TempDB means SQL Server ran out of working memory for part of the query plan and had to use disk-based TempDB as overflow space, almost always because the memory grant was too small relative to actual needs.

If you’re dealing with persistent TempDB spills, slow queries, or execution plans that aren’t performing as expected, reach out to Stedman Solutions. We specialize in SQL Server Performance tuning and can help identify the root causes and implement effective fixes.

Contact Stedman Solutions today for expert Performance Tuning assistance.

Understanding TempDB Spills in SQL Server Execution Plans