What is Memory Grants Pending in SQL Server? – Interview Question of the Week #103

Question: What is Memory Grants Pending in SQL Server?

Answer: This subject could fill a long article, but an interview gives us only a few moments. Memory Grants Pending counts requests waiting for workspace memory. Sorts and hash operations are common reasons a query asks for a grant before it can proceed.

Here is the quick counter query from my original answer:

SELECT object_name, counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Memory Manager%'
  AND [counter_name] = 'Memory Grants Pending';

Zero is the value I hope to see, but it means only that no grant request was waiting at that instant. It does not prove there was no earlier memory pressure or that every query had a sensible grant. A single nonzero sample calls for investigation; a counter that stays above zero during a slow workload deserves closer attention.

I would then inspect the waiting and granted requests in sys.dm_exec_query_memory_grants, the RESOURCE_SEMAPHORE waits, and the expensive plans behind them. Excessive grants, poor estimates, and too many concurrent memory-hungry queries can all contribute. More RAM might help a genuine capacity shortage, but the counter alone is not a purchase order.

That is my short interview answer: Memory Grants Pending tells you how many requests are waiting now, then you investigate why.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.