Page life expectancy perfmonance counter

Question

Hi All,

Would like know of why Page life expectancy counter is no more a good metric to tell SQL Server needs more memory?
What value to be considered as good or bad?

Thanks,

Bob

Answer 1

Hi,

Page Life Expectancy (PLE) is the time in seconds that a data page kept in the buffer cache. In theory, it indicates the memory pressure since when there is a low memory then the server need to free space in the memory and therefor the time that the page stay in memory is lower.

why Page life expectancy counter is no more a good metric to tell SQL Server needs more memory?

There are such claims and there is something in it, but this is not black and white (yes/no) case in my opinion and the PLE can have a huge value in the long rage monitoring. You can read more about the problematic in this counter at Paul Randal post here:

https://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/

In addition, Obviously in the first several minutes after the server woke up, the value is not useful, since the memory still loaded with new pages. In addition you need to remember that the PLE provides information about an emergency after the emergency has happened usually (it is lagging counter) and you might miss the issue if you count only on this value.

You can follow the PAGEIOLATCH and RESOURCE_SEMAPHORE waits to get more information at the same time. PAGEIOLATCH is the time waiting for a page to be retrieved from disk and loaded into memory - high value means low memory. RESOURCE_SEMAPHORE happen when memory requests by a query cannot be granted.