SQL Server Memory Grants and TempDB Spills Explained

Some query operators need working memory: sorts, hash joins and hash aggregates. Before the query runs, SQL Server reserves that memory as a memory grant. If the grant is too small, the work spills to tempdb and slows down. If it is too large, other queries wait in line for memory. Both problems usually come back to the same root cause.

SQL Server memory grants and spills explained

How the grant is sized

SQL Server estimates how much memory each memory-consuming operator needs, based on how many rows it expects and how wide those rows are. The grant is fixed when the query starts and cannot grow while it runs. So the accuracy of the estimate decides everything.

Problem 1: grant too small, so it spills

If the estimate is far too low, the sort or hash runs out of its memory and writes intermediate data to tempdb, then reads it back. That is far slower than memory. In an actual execution plan you will see a warning icon on the Sort or Hash Match operator, such as "Operator used tempdb to spill data during execution".

Typical causes: stale statistics, parameter sniffing, table variables in older versions, and complex filters the optimizer cannot estimate.

Problem 2: grant too large, so queries wait

If estimates are far too high, a query reserves memory it never uses. Under load, other queries cannot get their grants and wait with the RESOURCE_SEMAPHORE wait type, sometimes for many seconds, while plenty of memory sits idle inside oversized grants.

Typical causes: overestimated row counts, and very wide columns. For variable-length columns, SQL Server assumes each value is about half its declared size, so a NVARCHAR(4000) column that really holds 20 characters makes every row look huge.

Find grants in the execution plan

Open the properties of the root operator (the left-most SELECT) in an actual plan and expand MemoryGrantInfo. Compare GrantedMemory with MaxUsedMemory:

  • Granted far more than used: overestimate, wasting memory.
  • Used close to all of it plus a spill warning: underestimate.

See live grants with a DMV

SELECT
    mg.session_id,
    mg.requested_memory_kb / 1024 AS requested_mb,
    mg.granted_memory_kb   / 1024 AS granted_mb,
    mg.used_memory_kb      / 1024 AS used_mb,
    mg.wait_time_ms,
    t.text AS query_text
FROM sys.dm_exec_query_memory_grants mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) t
ORDER BY mg.requested_memory_kb DESC;

Rows with a NULL granted_mb are queries still waiting for memory.

How to fix it

  • Fix the estimates first. Update statistics, address parameter sniffing, and simplify predicates. Correct row estimates fix both kinds of problem at once.
  • Right-size column types. Do not declare NVARCHAR(MAX) or VARCHAR(8000) "just in case"; use realistic lengths, and select only the columns you need.
  • Avoid unnecessary sorts. An index that already provides the order can remove a Sort operator entirely, along with its grant.
  • Use query hints sparingly. OPTION (MIN_GRANT_PERCENT = x, MAX_GRANT_PERCENT = y) caps or raises one query's grant. Useful as a targeted fix, but it treats the symptom, not the cause.

Memory grant feedback

Newer versions can learn from mistakes. Memory grant feedback adjusts the grant on later executions of a cached plan when it was clearly too large or too small: introduced for batch mode in SQL Server 2017, extended to row mode in SQL Server 2019 (compatibility level 150), and improved in SQL Server 2022 with percentile-based adjustments and persistence in Query Store. It helps repeated queries, but it cannot fix a grant on the very first run.

Quick checklist

  • Look for spill warnings on Sort and Hash operators.
  • Watch for RESOURCE_SEMAPHORE waits under load.
  • Compare granted vs used memory in actual plans.
  • Fix estimates and column sizes before reaching for hints.
  • Use a recent compatibility level to benefit from memory grant feedback.

Related reading:

Comments

Post a Comment

Popular posts from this blog

How to Read a SQL Server Execution Plan (Beginner's Guide)

Image
When a query is slow, the execution plan tells you exactly what SQL Server did to run it: which indexes it used, how it joined tables and where the time went. Plans can look intimidating, but you only need a few habits to find the problem in most of them. Estimated vs actual plans SQL Server Management Studio (SSMS) can show two kinds of plan: Estimated plan (Ctrl+L): what SQL Server plans to do, without running the query. Useful for queries that are too slow or too risky to run. Actual plan (Ctrl+M, then run the query): the same plan plus what really happened, including actual row counts and warnings. Use this whenever you can. Turn on IO and time statistics at the same time so you have numbers to compare after each change: SET STATISTICS IO, TIME ON; -- run the query with "Include Actual Execution Plan" enabled SET STATISTICS IO, TIME OFF; Read from right to left Data flows from right to left. The operators on the far right read data from tables and indexes; each operato...
Read more

SARGable Queries in SQL Server: Why Functions in WHERE Slow You Down

Image
You add the perfect index, run the query, and SQL Server still scans the whole table. One of the most common reasons is a non-SARGable predicate: a WHERE clause written in a way that stops SQL Server from using the index to seek. The fix is usually a small rewrite. What SARGable means SARGable stands for S earch ARG ument- able . A predicate is SARGable when SQL Server can use it to jump straight to the matching rows in an index (an index seek). If you wrap the indexed column in a function or calculation, SQL Server can no longer search the index for a value; it has to calculate the expression for every row, which means a scan. The rule of thumb: leave the column bare on one side of the comparison , and do any calculation on the other side. Common non-SARGable patterns and their fixes Functions on date columns -- Not SARGable: YEAR() runs on every row WHERE YEAR(OrderDate) = 2026 -- SARGable: a range on the bare column WHERE OrderDate >= '2026-01-01' AND OrderDate < ...
Read more

SQL Server Statistics Explained: Why Your Query Plans Go Wrong

Image
When a query suddenly picks a terrible plan, the root cause is very often statistics . SQL Server's optimizer does not look at your data when it builds a plan; it looks at statistics about your data. If those are missing or out of date, it guesses wrong, and a wrong guess can mean a scan instead of a seek, the wrong join type or a memory spill. What statistics are A statistics object describes how values are distributed in one or more columns. SQL Server creates one automatically for every index, and, with AUTO_CREATE_STATISTICS on, for columns you filter or join on. The optimizer uses them to estimate how many rows each step will return, and those estimates drive almost every plan choice. How to read a statistics object DBCC SHOW_STATISTICS ('dbo.PayrollLine', 'IX_PayrollLine_Run_PayCode'); The output has three parts: Header: when the statistics were last updated, how many rows the table had, and how many rows were sampled . Density vector: how unique the column...
Read more