RESOURCE_SEMAPHORE Wait Type in SQL Server

July 14, 2026

–

Part of the SQL Server Wait Types Library

Before SQL Server runs a query that sorts or hashes large data sets, it requests a memory reservation, called a memory grant, from the resource semaphore. The semaphore controls how much of the total server memory can be allocated for query workspace at once. If the request cannot be granted immediately (not enough free workspace memory), the query waits here.

RESOURCE_SEMAPHORE is one of the more consequential wait types. It does not mean a query is running slowly, it means queries cannot start running at all. They are parked in a queue waiting for other queries to finish and release their grants.

Is This Wait Expected?

No, not meaningfully. Some very brief waits during memory pressure spikes are possible, but RESOURCE_SEMAPHORE appearing consistently in your top waits is always a signal worth investigating.

Unlike CXPACKET or ASYNC_NETWORK_IO which are often acceptable, RESOURCE_SEMAPHORE almost always points to something actionable, usually a missing index causing unnecessarily large sort or hash operations, or inaccurate statistics causing SQL Server to misestimate how much memory a query needs.

Root Causes

Missing indexes causing large sort or hash operations, the most common cause. A query that lacks a covering index cannot seek to just the rows it needs. Instead it scans a large table, then sorts or hashes the results. That sort needs a big memory grant. With the right index, the query runs as a seek, needs no sort, and needs no memory grant at all.

Stale or inaccurate statistics causing over-estimation, if statistics are out of date, SQL Server might overestimate how many rows a query will process, request a larger memory grant than necessary, and hold a disproportionate share of workspace memory while running.

Stale statistics causing under-estimation and spills, the opposite problem. If SQL Server underestimates rows, it requests too small a grant. The query starts but runs out of workspace memory and spills to tempdb. Spilling queries often re-request larger grants on the next run, creating contention. Check IO_COMPLETION and total_spills alongside RESOURCE_SEMAPHORE.

Too many concurrent memory-intensive queries, even if individual queries are efficient, if many run simultaneously and each requests a large grant, the pool is exhausted. More a scheduling problem than a query problem.

Max server memory set too low, workspace memory is a percentage of max server memory. If max server memory is constrained (left at default or misconfigured), the workspace pool is smaller than it should be.

How To Diagnose It

See what is currently waiting and what has been granted:

SELECT
    session_id,
    request_time,
    grant_time,
    requested_memory_kb,
    granted_memory_kb,
    used_memory_kb,
    max_used_memory_kb,
    queue_id,
    wait_order,
    is_next_candidate,
    wait_time_ms,
    plan_handle
FROM sys.dm_exec_query_memory_grants
ORDER BY
    CASE WHEN queue_id IS NOT NULL THEN 0 ELSE 1 END,  -- pending first
    wait_time_ms DESC;

Get-WaitStatistics output in the dba-tools terminal and web UI

Sessions with a non-null queue_id are waiting. wait_order shows their position in the queue. requested_memory_kb shows how much they need to start.

Find queries that historically consume large memory grants:

SELECT TOP 20
    qs.total_grant_kb / qs.execution_count           AS avg_grant_kb,
    qs.max_grant_kb,
    qs.total_spills / qs.execution_count             AS avg_spills,
    qs.execution_count,
    SUBSTRING(qt.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
WHERE qs.total_grant_kb > 0
ORDER BY qs.total_grant_kb DESC;

High avg_grant_kb with avg_spills > 0 means SQL Server’s estimates are consistently wrong, under-grants leading to spills.

What To Do

Add missing indexes, this is the fix most of the time. A query that was scanning 5 million rows to sort the result becomes a seek returning 200 rows with no sort at all. The memory grant drops from hundreds of megabytes to nothing.

Update statistics, bad cardinality estimates produce both over-grants (hogging workspace memory) and under-grants (causing spills). Update statistics on the tables involved in the offending queries:

UPDATE STATISTICS [schema].[table_name] WITH FULLSCAN;

For persistent under-estimation on a rapidly changing table, consider increasing the automatic update statistics threshold (SQL Server 2016+ with compat level 130 has a dynamic threshold, enable it if not already on).

Review max server memory, if workspace memory is genuinely too small, increasing max server memory gives the workspace pool more room:

EXEC sp_configure 'max server memory (MB)', 32768;  -- example: 32 GB
RECONFIGURE;

Leave headroom for the OS and other processes, generally do not allocate more than 80–90% of physical RAM to SQL Server.

Use Resource Governor (Enterprise), to cap memory grants per workload group, preventing one class of queries from starving others:

ALTER RESOURCE POOL [reporting_pool] WITH (MAX_MEMORY_PERCENT = 30);

Query hints for specific known-bad queries:

-- Cap a specific query's grant to prevent it monopolising workspace memory
SELECT ... FROM ... WHERE ...
OPTION (MAX_GRANT_PERCENT = 10);

-- Force a minimum grant for a query that keeps getting under-granted and spilling
OPTION (MIN_GRANT_PERCENT = 5);

Use hints only as a temporary measure while you fix the underlying index or statistics problem.

Blog Categories

Comments

Leave a Reply Cancel reply