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

Question: What is Memory Grants Pending in SQL Server?

Answer: Very interesting question and this subject is so long, it will be difficult to cover in a single blog post. I will try to answer this question in just 200 words as usual in the interview, we have only a few moments to give the correct answer.

Memory Grants Pending displays the total number of SQL Server processes that are waiting to be granted workspace in the memory.

In the perfect world the value of the Memory Grants Pending will be zero (0). That means, in your server there are no processes which are waiting for the memory to be assigned to it so it can get started. In other words, there is enough memory available in your SQL Server that all the processes are running smoothly and memory is not an issue for you.

Here is a quick script which you run to identify value for your Memory Grants Pending.

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'

Here is the result of the above scripts:

If you have consistently value of this setting higher than 0, you may be suffering from memory pressure and your server can use more memory. Again, this is one of the counters which indicates that your server can use more memory.

Reference: Pinal Dave (https://blog.sqlauthority.com)