Sort and Hash Spills to TempDB: Find the Query and Fix the Grant

One query is slow some of the time, tempdb is busier than it should be, and the actual plan has a yellow triangle on the Sort or the Hash Match. That triangle is a spill. Here is how to read it, why the grant was too small, what SQL Server 2022 and 2025 now fix for you, and how to find the queries it does not fix.

The Warning: Operator Used TempDB to Spill Data

In SSMS the tooltip on the operator reads “Operator used tempdb to spill data during execution with spill level N and N spilled thread(s)”, followed by how many pages it wrote and read and the memory it was given. The same facts sit in the plan XML, and that is the version worth reading, because it is exact and you can search a saved plan for it. This is the warning from a Sort in a stored procedure on the lab instance, SQL Server 2025 CU8, with line breaks added:

Read it left to right. The sort was granted 1,024 KB and used all of it, which is the giveaway: a spilling operator usually shows used equal to granted, or very close. WritesToTempDb and ReadsFromTempDb are 8 KB pages, so 42,929 pages is about 335 MB written to tempdb and read straight back, for a query that returned 300,000 rows. The statement took 8.8 seconds, 3.8 of them on CPU. SpillLevel is the number of passes: MS Docs describes level 1 in the Sort Warnings event class as one extra pass over the data, and anything higher as multiple passes, each one rereading what the last one wrote. SpilledThreadCount is how many threads of a parallel operator spilled; it is 1 here because the plan was serial.

A Hash Match carries the same warning with HashSpillDetails instead. The lab version came from a hash aggregate estimated at 10 rows that met 25,000:

The opposite warning exists too. When the same procedure ran for a customer with 10 rows, its plan carried MemoryGrantWarning GrantWarningKind="Excessive Grant": 1,024 KB granted, 16 KB used. That one costs other queries memory rather than costing this query time, and it matters when workspace memory is short.

Why the Grant Was Too Small: Estimated Rows Against Actual Rows

A memory grant is sized once, at the start, from the optimizer’s estimate of how many rows reach the sort or hash and how wide they are. It does not grow while the query runs. So the first thing to check on the spilling operator is EstimateRows against ActualRows. For the Sort above:

Estimated 10, actual 300,000. The plan was compiled for customer 500, who has 10 order lines, and reused for customer 1, who has 300,000. That is parameter sniffing, and it is why a spill is so often “slow only sometimes”: the query spills when a big value reuses a plan built for a small one, and runs fine the rest of the time. These are the causes worth checking, roughly in the order they turn up:

• A sniffed parameter. ParameterCompiledValue differs from ParameterRuntimeValue in the plan XML, and the estimate matches the compiled value. The lab Sort above.
• Stale or skewed statistics. The estimate is wrong for every value, not just some. Rows were added since the last update, or one value owns most of the table and the histogram cannot say so. Get Statistics Health shows modification counts against the last update.
• A grant cap. The estimate is right but the plan asked for less than it wanted. Look for MAX_GRANT_PERCENT in the query or a resource governor workload group with a low REQUEST_MAX_MEMORY_GRANT_PERCENT. In the plan, RequestedMemory sits well under DesiredMemory in MemoryGrantInfo.
• A grant that waited and gave up. When workspace memory is short, a query queues on RESOURCE_SEMAPHORE, and when the wait times out it can run with only its minimum. On the lab, with memory under pressure from the host, one parallel sort showed GrantWaitTime="67007" (milliseconds), DesiredMemory="39232" and GrantedMemory="4864", its required minimum, and spilled 2,044 pages across 8 threads. The estimate was right. The memory was not there.
• Rows wider than estimated. The optimizer guesses the width of varchar columns at half their declared size, so a sort over mostly full varchar(4000) columns can be under-granted with a perfect row count. Documented, not reproduced here.

MemoryGrantInfo on the root of the actual plan has the rest: DesiredMemory is what the estimate called for, RequestedMemory what was asked for after any cap, GrantedMemory what arrived, MaxUsedMemory what was used, and GrantWaitTime how long it queued first. A spill with desired and granted equal is an estimate problem. A spill with granted well under desired is a cap or a memory shortage.

Memory Grant Feedback Fixes Repeat Spills, Unless the Grant Is Capped

Memory grant feedback arrived in SQL Server 2017 (compatibility level 140) for batch mode operators only, and since SQL Server 2019 (compatibility level 150) row mode queries get it too: after a spill, the next execution of the same cached plan asks for more. SQL Server 2022 adds two things at compatibility level 140 and up, as MS Docs describes on the memory grant feedback page: the adjustment is based on a percentile of recent runs instead of only the last one, and it is persisted in Query Store, so it survives a plan being evicted or the instance restarting. Both are on by default in a database with Query Store on. The plan XML says what feedback did through IsMemoryGrantFeedbackAdjusted in MemoryGrantInfo.

To watch it, I ran two procedures 3 times each against a 25,000-row table on the lab, compatibility level 170. The first sorts with OPTIMIZE FOR (@CustomerId = 500), a hint that pins the 10-row estimate on every call. The second sorts 45,000 rows with an accurate estimate and OPTION (MAX_GRANT_PERCENT = 1):

The bad estimate spilled once and was fixed on the next call with the estimate still saying 10 rows. That is feedback doing its job, and it is why a spill you saw in yesterday’s plan may simply not be there today. The capped query spilled every time, and feedback called its grant Accurate, because the query used all of the 888 KB it was allowed. Feedback cannot raise a grant above a hint or a resource governor cap, so a MAX_GRANT_PERCENT added to calm RESOURCE_SEMAPHORE waits turns into a spill on every execution, and nothing in the engine will undo it.

Feedback is not free either. After the 42,929-page spill above, the next call of that procedure asked for 89,968 KB against an ideal of 203,072 KB, in sys.dm_exec_query_memory_grants. On a lab instance with about 11 MB of workspace memory at the time, that request queued on RESOURCE_SEMAPHORE until I cancelled it. A sniffed plan that swings between 10 rows and 300,000 is the case feedback handles worst: MS Docs says it disables itself for a plan whose grants keep oscillating, and then you are back to fixing the cause.

Find Which Queries Are Spilling Across the Server

The actual plan only shows the run you captured. For the whole instance there are two places to look, and they fail in different ways, so use both.

The Plan Cache: total_spills in sys.dm_exec_query_stats

sys.dm_exec_query_stats has total_spills, last_spills, min_spills and max_spills on SQL Server 2016 SP2, 2017 CU3 and later, counted in 8 KB pages, next to the granted, used and ideal grant columns. Get-MemoryGrantSpills ranks every cached query by those columns with a diagnosis per row. It is in Get Query Performance Deep-Dive, with an example run that caught a sniffed procedure spilling 19,000 pages over 9 executions.

Its limit is the plan cache. The counters live with the cached plan, so an eviction takes the evidence with it. On the lab, under external memory pressure (process_physical_memory_low = 1 in sys.dm_os_process_memory, SQL Server holding 212 MB), sys.dm_exec_query_stats held 0 rows for the test database straight after three spilling executions, and Get-MemoryGrantSpills returned 0 rows. Zero rows means nothing cached has spilled, not that nothing spilled.

Query Store: the Feedback That Survives

On SQL Server 2022 and later, persisted feedback lands in sys.query_store_plan_feedback. A memory grant feedback row there means the engine saw a wrong grant for that plan and stored a correction, which in practice is mostly spills. It is per database, it survives the cache flush that emptied the check above, and it outlives a restart. This script lists those rows for the current database with the plan’s Query Store memory and tempdb figures:

/*
Script Name : Get-MemoryGrantFeedback
Category    : performance
Purpose     : Queries in the current database that memory grant feedback has had to correct,
              read from Query Store (sys.query_store_plan_feedback). A row means the engine saw
              a grant that was wrong for this query, usually too small and spilling a sort or
              hash to TempDB, and stored an adjustment. Unlike sys.dm_exec_query_stats this
              survives a restart and a plan cache flush, because feedback persistence keeps it
              in Query Store. Shows the stored adjustment with the query's Query Store memory
              and TempDB figures.
              Run in the context of the target database (-Database <dbname>).
Author      : Peter Whyte (https://sqldba.blog)
Requires    : VIEW DATABASE PERFORMANCE STATE (SQL Server 2022+) or VIEW DATABASE STATE.
              SQL Server 2022 or later with Query Store on; earlier builds have no
              sys.query_store_plan_feedback.
Notes       : feedback_data is the engine's own JSON, shown as stored (AdditionalMemoryKB per
              plan node). It is not parsed here because OPENJSON fails on databases below
              compatibility level 130. Memory and TempDB columns are Query Store averages
              across the plan's retained intervals, converted from 8 KB pages to MB; TempDB
              space includes temp tables as well as spills.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
SET QUOTED_IDENTIFIER ON;

IF OBJECT_ID('sys.query_store_plan_feedback') IS NULL
BEGIN
    SELECT 'Memory grant feedback persistence (sys.query_store_plan_feedback) needs SQL Server 2022 or later. '
         + 'This instance is ' + CONVERT(varchar(32), SERVERPROPERTY('ProductVersion')) + '.' AS info;
END
ELSE IF ISNULL((SELECT actual_state_desc FROM sys.database_query_store_options), 'OFF')
        NOT IN ('READ_WRITE', 'READ_ONLY')
BEGIN
    SELECT
        DB_NAME() AS current_database,
        ISNULL((SELECT actual_state_desc FROM sys.database_query_store_options), 'OFF') AS query_store_status,
        'Feedback is only persisted with Query Store on: ALTER DATABASE [' + DB_NAME()
        + '] SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE)' AS action;
END
ELSE
BEGIN
    -- Dynamic SQL so the batch still compiles on builds without the view.
    DECLARE @sql nvarchar(max) = N'
    SELECT
        DB_NAME() AS database_name,
        p.query_id,
        f.plan_id,
        OBJECT_NAME(q.object_id) AS object_name,
        f.state_desc AS feedback_state,
        f.feedback_data,
        f.last_updated_time AS feedback_updated,
        rs.executions,
        CAST(rs.avg_used_memory_pages * 8 / 1024.0 AS DECIMAL(10,2)) AS avg_used_memory_mb,
        CAST(rs.max_used_memory_pages * 8 / 1024.0 AS DECIMAL(10,2)) AS max_used_memory_mb,
        CAST(rs.avg_tempdb_pages * 8 / 1024.0 AS DECIMAL(10,2)) AS avg_tempdb_mb,
        CAST(rs.max_tempdb_pages * 8 / 1024.0 AS DECIMAL(10,2)) AS max_tempdb_mb,
        p.last_execution_time,
        LEFT(qt.query_sql_text, 400) AS query_text
    FROM sys.query_store_plan_feedback AS f
    JOIN sys.query_store_plan AS p ON p.plan_id = f.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
    OUTER APPLY (
        SELECT
            SUM(r.count_executions) AS executions,
            SUM(r.avg_query_max_used_memory * r.count_executions)
                / NULLIF(SUM(r.count_executions), 0) AS avg_used_memory_pages,
            MAX(r.max_query_max_used_memory) AS max_used_memory_pages,
            SUM(r.avg_tempdb_space_used * r.count_executions)
                / NULLIF(SUM(r.count_executions), 0) AS avg_tempdb_pages,
            MAX(r.max_tempdb_space_used) AS max_tempdb_pages
        FROM sys.query_store_runtime_stats AS r
        WHERE r.plan_id = f.plan_id
    ) AS rs
    WHERE f.feature_desc LIKE N''Memory Grant%''
    ORDER BY f.last_updated_time DESC;';

    EXEC sys.sp_executesql @sql;
END;

Run it in the database you suspect, or from the repo with .\run.ps1 Get-MemoryGrantFeedback -Database YourDb. It needs VIEW DATABASE STATE, or VIEW DATABASE PERFORMANCE STATE on 2022 and later, and changes nothing. On the lab, run against the test database after the runs in the table above, it returned one row, shown here without the database name, ids, timestamps and query text:

GetCustomerOrderLines is the bad-estimate procedure: feedback added 6,144 KB to node 0, the Sort, and the 3.50 MB maximum in tempdb is the one run that spilled. The capped procedure, which spilled on every run, has no row at all, because feedback never changed it. So the two checks cover each other: Query Store finds the spills feedback fixed, even after the cache has forgotten them, and the plan cache finds the ones it could not fix, while they are still cached. Neither replaces the actual plan of the query someone is complaining about.

Fix the Estimate, Not the Memory

Adding memory to the server feels like the fix and rarely is. The grant was sized from an estimate, and a bigger server sizes the same wrong grant. Work through these instead:

• Fix the estimate. Update statistics on the tables feeding the operator, with FULLSCAN where the data is skewed, then compare estimated and actual rows again. For a sniffed parameter, the options are in Parameter Sniffing in SQL Server; on SQL Server 2022 at compatibility level 160 and up, parameter sensitive plan optimization can keep separate plans for small and large values by itself.
• Remove the sort or shrink the hash. An index that delivers rows in the order the query wants takes the Sort out of the plan, and selecting fewer or narrower columns shrinks what a sort or hash has to hold. No operator, no grant, no spill.
• Remove or raise a grant cap. If the plan shows RequestedMemory far under DesiredMemory, find the MAX_GRANT_PERCENT hint or the workload group setting. It was probably added to stop one query hogging workspace memory, and if so it needs a value that fits this query too.
• Give it a floor, as a splint. OPTION (MIN_GRANT_PERCENT = n), from the query hints page, guarantees a minimum grant for one query whose estimate you cannot fix yet. Every execution then takes that much workspace memory, so it moves the problem toward RESOURCE_SEMAPHORE if it is set high.
• Make the spills you cannot avoid cheaper. A spill is tempdb I/O. Fast tempdb storage does not stop it, but it decides whether a spill costs milliseconds or seconds. IO_COMPLETION climbing with spills is that cost showing up in wait statistics, and Get TempDB Hotspots shows which sessions hold internal object space while it happens.

Then run the query again and check the operator. No triangle, and MaxUsedMemory comfortably under GrantedMemory, is the fix confirmed. Look one run later as well on 2019 and up, because feedback can hide a spill on the second run that comes back the next time the plan is compiled for a different value.

Frequently Asked Questions