Memory grants are queuing

What this check looks for

Two signals, either of which fires the check:

  • Right now: any waiter in sys.dm_exec_query_resource_semaphores, meaning SUM(waiter_count) > 0 across the semaphores.
  • Since the last restart: more than 10 minutes of accumulated RESOURCE_SEMAPHORE or RESOURCE_SEMAPHORE_QUERY_COMPILE wait time in sys.dm_os_wait_stats.

The first is a live problem. The second is evidence that it has been happening whether or not it is happening at this moment.

Why it matters

Every query that sorts or hashes has to reserve memory before it starts, and when the server runs out, the rest queue at the door rather than running slowly.

That distinction is what makes this hard to recognize. A server short of buffer pool gets gradually slower, which everybody understands. A server short of workspace memory produces queries that do not start at all. From the application’s side this looks like the server hanging, and while it is happening:

  • There is no blocking, so the blocking report is empty.
  • There is no CPU, because the queued queries are not running.
  • There is no disk activity, for the same reason.

Every dashboard says the server is idle, and the users say it is down. That combination sends most investigations in the wrong direction for the first half hour.

The cause is usually one bad plan rather than a genuine memory shortage. A query with a bad row estimate can ask for gigabytes of workspace memory, hold it for its entire execution, and use almost none of it. One such query on a schedule can starve everything else on the instance for the duration of its run.

How to confirm it yourself

What is queuing right now:

SELECT [resource_semaphore_id],
       [target_memory_kb]    / 1024 AS [target_mb],
       [total_memory_kb]     / 1024 AS [total_mb],
       [available_memory_kb] / 1024 AS [available_mb],
       [granted_memory_kb]   / 1024 AS [granted_mb],
       [grantee_count],
       [waiter_count],
       [timeout_error_count],
       [forced_grant_count]
  FROM sys.dm_exec_query_resource_semaphores WITH (NOLOCK)
 WHERE [resource_semaphore_id] IS NOT NULL;

Who is holding and who is waiting:

SELECT mg.[session_id],
       mg.[request_time],
       mg.[grant_time],
       mg.[requested_memory_kb] / 1024 AS [requested_mb],
       mg.[granted_memory_kb]   / 1024 AS [granted_mb],
       mg.[used_memory_kb]      / 1024 AS [used_mb],
       mg.[ideal_memory_kb]     / 1024 AS [ideal_mb],
       mg.[queue_id],
       mg.[wait_order],
       t.
  FROM sys.dm_exec_query_memory_grants AS mg WITH (NOLOCK)
 OUTER APPLY sys.dm_exec_sql_text(mg.[sql_handle]) AS t
 ORDER BY mg.[requested_memory_kb] DESC;

Compare granted_mb against used_mb. A query granted 4 GB that used 40 MB is the one to fix, and it is usually the one at the top.

The accumulated wait:

SELECT [wait_type], [waiting_tasks_count],
       [wait_time_ms] / 1000 AS [wait_time_seconds]
  FROM sys.dm_os_wait_stats WITH (NOLOCK)
 WHERE [wait_type] IN ('RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE');

How to fix it

Start with the query that asked for the most and used the least. In nearly every case this is a plan problem rather than a hardware problem.

  1. Fix the row estimate. An over-estimate comes from stale statistics, a table variable with no statistics at all, a scalar function in a predicate, or a parameter sniffing problem where the cached plan was built for a much larger parameter value. Update statistics first, because it is the cheapest thing to try.
  2. Look at the sort or hash itself. A missing index that forces a sort, or a join order that hashes a large input, is what the grant is for. Removing the sort removes the grant.
  3. Check max server memory. If the instance is configured to leave the buffer pool far smaller than the machine can support, workspace memory is proportionally small too.
  4. Consider Resource Governor as a limit on the damage rather than a fix. Capping REQUEST_MAX_MEMORY_GRANT_PERCENT stops one query starving the rest, at the cost of that query spilling to tempdb.
  5. On SQL Server 2019 and later, memory grant feedback adjusts repeated bad grants automatically, but only for a plan that stays in cache and only after it has run a few times.

Adding memory to the server is the answer least often, and it is the one most often reached for.

How long it takes

About two hours to find the query and understand why its estimate is wrong. Fixing the estimate can be one statistics update or an index change.

Report Why you would go there
Memory Grants and Spills Grants and spills together, which is the fuller picture.
Memory How the instance’s memory is configured and divided up.
Waits Whether RESOURCE_SEMAPHORE is significant against everything else.
Plan Warnings Plans with spills and bad estimates, which is where these come from.
Cardinality Report Estimated against actual rows, which is the root cause here.
Statistics Stale statistics behind a bad estimate.
Check
Missing or out of date statistics The most common reason an estimate is wrong.
Max server memory Whether the instance is configured to use the memory it has.
Worker threads are running out The other resource that produces an idle-looking hang.
Max degree of parallelism Parallel plans multiply the grant across threads.

Frequently asked questions

The server has plenty of free memory. Why are queries queuing? Workspace memory is a fraction of the buffer pool, not of the machine. A single query can also be capped at 25 percent of workspace by default, so one query does not have to consume everything to starve the rest.

RESOURCE_SEMAPHORE_QUERY_COMPILE is different? Yes. That one is memory to compile a plan rather than to run one, and it usually points at a flood of unparameterized ad hoc queries rather than at one large query.

The wait time is old and nothing is queuing now. sys.dm_os_wait_stats accumulates since the last restart or the last manual clear, so it may be describing an incident from last month. The live semaphore view is the one that says what is happening now.

Is a forced grant bad? forced_grant_count rising means queries waited so long they were let through with a minimal grant. They will spill to tempdb. It is SQL Server preventing a deadlock of the memory queue rather than a healthy state.