SQL Server RESOURCE_SEMAPHORE waits: queries stuck waiting for a memory grant

RESOURCE_SEMAPHORE is the wait type SQL Server records when a worker thread cannot get a query memory grant. Before a query runs a sort, hash, or certain joins, the optimizer estimates how much workspace memory it needs and asks the grant pool for it. When the pool is exhausted, parsed-and-optimized queries sit in a queue. To the application they look hung.

CPU may be low. Disk I/O may be low. Buffer pool may look fine. Users report “the database is slow” and standard dashboards do not explain why. RESOURCE_SEMAPHORE only shows up clearly if you sample wait stats on a short interval and watch the Memory Grants Pending counter. Both are routinely missed.

Treat the condition as urgent when the wait dominates, the grant queue is at least 5 deep, the oldest wait is at least 300 seconds, and the queue is still growing. Brief blips during index rebuilds or ETL are expected, not incidents.

What this means

A query memory grant is workspace memory, separate from the buffer pool. SQL Server reserves it up front, before execution, so a sort or hash has somewhere to put intermediate results. The optimizer estimates how much to ask for using cardinality estimation. If the estimate is wrong, two failure modes follow.

First failure mode: the optimizer asks for more than the pool has. The query waits on RESOURCE_SEMAPHORE. If a slot frees up before the query wait timeout (default 25 times the estimated query cost, in seconds), the query runs. If it does not, SQL Server raises error 8645 (“A timeout occurred while waiting for memory resources to execute the query”) and the query dies. Applications see timeouts.

Second failure mode: the optimizer asks for less than the operation actually needs. The query gets its grant, runs, and discovers mid-execution that it does not have enough workspace memory. It spills the sort or hash to TempDB. The wait type shifts from RESOURCE_SEMAPHORE to whatever TempDB I/O produces, usually IO_COMPLETION or PAGEIOLATCH_* on database ID 2. Same root cause, very different signals.

flowchart TD
    A[Query parsed and optimized] --> B{Grant requested}
    B -->|Granted| C[Query executes]
    B -->|Pool exhausted| D[Wait on RESOURCE_SEMAPHORE]
    D -->|Slot frees in time| C
    D -->|Wait exceeds 25x cost| E[Error 8645: query killed]
    C -->|Grant underestimated| F[Sort/hash spills to TempDB]
    F --> G[Waits shift to IO_COMPLETION, PAGEIOLATCH_]

The pool size depends on max server memory, total physical memory, and on Resource Governor if it is enabled. One pathological query asking for a multi-GB grant can drain it. Several concurrent smaller grants can drain it. A Resource Governor cap set too low can drain it even when the host has plenty of free RAM.

Common causes

CauseWhat it looks likeFirst thing to check
Single oversized grant hoarding the poolsys.dm_exec_query_memory_grants shows one session with granted memory many times larger than the rest; RESOURCE_SEMAPHORE wait count rising with only a few waitersTop grant holder by requested_memory_kb
Too many concurrent memory-hungry queriesMany granted queries with similar sizes, no obvious outlier; correlates with concurrent reporting or ETL windowsActive grant count vs queue depth
Cardinality estimation errorA plan shows a large sort/hash with overestimated row counts; spills to TempDB if granted, waits if pool is dryCompare estimated vs actual rows in actual execution plan
Resource Governor cap too restrictivePlenty of free physical RAM but grant pool target is small; target_memory_kb well below what the host could givesys.dm_exec_query_resource_semaphores target vs free RAM
Parameter sniffing regressionA stored procedure’s duration jumps after plan recompile; same query text, different plan; may have a new large sort or hashQuery Store plan history

Quick checks

All queries below require VIEW SERVER STATE permission (sysadmin has it by default). They are read-only and safe to run during an incident.

-- Check current Memory Grants Pending counter (point-in-time)
SELECT cntr_value AS memory_grants_pending
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Memory Grants Pending'
  AND object_name LIKE '%Memory Manager%';

-- Detail on every active grant and every waiter.
-- grant_time IS NULL means the query is still waiting for a slot.
SELECT
    session_id,
    request_time,
    grant_time,
    requested_memory_kb,
    granted_memory_kb,
    required_memory_kb,
    used_memory_kb,
    wait_time_ms,
    dop,
    query_cost
FROM sys.dm_exec_query_memory_grants
ORDER BY wait_time_ms DESC;

-- Resource semaphore view: target vs granted.
-- resource_semaphore_id 0 = regular queries, 1 = small (<5 MB) queries
SELECT
    resource_semaphore_id,
    target_memory_kb,
    max_target_memory_kb,
    total_memory_kb,
    available_memory_kb,
    waiter_count,
    timeout_error_count,
    forced_grant_count
FROM sys.dm_exec_query_resource_semaphores;

-- OS-level memory state
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;

-- Confirm RESOURCE_SEMAPHORE is a top wait (excludes idle waits)
SELECT TOP 15
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,
    CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(5,2)) AS pct
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK', 'BROKER_TO_FLUSH', 'BROKER_TASK_STOP',
    'CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 'LAZYWRITER_SLEEP',
    'SQLTRACE_BUFFER_FLUSH', 'WAITFOR', 'XE_TIMER_EVENT',
    'XE_DISPATCHER_WAIT', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
    'BROKER_EVENTHANDLER', 'SP_SERVER_DIAGNOSTICS_SLEEP',
    'HADR_FILESTREAM_IOMGR_IOCOMPLETION', 'DIRTY_PAGE_POLL',
    'DISPATCHER_QUEUE_SEMAPHORE', 'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
    'QDS_ASYNC_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH',
    'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE', 'HADR_WORK_QUEUE',
    'BROKER_TRANSMITTER', 'KSOURCE_WAKEUP'
)
AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

-- Recent error 8645 (queries killed waiting for memory)
EXEC sp_readerrorlog 0, 1, '8645';

Cumulative DMVs reset on instance restart. Snapshot them externally if you need trend analysis.

How to diagnose it

  1. Confirm Memory Grants Pending is sustained above zero. A single sample of 1 or 2 is a ticket-grade investigation, not a page. Page only when queue depth is at least 5 and the oldest wait is at least 300 seconds.

  2. Open sys.dm_exec_query_memory_grants. Sort by wait_time_ms DESC. The waiter at the top is the oldest victim. Note the session_id and pull the SQL text using CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) on the same DMV, or DBCC INPUTBUFFER(<session_id>).

  3. Find the hoarder, if there is one. Sort the same DMV by granted_memory_kb DESC. A single grant 5 to 10 times larger than the rest is the prime suspect. Pull its plan via CROSS APPLY sys.dm_exec_query_plan(mg.plan_handle) and look for a fat sort or hash.

  4. Compare the hoarder’s requested_memory_kb against target_memory_kb in sys.dm_exec_query_resource_semaphores. If one query’s request is a meaningful fraction of the pool target, that is the bug.

  5. Check the OS layer via sys.dm_os_sys_memory. If available_physical_memory_kb is high, the OS is not the constraint; the grant pool is being capped internally by max server memory or Resource Governor. If OS memory is low, the problem is upstream (max server memory set too high, a co-tenant, or a leak) and RESOURCE_SEMAPHORE is a symptom.

  6. If Resource Governor is enabled, check the workload group’s REQUEST_MAX_MEMORY_GRANT_PERCENT (default 25) and the resource pool size. A common operator trap is setting the cap so low that the semaphore target shrinks and queries wait even when the host has free RAM.

  7. Check Query Store (SQL Server 2016+) for the offending query. Look for plan changes that coincide with the regression. A new plan with a large sort that did not exist before is a parameter sniffing regression.

  8. Check the error log for 8645 occurrences. Each one is a query that was killed waiting for memory.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
Memory Grants Pending (%Memory Manager%)Direct count of queries queuedSustained above zero in OLTP. 5 or more is severe
RESOURCE_SEMAPHORE wait time, delta sampled every 30 to 60sAggregated time spent waiting for grantsBecoming a top 5 wait type
sys.dm_exec_query_memory_grants waiter_countLive queue depthSustained above zero
sys.dm_exec_query_resource_semaphores target vs availablePool size and how much is freeavailable_memory_kb near zero
Error 8645 in error logQueries timing out for memoryAny occurrence
TempDB internal_object_reserved_page_countSort/hash spill indicator (under-grant case)Growing with no obvious user transaction
PLE and buffer cache hit ratioDifferentiates buffer pool pressure from grant pressurePLE dropping suggests a separate spiral
Batch requests/sec vs transactions/secWhether work is actually completingHigh connections, low transactions means stuck

Fixes

Single oversized grant (most common)

If sys.dm_exec_query_memory_grants shows one query holding the majority of the pool:

  • Short-term: kill the session after assessment. Warning: KILL triggers rollback. Duration depends on the open transaction size. The queue clears once the grant is released.
  • Short-term: cap future runs with OPTION (MAX_GRANT_PERCENT = X) (SQL Server 2012 SP3+). The query proceeds with a smaller grant and may spill to TempDB, but it no longer starves the pool.
  • Root cause: inspect the plan. A huge sort or hash usually means a missing index forcing a scan, stale statistics, or a parameter sniffing regression. Fix the plan, not the symptom.

Too many concurrent memory-hungry queries

  • Throttle concurrency at the application layer. Reporting and ETL jobs often run unthrottled and pile up.
  • Use Resource Governor workload groups to cap per-query memory and to isolate reporting from OLTP. Resource Governor is now available in Standard edition starting with SQL Server 2025.
  • Stagger maintenance windows so index rebuilds and ETL do not overlap.

Resource Governor cap too restrictive

If sys.dm_exec_query_resource_semaphores shows target_memory_kb far below available physical memory:

  • Check the resource pool’s max_memory_kb and the workload group’s REQUEST_MAX_MEMORY_GRANT_PERCENT.
  • Raise the cap and re-test. Watch for new spills to TempDB. The cap may have been masking a query that needs a bigger grant than it should.
  • The semaphore target is independent of host free memory. This trap shows RESOURCE_SEMAPHORE waits even when free RAM is plentiful.

Underestimated grant causing spills

  • Same root cause as oversized grant: cardinality estimation error.
  • Check whether memory grant feedback is applicable. SQL Server 2017 (compatibility level 140) introduced batch mode feedback. SQL Server 2019 (compatibility level 150) added row mode feedback. SQL Server 2022 (compatibility level 160) added persistence and percentile feedback.
  • For SQL Server 2022+, verify Query Store is enabled and in read-write mode. Persistence and percentile grant feedback have no effect without Query Store.
  • For older versions or for parameter-sensitive queries where feedback disables itself (observable via the memory_grant_feedback_loop_disabled extended event ), use MIN_GRANT_PERCENT or fix the underlying cardinality problem.

Prevention

  • Sample wait stats every 30 to 60 seconds and compute deltas. Cumulative sys.dm_os_wait_stats since startup is useless for diagnosing current conditions.
  • Alert on Memory Grants Pending sustained above zero. Alert harder at 5 or more.
  • Track the top query memory grants as a time series. The same query with the same plan should request a stable grant. A sudden change means a plan regression.
  • Enable Query Store on every production database. It is on by default for new databases in SQL Server 2022, but only for new databases. Older databases need explicit enablement.
  • Test compatibility level upgrades in staging before raising them in production. Memory grant feedback behavior changes between levels.
  • If you use Resource Governor, document why each REQUEST_MAX_MEMORY_GRANT_PERCENT cap is set to its value. The default of 25 is rarely wrong.
  • Pre-size TempDB for spills. A query that spills because its grant was capped is still better than a query that waits forever for memory.

How Netdata helps

  • Per-second collection of Memory Grants Pending from sys.dm_os_performance_counters catches the queue forming before users report slowness.
  • Wait statistics sampled on short intervals expose RESOURCE_SEMAPHORE climbing into the top waits without the cumulative-since-startup noise.
  • Correlating grants pending with RESOURCE_SEMAPHORE wait time, TempDB internal object usage, and batch requests per second distinguishes the three failure modes (oversized grant, concurrent load, under-grant spills) from each other in one view.
  • ML anomaly detection on the per-second grant signal surfaces deviations from baseline during quiet hours, before they become incidents.
  • Composite alerting combines queue depth, oldest waiter age, and wait-time trend, so you only page on the pathological case described above, not on every transient spike during index maintenance.

Netdata’s Microsoft SQL Server monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.