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.
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.
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.