question

Bobsql-8788 avatar image
0 Votes"
Bobsql-8788 asked pituach commented

Page life expectancy perfmonance counter

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

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

pituach avatar image
1 Vote"
pituach answered pituach commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you pituach.

1 Vote 1 ·

You are most welcome :-)

0 Votes 0 ·