Is SQL Server Memory Enough(Memory Grants Pending)

dbtut

6 years ago

In this article, we will examine several ways to determine if SQL Server Memory is sufficient.

SQL Server tries to use all memory on the operating system. In some cases, even if you do not limit the SQL Server, you may cause a bottleneck in the operating system. Therefore, you must configure MAX SERVER MEMORY. I would recommend you to read the article “Numa Nodes, MAX/MIN Server Memory, Log Pages In Memory and MAXDOP“.

You can check the adequacy of memory by pulling some information from Performance counter.

For example, page life expectancy or buffer cache hit ratio. For details, please refer to “Data Collector, Management Data Warehouse, Perfmon“.

A faster solution is to find the number of queries that are pending for memory. You can see the number of queries waiting to assign the memory with the help of the following query. This number should normally be 0 (zero).

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'

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'