Item: Page life expectancy
What is the SQL Page Life Expectancy (PLE)?
PLE is the expected time (in seconds) that a data file page read into the buffer pool will remain in memory before being pushed out of memory to make room for a different data file page.
This metric is not an aggregate or accumulation, but merely a point in time value.
It’s one symptom that can help lead you in the right direction.
Why should you care about it?
A deficient value of Page Life Expectancy (PLE) may indicate signs of memory pressure.
Notes: Currently, there are controversies about this metric. Use it as a complement to other indicators to confirm the real problem.
How can I check it?
You can find the value of the PLE in the sys.dm_os_performance_counters, running the following query.
SELECT [object_name], [counter_name], [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Manager%' AND [counter_name] = 'Page life expectancy'
Nowadays, a good reference value can be calculated using this formula:
(Max Server Memory/4GB) * 300 is more accurate.
How to fix it?
You can do some things:
- Do a more in-depth investigation using the wait stats.
- Look for queries with high numbers of physical reads and then fix those queries.
- Try to correlate the PLE drop with scheduled Agent jobs that perform database maintenance.