Which cached statements are writing spill data to tempdb? total_spills helps rank cached statements that spilled during their lifetime. Combine the counters with plans and Query Store evidence, then investigate estimates and row width before asking for larger memory grants.
Read total_spills at Its Actual Scope
sys.dm_exec_query_stats exposes spill counters on supported recent builds. total_spills accumulates spilled pages for the statement since its cached lifetime began. last_spills describes the last execution, and max_spills describes the largest recorded execution in that lifetime.
I keep execution_count and creation_time beside those values. A frequently executed statement can lead the total because of repetition, while one bad execution can dominate the maximum. Those patterns require different follow-up. The counters are statement-level, not a separate entry for every sort or hash operator.
Cache eviction, recompilation, and restart reset the context. A small counter after a reset does not prove the workload stopped spilling. Retain the capture time and lifetime fields when comparing samples. tempdb is doing real work during a spill. Calling it temporary does not make the IO temporary enough to ignore.
Rank Candidates by total_spills and Find Their Text
The next query returns a bounded list ordered by total_spills. It extracts the relevant statement from the batch using the standard byte-offset conversion and retains the cached plan. The statement text is more useful than a whole procedure body when several statements have different spill behavior.
The plan returned by sys.dm_exec_query_plan is ordinarily the cached plan representation, not a completed actual execution plan with every runtime spill warning. Use it to locate candidate Sort and Hash Match operators, then capture an actual plan for the representative execution when warning details are required.
I avoid presenting a cached plan as runtime proof it does not contain. The counters establish statement-level recorded spilling, while the actual plan supplies the operator-level warnings and observed behavior. Keep those two evidence sources distinct. Missing XML also needs an availability check rather than an invented operator explanation.
SELECT TOP(20) q.creation_time,q.last_execution_time,q.execution_count,
q.total_spills,q.last_spills,q.max_spills,
q.total_spills*1.0/NULLIF(q.execution_count,0) AS SpillPagesPerExecution,
SUBSTRING(t.text,q.statement_start_offset/2+1,
(CASE WHEN q.statement_end_offset=-1 THEN DATALENGTH(t.text)
ELSE q.statement_end_offset END-q.statement_start_offset)/2+1) AS StatementText,
p.query_plan
FROM sys.dm_exec_query_stats AS q
CROSS APPLY sys.dm_exec_sql_text(q.sql_handle) AS t
OUTER APPLY sys.dm_exec_query_plan(q.plan_handle) AS p
WHERE q.total_spills>0
ORDER BY q.total_spills DESC;Compare Last, Maximum, and Typical Behavior
Read the three counters together. A large maximum beside a quiet last execution suggests variability. A consistently spilling statement needs a different review from an occasional parameter extreme. The average calculated from the total is useful context, but it can hide that variability.
What input caused the costly execution? Capture the relevant application parameters through the approved diagnostic process and compare them with the data distribution. Parameter-sensitive behavior, underestimated rows, and unexpectedly wide intermediate results can all affect a sort or hash operation.
Spill pages are a storage-work measure, not elapsed time. Do not translate a page count directly into a promised delay without actual execution evidence. Different storage paths and concurrent workloads behave differently. Check the completed actual plan's warnings, memory grant, estimates, and actual rows to connect the recorded counter with a concrete execution. The goal is to identify why the memory-consuming operator needed more than its granted workspace.
Cross-Check Retained Query Store Tempdb Evidence
Query Store runtime statistics expose avg_tempdb_space_used on supported versions. It describes average tempdb space use for the plan and aggregation interval, expressed in page-based units. That is related evidence, not the same definition as the cache spill counters.
The next query retains plan, interval, execution count, and text. It orders interval rows for investigation rather than pretending to calculate one lifetime query average. If you aggregate intervals later, weight their averages by execution count and preserve execution-type distinctions.
Query Store must have captured the relevant work. Missing intervals and evicted cache entries create different gaps in the evidence. Compare overlapping representative periods when possible. A tempdb allocation can also reflect work beyond the specific spill operation being investigated, so use the actual plan to confirm the mechanism. Matching large numbers from two views is not a substitute for matching their scope and meaning.
SELECT TOP(20) q.query_id,p.plan_id,i.start_time,i.end_time,
r.execution_type_desc,r.count_executions,r.avg_tempdb_space_used,qt.query_sql_text
FROM sys.query_store_runtime_stats AS r
JOIN sys.query_store_plan AS p ON p.plan_id=r.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
JOIN sys.query_store_runtime_stats_interval AS i ON i.runtime_stats_interval_id=r.runtime_stats_interval_id
WHERE r.avg_tempdb_space_used>0
ORDER BY r.avg_tempdb_space_used DESC;Fix the Estimate or Access Pattern Before the Symptom
Check statistics, join predicates, implicit conversions, and filter selectivity when actual rows differ greatly from estimates. A supporting index can remove a required sort or reduce the input before a hash operation. Narrowing unnecessary projected columns can reduce the workspace needed for intermediate rows.
Larger grants can reduce one query's spill while limiting concurrency for other queries. Do not make them the automatic first response. Memory-grant feedback in relevant recent versions can help repeated behavior, but it does not replace understanding the workload and estimate problem.
For a currently executing candidate, the grant DMV provides another scoped observation. The next query uses a selected session identifier and reports requested, granted, and maximum-used memory. Replace 51 with the session you are watching. These values apply to active grant context, not the historical cached statement counter. Match the request deliberately and retain the observation time if it will support a later comparison.
SELECT session_id,request_id,requested_memory_kb,granted_memory_kb,max_used_memory_kb,ideal_memory_kb
FROM sys.dm_exec_query_memory_grants
WHERE session_id=51;Verify the Change Over Comparable Executions
Retest representative normal and extreme inputs after the focused change. Capture actual plans, spill warnings, and your own IO and timing evidence. Check that reduced spilling did not create excessive grants, worse concurrency, or different results.
Compare cache counters only across understood lifetimes, and use Query Store windows with compatible workload conditions. A recompile resets counters and cannot by itself demonstrate improvement. Keep the new execution evidence beside the original candidate record.
Retain parameter values with each test result. A spill-free selective input cannot validate the large input that originally required more memory for sorting or hashing.
total_spills is useful for finding statements worth investigating. Separate total, last, and maximum behavior, locate the actual spilling operators, and review estimates and access paths before increasing grants. The successful fix serves the whole workload, not merely one counter that became smaller after a reset.
Related reading on this blog: Performance and TempDB Spills: SQL in Sixty Seconds 208 and SQL SERVER 2022: Persistence and Percentile Memory Grant Feedback.
A spill counter is not an operator diagnosis, it is a statement-level signal that needs a plan.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
