Every weekday at 9:05 the order system's p99 latency went from forty milliseconds to eleven seconds, sat there for four minutes, and recovered on its own. Not blocking, not CPU, not disk. The wait stats said RESOURCE_SEMAPHORE, and the cause was a finance report requesting a twelve-gigabyte memory grant on a sixty-four-gigabyte instance. While it held that reservation, every other query needing memory to sort or hash stood in a queue, and that queue was the outage.
Memory grants are the least understood resource in SQL Server because they are invisible until they hurt. The buffer pool gets dashboards; the grant queue gets nothing. Here is how grants are actually sized, how I triage the queue when it forms, and how to fence the one-report-kills-OLTP pattern so it stops being a daily incident on SQL Server 2017 through 2022.
What is a memory grant and how is it sized?
A memory grant is workspace memory the query processor reserves for operators that need buffers: sorts, hash joins and aggregates, and columnstore builds in batch mode. The optimizer sizes the request at compile time from its cardinality estimates: each memory-hungry operator contributes an input size — estimated rows times estimated row width — and the plan adds a required floor, the minimum those operators need to run at all, plus additional memory up to what they could use, with parallel plans stacking per-thread exchange buffers and per-thread copies of operators like sorts on top. Every clause of that sentence is a failure mode. Overestimated rows inflate the grant and crowd out other queries. Underestimated rows spill to tempdb. And a DOP of sixteen inflates whatever the estimate said through those per-thread components, whether the estimate deserved it or not.
The reservation system handing out this memory is the resource semaphore. It caps the total grantable pool at a large fraction of target memory, caps any single query at twenty-five percent of the pool by default, and queues any request that does not fit what is currently free. Very small requests get a separate fast lane, the small query semaphore, so a thousand tiny lookups never queue behind one monster; anything above a few megabytes joins the main queue. That queue is the RESOURCE_SEMAPHORE wait. It is throttling working exactly as designed, and the design assumes the estimates are honest.
How do I triage RESOURCE_SEMAPHORE waits?
Confirm the wait is real first: sys.dm_os_wait_stats should show RESOURCE_SEMAPHORE climbing during the incident window and sitting near the top of the profile. Then go straight to sys.dm_exec_query_memory_grants, which lists every session that has requested, received, or is still waiting for a grant, with wait_time_ms showing exactly who has been standing in line and for how long. The column pair that tells the real story is granted_memory_kb versus used_memory_kb and its high-water sibling max_used_memory_kb — used is only the instant you happen to read the DMV, so judge a running query by the max. A session granted twelve gigabytes whose max never climbed past forty megabytes is the inflated-estimate case, and it is both victim and perpetrator: its unused reservation is what everyone else queues behind.
SELECT mg.session_id,
mg.wait_time_ms,
mg.dop,
mg.requested_memory_kb,
mg.granted_memory_kb,
mg.used_memory_kb,
mg.max_used_memory_kb,
SUBSTRING(st.text, 1, 200) AS statement_start
FROM sys.dm_exec_query_memory_grants AS mg
CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) AS st
ORDER BY mg.granted_memory_kb DESC;
SELECT pool_id,
total_memory_kb,
available_memory_kb,
granted_memory_kb,
used_memory_kb,
grantee_count,
waiter_count,
timeout_error_count,
forced_grant_count
FROM sys.dm_exec_query_resource_semaphores;The second result set, from sys.dm_exec_query_resource_semaphores, is the queue's vital signs. waiter_count above zero means queries are queued right now. timeout_error_count counts queries that gave up waiting and raised error 8645, the "timed out waiting for memory resource" message users paste into tickets. forced_grant_count counts queries pushed through with the minimum grant, and forced minimums are where spills are born. On the system from the opening story, timeout_error_count incremented by two hundred every weekday morning. That counter was the incident, quantified, and it took one query to find.
Spills versus waits: which side of the knife am I on?
A query granted too little memory spills; a query asking for too much makes everyone else wait. Spills surface in execution plans as Sort Warnings and Hash Warnings, mid-execution writes to tempdb, and since SQL Server 2016 SP2 and 2017 CU3 the spill columns in sys.dm_exec_query_stats quantify them per statement. The connection people miss is that these are the same disease seen from two sides. Underestimation forces spills onto tempdb; the lazy fix, bigger estimates and bigger grants, inflates reservations and builds the queue. On that finance report, the hash spill wrote nine gigabytes to tempdb while its reservation starved the pool. The tempdb half of that story, and why eight evenly sized files matter, is in my notes on tempdb contention.
The fix direction depends on which side you are on, and granted-versus-used tells you. Granted far above used means the estimate ran high: fix the statistics or the non-sargable predicate that poisoned it. Used close to granted and still spilling means the estimate ran low: same medicine from the other direction. Treat the grant numbers as an estimate autopsy. They show you how far the optimizer's arithmetic and reality parted ways for the request as a whole, per execution — the per-operator breakdown lives in the actual execution plan, not in this DMV — and they do it without a single trace flag.
What does memory grant feedback change?
Memory grant feedback closes the loop: the engine remembers what an execution actually used and sizes the next grant from history instead of from the estimate alone. It arrived for batch-mode plans in SQL Server 2017, for row-mode plans in 2019, and 2022 made it persistent, surviving plan-cache eviction through Query Store, and added percentile-based sizing so one outlier execution cannot poison the average. At compatibility level 160 with Query Store enabled it simply works. The recidivist spillers on my 2022 systems went quiet without a single index change, and the morning grant queue shrank because feedback also trims grants that were absurdly large.
It is not a substitute for fixing estimates. Feedback corrects one query at a time, at re-execution, with a bounded number of adjustments before it gives up on oscillating values, and it does nothing for a first execution or for the systemic case where a hundred different queries all overestimate at 9:05. Think of it as a thermostat in a building with bad insulation. You still fix the insulation.
How do I fence the one-report-kills-OLTP pattern?
With Resource Governor, because hints do not scale across an application you do not fully control. The single knob that matters is REQUEST_MAX_MEMORY_GRANT_PERCENT on the workload group, capping any one grant at a percentage of the grantable pool; the default of twenty-five on a big instance is still a monster. A classifier function routes the reporting login or application name into a group capped at five percent, and the report either fits or spills into its own corner while OLTP never queues behind it again. For the one procedure I own and cannot otherwise fix, the per-query counterpart is OPTION (MAX_GRANT_PERCENT = 5) — analogous, not identical: the hint is a percentage of the instance's configured memory limit rather than of the pool's query workspace, and Resource Governor still caps it from above.
ALTER WORKLOAD GROUP ReportingWG
WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 5);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO
-- the per-query version, inside the procedure that earned it
SELECT customer_id, SUM(order_total) AS total
FROM Sales.Orders
GROUP BY customer_id
OPTION (MAX_GRANT_PERCENT = 5);Set the fence before you tune the report, and I mean that as sequencing advice paid for in pain. I once spent a week optimizing the finance query while the business ate the 9:05 brownout every morning; the Resource Governor change was a twenty-minute deployment that would have made the tuning week leisurely. Fence first, then tune. Confirming the fence worked is the same loop from my wait statistics triage notes: watch RESOURCE_SEMAPHORE drain out of the top five waits and stay there for a week of 9:05s.
Watching the grant queue with MonPG when SQL Server support lands
The counters worth graphing are waiter_count and forced_grant_count over time, granted-versus-used divergence per query, and spill pages per statement, because the queue is visible in telemetry long before users feel the brownout. MonPG monitors PostgreSQL in production today; SQL Server support is in active development, and memory-grant telemetry is high on the list of things it should make boring. Status lives on the SQL Server monitoring (coming soon) page. Until it ships, dm_exec_query_memory_grants ordered by granted_memory_kb descending is the query that has saved my mornings.