For Day 17 of this series, we start out with Query #39, which is PLE by NUMA Node. This query retrieves information from the sys.dm_os_performance_counters dynamic management view about your page life expectancy (PLE) by NUMA node. Query #39 is shown in Figure 1.
1: -- Page Life Expectancy (PLE) value for each NUMA node in current instance (Query 39) (PLE by NUMA Node)
2: SELECT @@SERVERNAME AS [Server Name], [object_name], instance_name, cntr_value AS [Page Life Expectancy]
3: FROM sys.dm_os_performance_counters WITH (NOLOCK)
4: WHERE [object_name] LIKE N'%Buffer Node%' -- Handles named instances
5: AND counter_name = N'Page life expectancy' OPTION (RECOMPILE);
6:
7: -- PLE is a good measurement of memory pressure
8: -- Higher PLE is better. Watch the trend over time, not the absolute value
9: -- This will only return one row for non-NUMA systems
10:
11: -- Page Life Expectancy isn’t what you think…
12: -- https://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/
Figure 1: Query #39 PLE by NUMA Node
I think that page life expectancy (PLE) is probably one of the best ways to gauge whether you are under internal memory pressure, as long as you think about it correctly. What you should do is monitor your PLE value ranges over time so that you know what your typical minimum, average, and maximum PLE values are at different times and on different days of the week. They will usually vary quite a bit according to your workload.
The ancient guidance that a PLE measurement of 300 or higher is good, is really not relevant with modern database servers with much higher amounts of physical RAM compared to 10-12 years ago. Basically, higher PLE values are always better. You want to watch the ranges and trends over time, rather than focus on a single measurement.
Query #40 is Memory Grants Pending. This query retrieves information from the sys.dm_os_performance_counters dynamic management view about the current value of the Memory Grants Pending performance counter. Query #40 is shown in Figure 2.
1: -- Memory Grants Pending value for current instance (Query 40) (Memory Grants Pending)
2: SELECT @@SERVERNAME AS [Server Name], [object_name], cntr_value AS [Memory Grants Pending]
3: FROM sys.dm_os_performance_counters WITH (NOLOCK)
4: WHERE [object_name] LIKE N'%Memory Manager%' -- Handles named instances
5: AND counter_name = N'Memory Grants Pending' OPTION (RECOMPILE);
6:
7: -- Run multiple times, and run periodically is you suspect you are under memory pressure
8: -- Memory Grants Pending above zero for a sustained period is a very strong indicator of internal memory pressure
Figure 2: Query #40 Memory Grants Pending
This query is another way to determine whether you are under severe internal memory pressure. The value of this query will change from second to second, so you will want to run it multiple times when you suspect you are under memory pressure. Any sustained value above zero is not a good sign. In fact, it is a very bad sign, showing that you are under pretty extreme memory pressure.