Troubleshoot RESOURCE_SEMAPHORE Waits in SQL Server (Memory Grant Pressure)

July 20, 2026

–

RESOURCE_SEMAPHORE means a query is waiting for a workspace memory grant, memory SQL Server reserves up front for sorts, hashes, and other operators before the query is allowed to run at all, and there isn’t enough available right now. Unlike most waits, this one can genuinely hang a session for minutes with nothing else visibly wrong: no blocking, no locks held, just a query sitting there waiting for memory that other running queries currently have reserved. If the wait outlasts the grant timeout, the query fails with error 8645.

This post covers both how to diagnose it and a real incident: this exact wait, reproduced twice independently, on the actual SQL Server instance behind this site’s scripts, where the root cause turned out to be the host machine, not SQL Server configuration.

STEP1

Step 1: Confirm It’s Actually RESOURCE_SEMAPHORE

Check current waits and what’s actually queued for memory:

SELECT session_id, wait_type, wait_time AS wait_time_ms, wait_resource
FROM sys.dm_exec_requests
WHERE wait_type = 'RESOURCE_SEMAPHORE';

SELECT session_id, requested_memory_kb, granted_memory_kb, wait_order, is_next_candidate
FROM sys.dm_exec_query_memory_grants
ORDER BY wait_order;

A session with a row in dm_exec_query_memory_grants but granted_memory_kb IS NULL is actively queued waiting for its grant. wait_order tells you its position in the queue; is_next_candidate tells you which one memory will go to next as it frees up.

Note the variant: RESOURCE_SEMAPHORE_QUERY_COMPILE is a different wait, queuing for compile-time memory, not execution memory. Same family, different bottleneck, different fix (it’s usually plan cache/compilation pressure, not workload memory pressure).

STEP2

Step 2: Check Instance-Level Memory Configuration

SELECT name, value_in_use FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'min server memory (MB)');

SELECT total_physical_memory_kb / 1024 AS total_physical_mb,
       available_physical_memory_kb / 1024 AS available_physical_mb,
       system_memory_state_desc
FROM sys.dm_os_sys_memory;

system_memory_state_desc is the line to actually read. 'Available physical memory is high' means the OS has plenty of headroom. 'Available physical memory is low' or '...running low' means SQL Server is genuinely constrained at the OS level, not just its own max server memory setting.

Real Incident: The Host, Not the Config, Was the Problem

On the lab instance behind this site, RESOURCE_SEMAPHORE waits and hangs were documented repeatedly across unrelated scripts: Get-OpenTransactions.sql reproduced the wait twice independently against the same workload, and Get-QueryStoreRegressions.sql (a heavy multi-CTE Query Store comparison) hung roughly 8 minutes on RESOURCE_SEMAPHORE against a real database. Both looked, from inside SQL Server, like a memory-grant configuration problem.

The actual root cause, found by checking sys.dm_os_sys_memory rather than only SQL Server’s own settings: the instance runs on an 8GB working machine, not a dedicated server, with Docker Desktop, a WSL2 VM, and an IDE running alongside SQL Server. available_physical_memory_kb showed under 200MB free and system_memory_state_desc read 'Available physical memory is running low', while max server memory was already set to a conservative 1800MB, that setting wasn’t the bug, the host simply had no memory to spare regardless of what SQL Server itself was configured to use. Under that pressure, a maintenance job doing an index rebuild failed mid-operation with a genuinely alarming multi-error cascade (802, 9001, 3314, 845, 3908, the kind of errors that read like corruption), and SQL Server’s own crash recovery brought the database back online cleanly afterward, sys.databases.state_desc confirmed ONLINE immediately after, no actual corruption.

The lesson generalizes past this one box: when max server memory is already reasonable for the workload and RESOURCE_SEMAPHORE still shows up, check the OS’s own free memory before assuming it’s a SQL Server-side problem to tune. sys.dm_os_sys_memory answers that question directly and takes one query to check.

STEP3

Step 3: If Memory Genuinely Is Available, Check the Grants Themselves

When the host has memory to spare and RESOURCE_SEMAPHORE still happens, the more common cause is a query requesting far more memory than it actually needs, usually from a bad cardinality estimate:

SELECT session_id, requested_memory_kb, granted_memory_kb,
       ideal_memory_kb, used_memory_kb
FROM sys.dm_exec_query_memory_grants;

A large gap between granted_memory_kb and used_memory_kb means the optimizer over-estimated how much memory the query would need, usually because of stale statistics or a cardinality estimate that’s badly wrong for the actual data. That over-large grant then starves other queries competing for the same pool, even though the requesting query didn’t need most of what it reserved. Get Memory Grant Spills does the same comparison across the plan cache rather than for the sessions running now, granted against used per query, with the spills that an under-grant causes.

Common Causes and Fixes

Cause	Fix
Host-level memory pressure	The incident above. Free memory on the host, take `max server memory` down further if the box is shared with other real workloads, or move to one with headroom. This is not a SQL-Server-config-only problem and tuning the instance will not fix it.
Stale statistics inflating the grant	Update statistics on the tables the queuing query touches. A bad row-count estimate is what drives an oversized grant request in the first place, so the grant is a symptom of the estimate.
Many memory-hungry queries at once	Large sorts, hash joins and wide `ORDER BY`s all competing for the same pool. Resource Governor can cap `MAX_MEMORY_PERCENT` per workload group so one workload cannot starve another; Get Trace Flags and Resource Governor Configuration shows whether it is configured at all.
One query too expensive for the pool	Sometimes the honest fix is the query, not the memory: a supporting index so the optimiser never needs the large sort or hash operator, rather than finding more memory to hand it.

Best Practices

Check sys.dm_os_sys_memory before assuming a memory-grant wait is a SQL Server configuration problem to tune; on a shared or resource-constrained host, it usually isn’t.
Don’t retry a hung query blindly when it’s showing RESOURCE_SEMAPHORE; check whether memory is actually available first, retrying into the same pressure just repeats the wait.
Update statistics on tables behind consistently memory-hungry queries; a bad estimate is a common, fixable root cause of an oversized grant request.
If a maintenance job fails under memory pressure with a scary-looking multi-error cascade, check sys.databases.state_desc before assuming corruption, SQL Server’s own crash recovery often resolves it cleanly.