Diagnose SQL Server query memory grants under load

A reporting query can finish quickly when run alone and cause a queue when several users run it together. One explanation is workspace memory: queries reserve memory for operations such as sorting and hashing. Optimizing the duration of one isolated execution can miss the amount of memory it holds and the time other requests spend waiting.

Capture the queue and its occupants

Start during the incident, not after every report has finished. The following query shows active memory requests alongside current request information. It is read-only, but server-wide diagnostic visibility requires appropriate permissions. SQL Server 2022 and later use VIEW SERVER PERFORMANCE STATE for this DMV; earlier versions generally require VIEW SERVER STATE.

SELECT
    mg.session_id,
    mg.request_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,
    r.status,
    r.wait_type,
    r.total_elapsed_time,
    txt.text AS batch_text
FROM sys.dm_exec_query_memory_grants AS mg
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = mg.session_id
   AND r.request_id = mg.request_id
OUTER APPLY sys.dm_exec_sql_text(mg.sql_handle) AS txt
ORDER BY mg.requested_memory_kb DESC;

The text is the batch, so a stored procedure may contain more than the statement responsible for the grant. Use the request's statement offsets or an execution plan when narrowing the investigation. Keep the session and request identifiers together, and do not assume a later observation of the same session represents the same work.

A missing grant_time identifies a request still awaiting its grant. Compare the requested and granted kilobytes with the currently used and maximum used amounts. These are live observations, not a completed history. A query observed near its start can legitimately use little of the reservation so far. Save several timestamped samples and correlate them with the incident window.

RESOURCE_SEMAPHORE waits point toward execution-memory admission. They are different from RESOURCE_SEMAPHORE_QUERY_COMPILE, which concerns compilation. Also inspect requests that already hold large grants. The waiting query may be the victim, while a different query occupies the workspace.

Separate three different problems

An oversized grant reserves substantially more than the execution needs. A small grant can cause a sort or hash operation to spill intermediate data to tempdb. A queue can occur even when individual grants are reasonable, simply because too many large operations overlap. These situations need different remedies, and a single server memory percentage does not distinguish them.

For a hypothetical report requesting 600 MB and repeatedly using only 25 MB, investigate the row estimate and the width of the rows carried into memory-intensive operators. Do not label it waste based on one early sample. Confirm the completed actual plan's memory information and repeat with the parameter values that produce large result sets.

For a spilling query, compare estimated and actual input rows at the relevant operator. An underestimated join result can grow the hash input far beyond the planned size. A wide projection can make a moderate row count expensive. Returning only necessary columns before sorting or aggregating can reduce demand, but verify that a rewrite preserves the query's meaning.

An index that supplies required order can sometimes remove a sort. Pre-aggregating at the correct grain can reduce the rows entering a join. Neither is a universal prescription: the index has write costs, and an invalid aggregation rewrite can produce convincing but incorrect totals.

Validate at the concurrency level that matters

Run the representative report mix concurrently, with realistic parameters, and measure completed requests per minute and tail latency as well as individual query duration. A smaller reservation that creates more tempdb work may improve admission while worsening total throughput. A faster isolated execution with a much larger grant may do the reverse.

Memory grant feedback can adjust grants across executions in supported versions and execution modes. Its behavior and persistence depend on the SQL Server version and configuration. Treat it as an observable optimization, not a guarantee that the first execution, every parameter distribution, or a newly compiled plan will be well sized.

Avoid starting with blanket grant hints or a server-wide memory increase. First establish whether estimation, row width, ordering, or excessive overlap causes the pressure. A scheduling change that staggers several heavy exports may be more effective than forcing every query into a smaller reservation.

Finish with evidence from the same workload window: fewer waiting grants, acceptable spill behavior, stable throughput, and correct results. Retain the before and after plans and sample timestamps. That record explains whether the change reduced demand, improved estimates, or merely moved the waiting elsewhere.

Technical references: Microsoft Learn: Memory grant diagnostics · Microsoft Learn: sys.dm_exec_query_memory_grants · Microsoft Learn: Memory grant feedback.