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

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

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.

Related guides

• How Microsoft SQL Server actually works in production: a mental model for operators
• Microsoft SQL Server monitoring checklist: the signals every production instance needs
• Microsoft SQL Server monitoring maturity model: from survival to expert
• SQL Server runnable tasks backlog: the in-engine CPU queue OS metrics miss
• SQL Server CPU utilization high: telling query load apart from a bad plan
• SQL Server CXPACKET and CXCONSUMER waits: parallelism, MAXDOP, and what is actually wrong
• SQL Server high compilations per second: plan cache pollution and CPU burn