Catch Memory Grant Waits via sys.dm_exec_query_memory_grants

Finding the one query stuck behind a RESOURCE_SEMAPHORE wait starts with a plain fact most dashboards gloss over: a query waiting on a memory grant isn't failing yet — it's queued, holding a worker thread while SQL Server decides who gets the next slice of execution memory. If that wait outlasts the query's own timeout clock, it gives up and errors out instead of finishing, having done no real work at all. Reading the queue before the clock runs out is the difference between tuning a slow query and explaining a timeout nobody saw coming.

Purpose and Overview

Diagnosing a memory-grant timeout is really a two-layer job: catch the specific queries sitting in the wait queue right now, then step back to the instance-wide numbers that explain why the queue exists at all. sys.dm_exec_query_memory_grants handles the first layer — it returns a row for every query that has requested, is waiting for, or has already received a memory grant, on SQL Server, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics (under a node-level name there), and SQL database in Microsoft Fabric. Serverless SQL pool in Azure Synapse Analytics doesn't support it. Queries that don't need a memory grant never appear here: a query with an ORDER BY typically does, one without typically doesn't.

The columns split cleanly by what they describe. request_time and grant_time bound the wait (grant_time reads NULL until memory is handed over); requested_memory_kb, required_memory_kb, granted_memory_kb, used_memory_kb, and max_used_memory_kb trace the gap between what the optimizer asked for and what the query used; ideal_memory_kb is the size that would let the whole operation fit in physical memory based on the cardinality estimate; and timeout_sec is the number of seconds before the query gives up the request on its own. wait_order, queue_id, and is_next_candidate describe position in the queue. wait_order can change as other queries get grants or time out ahead of it, and is_next_candidate flags the one query next in line. One column needs care across versions: resource_semaphore_id was unique only before SQL Server 2008 (10.0.x); from 2008 onward it is not, so a script that treats it as a unique key per row breaks on any current instance. Reading the view requires VIEW SERVER STATE on SQL Server (SQL Server 2022 and later narrow that to VIEW SERVER PERFORMANCE STATE), or VIEW DATABASE STATE in the database on Azure SQL Database.

"Memory grant" gets used loosely, so it helps to pin down what creates one of these rows. Microsoft's troubleshooting guide for memory grant issues names four: sort operations, hash operations (joins or aggregates), bulk copy operations, and index creation — including inserts into columnstore indexes, which build hash dictionaries at runtime. A plain SELECT * FROM sys.messages ORDER BY message_id, pulling over 300,000 rows into a sort, is the guide's own example of a query that induces exactly this kind of grant. The memory involved is temporary and goes back to the engine once the sort or hash operation finishes, but on a large rowset that operation can run for seconds or minutes, and the grant is held for the query's entire lifetime, not released piecemeal.

Memory grants aren't handed out from one shared pool with no structure behind it. Resource Governor lets an administrator split server resources across up to 64 resource pools, and starting with SQL Server 2008 (10.0.x) each pool behaves like its own small independent instance and needs two semaphores of its own — which is also the direct reason resource_semaphore_id stopped being unique that same release, and why a pool-aware server can return up to 20 times more rows from the resource-semaphore view than SQL Server 2005 ever did. group_id and pool_id on sys.dm_exec_query_memory_grants tie a specific waiting or granted query back to the workload group and resource pool it's running under, which matters once more than the default pool is in play.

Code Breakdown

The first query is the one that answers "what's queued right now" — every row where grant_time is still NULL, ordered by position in line.

 1SELECT
 2    mg.session_id,
 3    mg.request_time,
 4    mg.requested_memory_kb,
 5    mg.required_memory_kb,
 6    mg.ideal_memory_kb,
 7    mg.query_cost,
 8    mg.timeout_sec,
 9    mg.wait_order,
10    mg.is_next_candidate,
11    DATEDIFF(SECOND, mg.request_time, GETDATE()) AS seconds_waiting
12FROM sys.dm_exec_query_memory_grants AS mg
13WHERE mg.grant_time IS NULL
14ORDER BY mg.wait_order;

Reading wait_order against timeout_sec before the clock runs out

seconds_waiting is a derived column the base view doesn't supply. Set beside timeout_sec, it shows how many seconds each waiting query has left. A row with is_next_candidate = 1 is the one SQL Server will grant memory to next as soon as any becomes available; everything behind it is still waiting on wait_order, and that order can shift if a query ahead of it times out or finishes. required_memory_kb is the floor — requested_memory_kb is always the same or larger — so a query where the two are close is a tighter, more realistic ask than one requesting far beyond what it strictly needs.

The second query shifts to queries that already have memory, joining to the live request and its text so the one consuming the most memory is identifiable, not just its session ID.

 1SELECT
 2    r.session_id,
 3    r.status                AS request_status,
 4    r.wait_type,
 5    mg.grant_time,
 6    mg.requested_memory_kb,
 7    mg.granted_memory_kb,
 8    mg.used_memory_kb,
 9    mg.max_used_memory_kb,
10    mg.ideal_memory_kb,
11    t.text                  AS query_text
12FROM sys.dm_exec_requests AS r
13INNER JOIN sys.dm_exec_query_memory_grants AS mg
14    ON mg.session_id = r.session_id
15OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
16WHERE mg.granted_memory_kb > 0
17ORDER BY mg.granted_memory_kb DESC;

used_memory_kb vs granted_memory_kb — spotting a bad estimate either direction

The gap between granted_memory_kb and max_used_memory_kb runs both ways, and both are a problem. If the optimizer overestimates, a query gets granted far more than it uses — memory that sat idle instead of going to another query in the wait queue above. If it underestimates, required_memory_kb and the actual grant fall short of what's needed, and rather than failing outright, SQL Server spills the excess rows to a tempdb workfile or worktable instead of handing out more memory than was originally requested. Either direction shows up clearly once granted_memory_kb and max_used_memory_kb sit side by side in the same row — a wide gap in either direction is the signal that the query's cardinality estimate, not the DMV, is the actual problem.

The resource-semaphore view is the pool-wide companion to both queries above — where they show individual queries, this shows whether the pool itself is under pressure.

 1SELECT
 2    pool_id,
 3    resource_semaphore_id,
 4    total_memory_kb,
 5    available_memory_kb,
 6    granted_memory_kb,
 7    used_memory_kb,
 8    grantee_count,
 9    waiter_count
10FROM sys.dm_exec_query_resource_semaphores;

Reading waiter_count and the small-vs-regular semaphore split

A nonzero waiter_count on this view means something, somewhere, is queued for memory in that pool right now — it's the single fastest check for "is this even happening currently" before running either query above. resource_semaphore_id = 0 marks the regular semaphore for a pool; a separate small-query semaphore carries its own ID, and Microsoft's troubleshooting guide walks through a worked example where the default pool's regular semaphore shows roughly 900 MB granted across 22 live grantees with 3 more waiting, while that same pool's small-query semaphore sits at zero — two independent queues inside one pool, not one.

A fourth query confirms the memory is tied up where this script assumes it is, rather than somewhere else in the engine entirely.

1SELECT
2    type,
3    SUM(pages_kb) / 1024 AS pages_mb
4FROM sys.dm_os_memory_clerks
5WHERE type = 'MEMORYCLERK_SQLQERESERVATIONS'
6GROUP BY type;

Confirming the allocation with the MEMORYCLERK_SQLQERESERVATIONS clerk

Every memory grant is tracked internally by a specific memory clerk — MEMORYCLERK_SQLQERESERVATIONS — and querying sys.dm_os_memory_clerks filtered to that one type confirms the total reservation memory in use independent of the per-query view. If this number is large but the two query-level results above look clean, the pressure is coming from somewhere other than the queries this script is checking.

The last query pulls the execution plan behind a granted query, which carries its own memory-grant detail the DMV columns don't show directly.

1SELECT
2    mg.session_id,
3    mg.granted_memory_kb,
4    qp.query_plan
5FROM sys.dm_exec_query_memory_grants AS mg
6CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) AS qp
7WHERE mg.granted_memory_kb > 0;

Reading MemoryGrantInfo inside the plan XML

The returned plan's MemoryGrantInfo element carries a fuller breakdown than the DMV row alone — Microsoft's troubleshooting guide shows a real example with SerialRequiredMemory="512", DesiredMemory="41232", RequestedMemory="46016", GrantedMemory="46016", and MaxUsedMemory="45816" all on one query, plus an IsMemoryGrantFeedbackAdjusted attribute reading No: First Execution on that run. Pulling the plan is the next step once the DMV-level numbers flag a query worth a closer look — it's where "requested vs. granted vs. actually used" turns into the same numbers Management Studio's own graphical plan would show, without opening the plan by hand.

Key Benefits and Use Cases

• Separates "waiting" from "granted" in one query — filtering on grant_time IS NULL isolates the exact queries stuck in the queue right now, without scanning every active request.
• A real countdown, not a guess — comparing seconds_waiting against timeout_sec shows how close a specific wait is to erroring out, before it does.
• Catches both estimate failures, not just one — granted_memory_kb vs. max_used_memory_kb surfaces overestimates (wasted memory) and underestimates (tempdb spills) in the same column pair.
• Pool-level and query-level in the same toolkit — sys.dm_exec_query_resource_semaphores answers "is the pool under pressure" before drilling into which query is responsible.
• No setup required — every query here runs against a stock instance; no trace flag, Extended Events session, or Resource Governor configuration has to exist first.
• Extends cleanly into the plan — sys.dm_exec_query_plan on the same plan_handle goes one level deeper into the same memory-grant numbers without a second diagnostic pass.

Performance Considerations

• Memory-only, resets on restart: like every sys.dm_exec_* and sys.dm_os_* view, nothing here is retained once the Database Engine restarts — a history table populated on a schedule is the only way to see a trend.
• Permission floor changed in SQL Server 2022: VIEW SERVER STATE covers SQL Server 2019 and earlier; SQL Server 2022 and later require the narrower VIEW SERVER PERFORMANCE STATE instead.
• resource_semaphore_id lost its uniqueness in SQL Server 2008: a script written against an older instance that assumes a unique ID per row needs rework before it's trusted on a current one.
• Up to 64 resource pools, each with its own two semaphores: on an instance using Resource Governor heavily, sys.dm_exec_query_resource_semaphores can return far more rows than a single-pool mental model expects.
• DMV queries with ORDER BY or aggregates add their own memory pressure: Microsoft's own remarks on the view caution that a troubleshooting query itself can contribute to the condition it's investigating — keep these queries lean.

Practical Tips

• Schedule the first two queries as a recurring SQL Server Agent job writing to a history table — a single ad hoc run only ever catches whatever is queued or granted at that exact second.
• Check sys.dm_os_wait_stats for cumulative RESOURCE_SEMAPHORE wait time fleet-wide before drilling into this view — Microsoft names that as the specific wait type behind this whole condition, and it's the faster first signal.
• Alert on waiter_count > 0 from sys.dm_exec_query_resource_semaphores rather than waiting for a timeout to happen — it's the pool-wide leading indicator, not a per-query guess.
• Before assuming a bad grant is the query's fault, confirm overall system memory with sys.dm_os_sys_info and the MEMORYCLERK_SQLQERESERVATIONS clerk total — a starved instance produces the same symptom as one bad query.
• For the query-rewrite side of a persistently bad estimate (updating statistics, adding a query hint, or restructuring the sort or join driving the grant), the Memory Grants Week series in the References below covers the query-writing angle once the DMV work above has named the query.

Conclusion

sys.dm_exec_query_memory_grants replaces "something timed out" with a list: who is waiting, how much each asked for, and how many seconds are left before the request gives up. Paired with sys.dm_exec_query_resource_semaphores for the pool-wide view and sys.dm_os_memory_clerks to confirm where the memory went, it's the first stop for a RESOURCE_SEMAPHORE wait before reaching for a memory upgrade or a Resource Governor change that may not be the actual fix.

References

• sys.dm_exec_query_memory_grants (Transact-SQL) — Microsoft Learn — Full column reference, permissions by version, and the resource_semaphore_id uniqueness note.
• Troubleshoot slow performance or low memory issues caused by memory grants — Microsoft Learn — What triggers a memory grant, the resource-semaphore worked example, and the MemoryGrantInfo plan XML example.
• Memory Grants Week: Introduction to Query Memory — Brent Ozar Unlimited — A multi-part series on diagnosing and fixing query memory grants from the query-writing side.