Memory Grants: Why a Query Waits for Memory

One query asks for enough workspace memory that several others wait behind it. Memory grants control sorts and hashes, so a RESOURCE_SEMAPHORE wait can point to a bad estimate before it points to a small server.

Understand Memory Grants

SQL Server reserves execution memory for operators such as sorts and hash joins. The optimizer estimates how much work those operators will do and requests a grant before execution. That grant is different from the buffer pool memory used to cache data pages. A query can have enough CPU and still wait because its execution memory request cannot be satisfied yet.

I start by asking whether the wait is widespread or tied to one statement. RESOURCE_SEMAPHORE identifies a grant wait, not the reason the grant became large. The useful next evidence is the query, its estimated row counts, requested memory, and actual maximum use. Adding RAM before reading those values can hide the real defect.

Find Current Requests and Waiters

sys.dm_exec_query_memory_grants shows queries waiting for a grant and queries that hold one. The request_time, grant_time, requested_memory_kb, granted_memory_kb, and max_used_memory_kb columns help separate queueing from over-allocation. A NULL grant_time identifies a current waiter. These values are a momentary snapshot and need repeated collection or workload history for an intermittent problem.

I capture the SQL text and plan handle only when permissions and workload impact allow. The following query is a small first look. It does not calculate an average for the whole day. Compare waiters with the sessions holding large grants, then inspect those statements.

SELECT session_id, request_time, grant_time,
       requested_memory_kb, granted_memory_kb,
       max_used_memory_kb, wait_time_ms
FROM sys.dm_exec_query_memory_grants
ORDER BY requested_memory_kb DESC;

Read Estimates Against Actual Work

A large estimated input can drive an oversized grant. Stale statistics, a predicate the optimizer cannot estimate well, or a join that multiplies rows can be responsible. In the actual execution plan, compare estimated and actual row counts near the memory-consuming operators. Look for a large mismatch before adjusting a server setting. Better statistics or a clearer predicate can reduce the request.

I ask which part of the plan inflated first. If a filter was applied too late, moving it into a suitable access path can change the size of later sorts. If a parameter produces very different row counts, one cached plan can serve some executions badly. Test candidate fixes with representative parameter values, not only the one that happened to be captured.

Do Not Ignore Underestimated Memory Grants

A grant can be too small as well as too large. When a sort or hash cannot stay in memory, it can spill work to tempdb. The query then runs longer and adds I/O, even if it did not wait at the semaphore. Execution plans and relevant events show spills. Increasing the grant globally is not a precise remedy for a poor cardinality estimate.

I compare requested memory, actual use, and spill warnings for the same statement. The right fix can be updated statistics, a changed index, or rewritten logic that reduces rows before the expensive operator. A memory grant hint is a targeted option only after the underlying estimate has been understood. Otherwise the hint can age badly as data changes.

Check Query Store and Concurrency

The impact of a grant depends on how many queries run together. A single large report during a quiet period behaves differently from several copies during business hours. Query Store can help identify plan changes and runtime patterns. Monitor the queue at the time users report slow work, then compare it with the known schedule of reports and maintenance tasks.

The query below shows active requests currently waiting on the memory resource semaphore. A missing row does not clear an earlier incident. Preserve a time window and the associated plans for a recurring complaint.

SELECT session_id, status, wait_type,
       total_elapsed_time, cpu_time
FROM sys.dm_exec_requests
WHERE wait_type = N'RESOURCE_SEMAPHORE'
ORDER BY total_elapsed_time DESC;

Use Memory Grant Feedback With Context

Recent SQL Server versions can adjust memory grants after executions for eligible plans. That feedback can help repeated statements whose first estimate was poor, but it does not replace sound statistics and query design. Plans can change, workloads can alternate between small and large parameters, and feedback has boundaries. Verify what happened on the actual instance.

I do not disable or force a database-wide feature because one query surprised me. First capture the plan, row estimates, used memory, and wait behavior. Then test the narrowest change. A system that stops waiting only because every other query was delayed is not improved.

Fix the Estimate Behind Memory Grants Before the Hardware

Work from evidence to action. Update relevant statistics when they are stale, correct non-sargable predicates when possible, and remove unnecessary sorts or wide intermediate rows. Add a useful index only after checking write cost. Retest with representative concurrent work and record the change in grant size and user-visible duration on your own server.

Which query consumed the memory, and which queries waited behind it? Answer both. If the workload is genuinely larger than the available resource after efficient plans are in place, capacity planning becomes the right conversation. The order matters. A better estimate can free more usable concurrency than a blind hardware change.

Memory grants need context from the plan and workload. A large requested grant can be reasonable for a big sort, while a small grant can still spill if the estimate is wrong. Look at actual versus estimated rows, sort and hash operators, and the wait history before changing a server-wide memory setting. I check whether statistics, parameter choices or a predicate shape caused the estimate to drift. A bigger memory cap does not repair a query that believes ten rows will arrive when ten million do.

Test one change at a time against representative parameter values. If an index removes a sort or a query rewrite reduces rows before a hash operation, the grant can shrink without any global setting change. Also watch concurrent queries: one grant that looks harmless alone can block a queue during peak work. The goal is not the smallest grant on a screenshot; it is predictable throughput and an acceptable result for the real workload.

Related reading on this blog: Queries Waiting for Memory Grant: Performance Tuning and SQL SERVER 2022: Persistence and Percentile Memory Grant Feedback.

A memory grant wait is not a request to buy RAM, it is a request to inspect the plan that asked for it.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.