RESOURCE_SEMAPHORE_MUTEX Wait Type Explained

RESOURCE_SEMAPHORE_MUTEX waits originate from contention on the internal resource semaphore mutex that protects the query memory grant structures. When SQL Server receives a query requiring memory for operations like sorts, hashes, or bulk operations, it must acquire this mutex to evaluate available memory and potentially queue the request.

The resource semaphore maintains two primary queues: a small query queue for queries requesting less than 5MB of memory, and a large query queue for queries exceeding this threshold. The mutex serializes access to these queue structures and the associated memory grant calculations. Contention occurs when multiple concurrent queries simultaneously request memory grants, forcing them to wait for mutex acquisition.

SQL Server 2016 introduced the query memory grant feedback feature, which can influence this wait type by causing queries to renegotiate their memory requirements based on previous execution history. SQL Server 2019 added batch mode memory grant feedback, potentially increasing mutex contention as more queries adjust their memory requests dynamically.

In SQL Server 2022, intelligent query processing enhancements include memory grant feedback persistence, which stores memory grant information across database restarts. This can reduce mutex contention by providing better initial memory grant estimates, though the mutex acquisition pattern remains unchanged.

The wait occurs specifically during these operations: initial memory grant evaluation, memory grant timeout processing, query compilation memory requests, and resource semaphore cleanup when queries complete. High RESOURCE_SEMAPHORE_MUTEX waits typically correlate with insufficient query memory configuration or concurrent execution of memory-intensive queries exceeding available grant memory.

-- Check current memory grant queue status and waiting queries
SELECT 
    mg.session_id,
    mg.request_id,
    mg.grant_time,
    mg.requested_memory_kb,
    mg.granted_memory_kb,
    mg.required_memory_kb,
    mg.used_memory_kb,
    mg.max_used_memory_kb,
    mg.query_cost,
    mg.timeout_sec,
    mg.resource_semaphore_id,
    s.host_name,
    s.program_name
FROM sys.dm_exec_query_memory_grants mg
INNER JOIN sys.dm_exec_sessions s ON mg.session_id = s.session_id
ORDER BY mg.requested_memory_kb DESC;

-- Identify resource semaphore wait statistics and bottlenecks
SELECT 
    rs.resource_semaphore_id,
    rs.target_memory_kb,
    rs.max_target_memory_kb,
    rs.total_memory_kb,
    rs.available_memory_kb,
    rs.granted_memory_kb,
    rs.used_memory_kb,
    rs.grantee_count,
    rs.waiter_count,
    rs.timeout_error_count,
    rs.forced_grant_count
FROM sys.dm_exec_query_resource_semaphores rs;

-- Analyze historical memory grant patterns and timeouts.
-- Note: sys.dm_exec_query_stats does not expose an avg_grant_kb column —
-- compute an average from total_grant_kb / execution_count, and use the
-- min/max/last_grant_kb columns (SQL Server 2016+) for per-execution detail.
SELECT 
    qs.sql_handle,
    qs.statement_start_offset,
    qs.statement_end_offset,
    qs.plan_handle,
    qs.execution_count,
    qs.total_grant_kb,
    qs.total_grant_kb / NULLIF(qs.execution_count, 0) AS avg_grant_kb,
    qs.min_grant_kb,
    qs.max_grant_kb,
    qs.last_grant_kb,
    qs.total_used_grant_kb,
    qs.total_ideal_grant_kb
FROM sys.dm_exec_query_stats qs
WHERE qs.total_grant_kb > 0
ORDER BY qs.total_grant_kb DESC;

-- Monitor current RESOURCE_SEMAPHORE_MUTEX wait times by session
SELECT 
    session_id,
    wait_type,
    wait_time_ms,
    wait_resource,
    blocking_session_id
FROM sys.dm_exec_requests
WHERE wait_type = 'RESOURCE_SEMAPHORE_MUTEX'
ORDER BY wait_time_ms DESC;

-- Check memory broker clerks and overall memory pressure
SELECT 
    memory_broker_type,
    allocations_kb,
    allocations_kb_per_sec,
    predicted_allocations_kb,
    target_allocations_kb,
    future_allocations_kb,
    overall_limit_kb,
    last_notification
FROM sys.dm_os_memory_brokers
WHERE memory_broker_type IN ('MEMORYBROKER_FOR_CACHE', 'MEMORYBROKER_FOR_STEAL', 'MEMORYBROKER_FOR_RESERVE');

Increase Query Memory Grant Timeout This script increases the default query wait time for memory grants, reducing timeout-related mutex contention.

-- Increase query wait timeout (default is -1 which equals 25 times the query cost)
-- Setting to 0 means unlimited wait time - use cautiously
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'query wait (s)', 600; -- 10 minutes, adjust based on workload
RECONFIGURE;
-- TEST IN DEV FIRST: Can cause queries to wait indefinitely for memory

Configure Memory Grant Feedback Enable or disable memory grant feedback to reduce unnecessary mutex contention from over-requesting queries.

-- Enable memory grant feedback at database level (SQL Server 2017+)
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

-- For persistent memory grant feedback (SQL Server 2022+)
ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = ON;

-- Monitor effectiveness with:
-- SELECT * FROM sys.dm_exec_query_memory_grants WHERE is_next_candidate_for_memory_grant_feedback = 1;

Optimize Resource Governor Memory Settings Configure Resource Governor to prevent single queries from consuming excessive memory grants.

-- Create resource pool with memory grant limits
CREATE RESOURCE POOL limited_memory_pool
WITH (
    MIN_MEMORY_PERCENT = 10,
    MAX_MEMORY_PERCENT = 30,
    CAP_CPU_PERCENT = 50,
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 10  -- Limit individual query memory grants
);

-- Create workload group
CREATE WORKLOAD GROUP memory_limited_group
WITH (
    GROUP_MAX_REQUESTS = 50,
    REQUEST_MAX_CPU_TIME_SEC = 300,
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 5
)
USING limited_memory_pool;

-- Apply configuration
ALTER RESOURCE GOVERNOR RECONFIGURE;
-- WARNING: Test thoroughly in development, can impact query performance

Add Memory to Reduce Pressure Immediate relief through max server memory adjustment if physical memory allows.

-- Check current memory configuration
SELECT 
    name,
    value_in_use,
    description
FROM sys.configurations
WHERE name LIKE '%server memory%';

-- Increase max server memory (if physical RAM available)
-- Calculate as: (Total Physical RAM - OS Reserve - Other Apps) * 0.9
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 16384; -- Adjust based on available RAM
RECONFIGURE;
-- CAUTION: Ensure sufficient memory remains for OS and other applications

Configure appropriate max server memory settings based on physical RAM, leaving adequate memory for the operating system and other applications. Monitor memory grant feedback effectiveness and disable it for databases with stable, well-tuned queries if it causes unnecessary mutex contention.

Implement Resource Governor workload groups to limit individual query memory consumption, preventing single queries from monopolizing memory grant resources. Set REQUEST_MAX_MEMORY_GRANT_PERCENT to reasonable values based on your concurrent query workload.

Regularly analyze query memory usage patterns using the diagnostic queries to identify queries consistently over-requesting memory. Work with application teams to optimize these queries through better indexing, query rewriting, or breaking large operations into smaller batches.

Establish monitoring for the resource semaphore wait counters and memory grant queue depths. Alert when waiter_count exceeds 10 or timeout_error_count increases rapidly, indicating sustained memory pressure requiring intervention.

Consider upgrading to SQL Server 2022 for persistent memory grant feedback, which reduces mutex contention by providing better initial memory grant estimates based on historical execution patterns stored across database restarts.