SQL Server Memory Grant Pressure

Why this problem matters

Queries requesting large memory grants for sorts and hashes can make other requests wait on RESOURCE_SEMAPHORE. Incorrect row estimates can increase the risk of over-granting or spilling to disk.

How to diagnose it

Inspect requested, granted, and used memory plus wait time for current grant requests together with query text. Sample the number of waiting requests over time to determine whether pressure is brief or persistent.

The query shows only requests currently waiting for or using memory grants. Completed queries and brief waits require Query Store, plans, or continuous sampling.

SELECT mg.session_id,
       mg.request_time,
       mg.grant_time,
       mg.requested_memory_kb,
       mg.granted_memory_kb,
       mg.required_memory_kb,
       mg.used_memory_kb,
       mg.max_used_memory_kb,
       mg.wait_time_ms,
       mg.dop,
       st.text AS batch_text
FROM sys.dm_exec_query_memory_grants AS mg
OUTER APPLY sys.dm_exec_sql_text(mg.sql_handle) AS st
ORDER BY mg.wait_time_ms DESC, mg.requested_memory_kb DESC;

A safe solution approach

Reduce unnecessary workspace demand by correcting row estimates, statistics, query shape, and indexes. Resource governance or query hints should be considered only with measured impact and narrow scope.

Why continuous monitoring matters

Memory-grant queues can form quickly during specific reports or concurrent query groups. moon can continuously monitor this pressure and route prolonged-condition alerts to Slack, PagerDuty, or webhooks.