RESOURCE_SEMAPHORE When SQL Server Is Nowhere Near Its Memory Cap

Queries wait on RESOURCE_SEMAPHORE even though SQL Server is well below max server memory. Check whether low Windows memory has shrunk the grant pool before raising the cap. For other causes, see Troubleshoot RESOURCE_SEMAPHORE waits.

Confirm the Query Is Waiting for a Grant

One row per query that has asked for workspace memory and been given none. sys.dm_exec_query_memory_grants keeps grant_time NULL until the grant arrives.

SELECT
    r.session_id,
    r.status,
    r.wait_type,
    r.cpu_time                      AS cpu_ms,
    r.total_elapsed_time / 1000     AS elapsed_sec,
    g.requested_memory_kb,
    g.granted_memory_kb,
    g.grant_time
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_query_memory_grants AS g
    ON  g.session_id = r.session_id
    AND g.request_id = r.request_id
WHERE g.grant_time IS NULL
OPTION (MAXDOP 1);   -- needs no memory grant, so it cannot queue behind the queries it is looking for

16 ms of CPU in 50 seconds is a query that has not started, not a slow one. It asked for 52,848 KB and has been given nothing.

Read the Grant Pool Against the Cap

This is the check that makes this a different problem. One row: the cap, what SQL Server is aiming to hold now, the most the grant pool could grow to, and the size it is right now.

SELECT
    c.max_server_mb,
    i.committed_target_kb / 1024    AS sql_target_mb,
    s.max_target_memory_kb / 1024   AS pool_max_mb,
    s.target_memory_kb              AS pool_now_kb,
    s.grantee_count,
    s.waiter_count,
    p.process_physical_memory_low   AS memory_low,
    m.available_physical_memory_kb / 1024 AS os_free_mb
FROM sys.dm_exec_query_resource_semaphores AS s
CROSS JOIN sys.dm_os_sys_info       AS i
CROSS JOIN sys.dm_os_process_memory AS p
CROSS JOIN sys.dm_os_sys_memory     AS m
CROSS JOIN (SELECT CAST(value_in_use AS int) AS max_server_mb
            FROM sys.configurations
            WHERE name = 'max server memory (MB)') AS c
WHERE s.resource_semaphore_id = 0   -- the regular semaphore, not the small-query one
  AND s.pool_id = 2                 -- the default pool; drop this line if you use Resource Governor
OPTION (MAXDOP 1);                  -- needs no memory grant, so it cannot queue behind the queries it is looking for

The cap is 2,048 MB. SQL Server is aiming for 442 MB, the pool could grow to 332 MB, and right now it is 10,560 KB. The waiting query wants 52,848 KB, 5 times the whole pool, so it waits until the pool grows or the request is cut.

pool_max_mb follows sql_target_mb, not the cap: 332 of 442 MB here, and 840 of 1,160 MB when the same instance had memory back. sql_target_mb is committed_target_kb, which SQL Server works out from the load and the memory on the machine, not from the setting alone.

memory_low = 1 is process_physical_memory_low: SQL Server responding to a low physical memory notification from Windows. While it holds, the target stays down and the pool with it. Minutes apart on the same instance, with no setting changed, the same query returned memory_low 0, a target of 1,160 MB and a pool of 618,112 KB, and the same sort ran straight away.

Which Case You Have

Let One Query Run While You Free Memory

The fix is on the host. While you get memory back, a query you need now can be capped so its request fits the pool. MAX_GRANT_PERCENT is a percentage of the per-query limit, and that limit is REQUEST_MAX_MEMORY_GRANT_PERCENT of the pool, 25 by default.

-- the lab query; put the hint on the query you need to run now
SELECT OrderId, CustomerRef, Notes
FROM dbo.Orders
ORDER BY CustomerRef
OPTION (MAX_GRANT_PERCENT = 10);

Read from the actual execution plans on the lab against a 10,560 KB pool. The plans put the per-query limit at about 85 MB, a quarter of the most the pool could grow to, which is how one request can be bigger than the whole pool as it stands. The hint brings the request under the pool, and the price is the spill. Take it out once the host has memory again.

What Will Not Help

• Raising max server memory. SQL Server is not reaching the cap it already has.
• Killing the waiters. The queue empties, the pool stays the same size, and the next query that needs a grant waits the same way.
• Tuning the query that noticed. A small diagnostic stuck in the queue is a symptom of the pool, not the cause.

Frequently Asked Questions