RESOURCE_SEMAPHORE Wait Stats: Memory Grant Waits

RESOURCE_SEMAPHORE wait stats mean a query is waiting for memory before it can even start. Queries that sort or hash need workspace memory, called a memory grant. When it isn’t free, they wait in line until enough is released. Oversized grants, too many big queries at once or too little memory can all build that line.

This post is part of my wait stats series, told as one story at the Clipboard Diner. Every post is listed in the series guide.

Night 25 at the Clipboard Diner

The wristbands were working. Nobody missed the old Reserved cards, and the milkshake party in booth 7 had finally paid. Then, on Night 25, a wedding came up the highway.

The Clipboard Diner had one firm kitchen rule. No plating starts until the counter space for the whole order is set aside. Otherwise two big orders could each grab half the counter, and neither could finish. So at 6 PM, Dee read the wedding’s RSVP card. It said 300 people. Dee cleared the whole prep counter and lined it with 300 plates.

At 6:40 the wedding party walked in. There were 120 of them. Casey counted twice. The plates for the other 180 sat on the counter, clean and empty, holding space nobody would use.

At 7:10 two birthday parties came in, a table of six and a table of four. Their tickets needed a small corner of the counter for a cake and some grilled cheese. There wasn’t one. Kit held both tickets at the edge of the counter and waited. The burners were free. The cooks were free. Only the counter was taken.

The birthday cakes went out forty minutes late. The wedding sent a slice of its own cake to each table, which helped. Casey picked up the clipboard and wrote: Reserved for 300. Fed 120. Two parties waited.

What RESOURCE_SEMAPHORE Means

That’s what SQL Server does when a query waits for a memory grant. Before a query with a sort or a hash join starts, it asks for workspace memory. It can’t start until that memory is granted.

The size of the request comes from the plan’s estimates. SQL Server guesses how many rows will reach the sort and how wide they are. Then it reserves memory to match. The queue in front of that memory is the resource semaphore. A query standing in it waits with RESOURCE_SEMAPHORE, and it doesn’t use any CPU while it waits.

Each Resource Governor pool has two of these queues. One handles regular queries, and a small one handles tiny, cheap queries. By default, the workload group setting REQUEST_MAX_MEMORY_GRANT_PERCENT limits one query to 25 percent of the pool’s query memory. In the default group, a query still gets its minimum required memory, even above that limit. If a query waits too long, it gives up with error 8645.

Why Grants Get Too Big

The wedding said 300 and brought 120. That’s an overestimate, and it’s the most common story I find. Bad statistics, tricky predicates or a table variable can all throw the row estimate off. A grant sized for ten million rows stays reserved even when one million arrive.

Wide columns are the second cause. The grant is sized from the declared width of the columns, not from the data inside them. A column declared as nvarchar(4000) that holds short names still costs a lot of memory in a sort. I wrote about this in oversized varchar columns and inflated memory grants.

The third cause is plain size. A report that sorts and hashes huge row sets needs a big grant, even with perfect estimates. Grants that are too small hurt too. They spill to tempdb, which the IO_COMPLETION Wait Stats post covers.

Normal or a Problem?

See It on Your Server

The first query lists every query that holds a grant or waits for one. Run it while the waits are happening.

-- The wedding test: who waits in line, and who reserved more than it uses?
SELECT mg.session_id,
       CASE WHEN mg.grant_time IS NULL THEN 'waiting in line' ELSE 'holding a grant' END AS grant_state,
       mg.wait_time_ms,
       mg.requested_memory_kb AS asked_kb,
       mg.granted_memory_kb AS granted_kb,
       mg.granted_memory_kb - mg.max_used_memory_kb AS unused_so_far_kb,
       mg.dop,
       t.text AS query_text
FROM sys.dm_exec_query_memory_grants AS mg
OUTER APPLY sys.dm_exec_sql_text(mg.sql_handle) AS t
ORDER BY CASE WHEN mg.grant_time IS NULL THEN 0 ELSE 1 END,
         mg.granted_memory_kb - mg.max_used_memory_kb DESC,
         mg.wait_time_ms DESC;

Rows marked waiting in line are the birthday parties, and they sort to the top. Below them, the holder with the biggest unused_so_far_kb is your wedding candidate. That number is granted memory the query hasn’t touched yet. A running query can still need it later, so confirm with the granted and used memory in the actual plan.

The second query shows the queues themselves. There’s one row for each semaphore in each Resource Governor pool.

-- Is the counter full? One row per grant queue in each pool
SELECT pool_id,
       CASE resource_semaphore_id WHEN 0 THEN 'regular' ELSE 'small query' END AS queue,
       total_memory_kb,
       available_memory_kb,
       grantee_count AS holding,
       waiter_count AS waiting,
       timeout_error_count AS gave_up
FROM sys.dm_exec_query_resource_semaphores
ORDER BY waiting DESC, pool_id;

Watch waiting and available_memory_kb together. Waiters with little memory available means the counter is full. The gave_up count runs since the last restart, so watch whether it rises between two runs. The Memory Grants Pending counter tells the same story, as I explain in what is Memory Grants Pending.

Fix It

You could say memory is cheap, so add more. Fair point, and sometimes the server needs it. More memory relieves real pressure, but it can also hide oversized grants. That’s why I check the estimates first, then decide.

1. Find the biggest grants with the first query, and save their query text.
2. Update statistics on the tables behind those queries, then compare estimated and actual rows in the plan.
3. Return only the columns the query needs, and right-size wide varchar and nvarchar columns.
4. Add an index that delivers rows already sorted, so the big sort disappears.
5. Let memory grant feedback work. In SQL Server 2022 and 2025 it’s an Enterprise feature, so Standard and Express don’t get it. Batch mode needs compatibility level 140 or higher, and row mode needs 150 or higher. The SQL Server 2022 percentile and persistence features also need Query Store in READ_WRITE mode.
6. For one known bad report, test a cap with the MAX_GRANT_PERCENT query hint. A smaller grant can spill to tempdb and run longer, so compare both runs before you keep it.
7. Add memory last, after the grants make sense.

A Cousin: RESOURCE_SEMAPHORE_QUERY_COMPILE

This wait sounds the same but happens earlier. It means a query is waiting for memory to compile, not to run. SQL Server limits how many large compiles run at once, so a storm of new plans forms a line. The usual cause is lots of ad hoc SQL that never reuses a plan. My note on RESOURCE_SEMAPHORE_QUERY_COMPILE has more.

New in SQL Server 2022 and 2025

Memory grant feedback started with batch mode, and SQL Server 2019 added it for row mode. It shrinks a grant that was too big, or grows one that spilled, the next time the query runs. SQL Server 2022 added percentile mode, which looks at several past runs instead of only the last one. It also added persistence: the feedback lives in Query Store and survives a plan leaving the cache. Read more in persistence and percentile memory grant feedback.

SQL Server 2025 helps the compile cousin. The database scoped setting OPTIMIZED_SP_EXECUTESQL is off by default. When it’s on, the first sp_executesql call compiles the plan, and other sessions wait and reuse it. Stored procedures already compile that way. It calms storms where many sessions compile the same statement at once. It won’t help ad hoc SQL that never reuses a plan.

Related Reading

• Memory Grants: Why a Query Waits for Memory
• Queries Waiting for Memory Grant: Performance Tuning
• Introduction to Memory Grant Feedback

A memory grant is not a gift, it is a reservation someone else is waiting behind.

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