Sev1: SQL Server queries stuck waiting for memory — RESOURCE_SEMAPHORE and runaway memory grants

#Symptoms

• Application timeouts across many features; CPU and disk look normal.
• sys.dm_exec_requests shows many sessions with wait type RESOURCE_SEMAPHORE.
• Often after a deployment, a statistics change or a new report.

#1. Confirm memory grant waits

SELECT wait_type, COUNT(*) AS sessions, MAX(wait_time) AS max_wait_ms
FROM sys.dm_exec_requests
WHERE wait_type IS NOT NULL
GROUP BY wait_type
ORDER BY sessions DESC;

SELECT resource_semaphore_id, target_memory_kb, total_memory_kb, available_memory_kb,
       granted_memory_kb, grantee_count, waiter_count
FROM sys.dm_exec_query_resource_semaphores;

waiter_count > 0 with available_memory_kb near zero confirms it.

#2. Find who holds the memory

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

• Rows with grant_time = NULL are waiting.
• Rows with large granted_mb and small used_mb are over-estimated grants that block everyone else.

Get the plan for the top consumer:

SELECT g.session_id, p.query_plan
FROM sys.dm_exec_query_memory_grants g
CROSS APPLY sys.dm_exec_query_plan(g.plan_handle) p
WHERE g.session_id = 87;

Look at the SELECT operator's MemoryGrantInfo and at big Sort or Hash Match operators with wildly wrong row estimates.

#3. Immediate relief

Kill the worst offender if it is a report or batch that can be rerun:

KILL 87;

If a recent plan change caused it, force the previous good plan with Query Store:

SELECT q.query_id, p.plan_id, rs.avg_query_max_used_memory, rs.count_executions
FROM sys.query_store_query q
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
WHERE q.query_id = 4711;

EXEC sp_query_store_force_plan @query_id = 4711, @plan_id = 12;

#4. Cap grants for the bad query

Add a hint (or a Query Store hint on 2022 without changing code):

-- in code
SELECT ... OPTION (MAX_GRANT_PERCENT = 5);

-- SQL Server 2022 / Azure SQL: no code change
EXEC sys.sp_query_store_set_hints @query_id = 4711, @query_hints = N'OPTION (MAX_GRANT_PERCENT = 5)';

Warning

A grant that is too small makes sorts and hashes spill to tempdb, which is slower but doesn't block everyone. That is usually the right trade during an incident; tune properly afterwards.

#5. Cap grants for a whole workload

Enterprise Edition Resource Governor can limit grants per workload group:

ALTER WORKLOAD GROUP [default] WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 15);
ALTER RESOURCE GOVERNOR RECONFIGURE;

(The default is 25%. Use a dedicated group for reporting users through a classifier function rather than changing default permanently.)

#6. Root causes to fix afterwards

• Out-of-date or missing statistics causing huge estimates: update stats on the involved tables.
• SELECT * with wide nvarchar(max) columns in sorts.
• Memory grant feedback (2017+ batch mode, 2019+ row mode; persisted in 2022) helps repeat executions; make sure the database compatibility level allows it.
• Server max server memory set too low for the workload.

#Prevent it next time

• Alert on RESOURCE_SEMAPHORE waits and on waiter_count > 0.
• Keep Query Store on in read-write mode for production databases.
• Separate reporting onto a readable secondary or a reporting copy.
• Review new reports' plans before they reach production.