RESOURCE_SEMAPHORE Wait Type Explained

SQL Server's memory manager maintains a resource semaphore that controls query memory grants through the query memory governor. When a query requires memory for operations like hash joins, sorts, or bitmap filters, it requests a memory grant during compilation. The memory governor calculates available memory by subtracting committed grants from the query memory pool, which defaults to 75% of available memory above min server memory.

The semaphore uses two primary controls: small query thresholds (default 1024KB) and large query thresholds. Queries exceeding the small query threshold compete for a separate large-query pool; the split between small and large query pools is nuanced and version-dependent, so rather than relying on a fixed percentage, treat the large-query pool as a bounded subset of the overall query memory pool. The governor blocks new grants when granting would exceed these limits or when the concurrency limit for large grants is reached.

Resource Governor was introduced in SQL Server 2008 Enterprise Edition, allowing workload-specific memory limits. SQL Server 2016 added batch mode memory grant feedback, reducing oversized grants in subsequent executions. SQL Server 2019 expanded this to row mode operations and introduced memory grant percentile-based feedback. SQL Server 2022 added degree-of-parallelism feedback and further memory grant refinements.

The wait manifests when queries enter the memory grant queue. Queries timing out after the default wait period generate error 8645. The default 'query wait' is calculated as 25 times the estimated query cost (in seconds), not a flat 25 seconds, and can be overridden via the 'query wait' sp_configure option. The semaphore uses FIFO ordering, but Resource Governor can prioritize workloads.

-- Current memory grants and waiting requests
SELECT 
    r.session_id,
    r.request_id, 
    r.granted_query_memory * 8 AS granted_memory_kb,
    r.requested_memory_kb,
    r.ideal_memory_kb,
    r.query_cost,
    r.timeout_sec,
    r.resource_semaphore_id,
    t.text AS query_text
FROM sys.dm_exec_query_memory_grants r
LEFT JOIN sys.dm_exec_sql_text(r.sql_handle) t ON 1=1
ORDER BY r.requested_memory_kb DESC;

-- Memory grant wait statistics and queue depth
SELECT 
    pool_id,
    name,
    max_memory_kb,
    used_memory_kb, 
    target_memory_kb,
    outstanding_requests_count,
    max_outstanding_requests_count
FROM sys.dm_resource_governor_resource_pools
WHERE pool_id >= 2 OR name = 'default';

-- Historical memory grant timeouts and patterns
SELECT 
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    max_wait_time_ms,
    signal_wait_time_ms,
    wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_wait_ms
FROM sys.dm_os_wait_stats 
WHERE wait_type = 'RESOURCE_SEMAPHORE'
AND waiting_tasks_count > 0;

-- Query plans with expensive memory-consuming operations
SELECT TOP 20
    qs.sql_handle,
    qs.plan_handle,
    qs.max_grant_kb,
    qs.min_grant_kb, 
    qs.execution_count,
    qs.total_elapsed_time / qs.execution_count AS avg_duration_ms,
    st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
WHERE qs.max_grant_kb > 100000  -- Queries with large memory grants
ORDER BY qs.max_grant_kb DESC;

-- Current blocking and memory grant dependencies
SELECT 
    s.session_id,
    s.login_name,
    s.host_name,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    mg.requested_memory_kb,
    mg.granted_query_memory * 8 AS granted_kb
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id  
LEFT JOIN sys.dm_exec_query_memory_grants mg ON r.session_id = mg.session_id
WHERE r.wait_type = 'RESOURCE_SEMAPHORE'
OR mg.session_id IS NOT NULL;

Increase query timeout for long-running operations

-- Increase query timeout to prevent premature failures
-- Test thoroughly before implementing in production
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = ON;

-- For specific connections, set at application level:
-- SET LOCK_TIMEOUT 60000;  -- 60 seconds
-- Or modify connection string: Command Timeout=300

Caveats: Does not solve underlying memory pressure, only prevents timeout errors. Test application tolerance for longer waits.

Reduce memory grant sizes through plan optimization

-- Force recompilation to get updated statistics and potentially smaller grants
-- Run during maintenance window due to temporary performance impact
EXEC sp_recompile 'YourLargeTable';

-- Clear plan cache for specific problematic queries (use actual plan_handle)
-- DBCC FREEPROCCACHE (0x06000500204D640040A57C01F79A9600F8290000000000000000000000000000000000000000000000000000);

-- Update statistics to improve cardinality estimates
UPDATE STATISTICS YourLargeTable WITH FULLSCAN;

Caveats: Recompilation causes temporary CPU spike and plan cache churn. Schedule during low activity periods.

Configure Resource Governor memory limits

-- Create workload group with memory limits for heavy queries
-- Requires Enterprise Edition or SQL Server 2022 Standard+
CREATE WORKLOAD GROUP high_memory_queries
WITH (
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 10,  -- Max 10% per query
    REQUEST_MAX_CPU_TIME_SEC = 300,         -- 5 minute timeout
    REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 120, -- 2 minute memory wait
    MAX_DOP = 4                             -- Limit parallelism
)
USING [default];

-- Apply to classifier function targeting specific applications
ALTER RESOURCE GOVERNOR RECONFIGURE;

Caveats: Requires careful workload classification. Test classifier function logic thoroughly. Monitor for query failures.

Emergency memory pressure relief

-- Clear plan cache to free memory (emergency use only)
-- WARNING: Causes temporary performance degradation
CHECKPOINT; -- Force clean buffers first
DBCC DROPCLEANBUFFERS; -- Remove unused pages
DBCC FREEPROCCACHE; -- Clear plan cache

-- Check immediate impact
SELECT 
    (physical_memory_kb / 1024) AS physical_memory_mb,
    (committed_kb / 1024) AS committed_mb,
    (committed_target_kb / 1024) AS target_mb
FROM sys.dm_os_sys_info;

Caveats: Nuclear option causing immediate performance impact. Use only during critical memory exhaustion. All queries will recompile.

Configure max server memory to leave 15-20% for OS and other processes, never using default unlimited setting. Set min server memory to 512MB minimum to prevent memory starvation during startup. Monitor memory grant patterns through extended events or Query Store to identify queries with oversized grants.

Implement query plan optimization focusing on accurate cardinality estimates through updated statistics and appropriate indexing. Large hash joins and sorts drive excessive memory grants, address through covering indexes and query rewrites. Enable memory grant feedback in SQL Server 2017+ through database scoped configuration (BATCH_MODE_MEMORY_GRANT_FEEDBACK and ROW_MODE_MEMORY_GRANT_FEEDBACK).

Configure Resource Governor workload groups with REQUEST_MAX_MEMORY_GRANT_PERCENT limits for analytical workloads. Set REQUEST_MEMORY_GRANT_TIMEOUT_SEC to reasonable values preventing indefinite waits. Use MAX_DOP limits to reduce parallel query memory multiplication.

Monitor sys.dm_exec_query_memory_grants during peak periods identifying queries with requested_memory_kb exceeding 100MB. Establish alerts on RESOURCE_SEMAPHORE wait statistics when average wait times exceed 10 seconds or waiting task counts exceed 20. Track memory grant timeouts through error log monitoring for error 8645 patterns.

Consider columnstore indexes for analytical workloads reducing memory grant requirements through batch mode processing. Evaluate query patterns for opportunities to break large operations into smaller chunks. Schedule heavy reporting during off-peak hours when memory contention is minimal.