Troubleshoot RESOURCE_SEMAPHORE Waits in SQL Server (Memory Grant Pressure)

Peter Whyte

–

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.

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.

Step 1: Confirm It’s Actually RESOURCE_SEMAPHORE

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

SELECT session_id, wait_type, wait_duration_ms, resource_description
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).

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 this project’s own lab instance (PWSQL01), 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.

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.

Common Causes and Fixes

  • Host-level memory pressure (the incident above). Fix by freeing host memory, adjusting max server memory down further if the host is shared with other real workloads, or moving to a box with adequate headroom. Not a SQL-Server-config-only problem.
  • Stale statistics causing over-large memory grants. Update statistics on the tables involved in the queuing query; a bad row-count estimate is what drives an oversized grant request in the first place.
  • Many concurrent memory-hungry queries (large sorts, hash joins, wide ORDER BYs) all competing for the same grant pool. Resource Governor can cap MAX_MEMORY_PERCENT per workload group to prevent one workload from starving another, worth checking with Get Trace Flags and Resource Governor Configuration if Resource Governor is even configured on the instance.
  • A single query that’s simply too expensive for the available memory pool. Sometimes the real fix is query tuning, adding a supporting index so the optimizer doesn’t need a large sort/hash operator at all, rather than finding more memory to give 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.

Related Scripts

Blog Categories

Recent Posts

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *