Should you care about page life expectancy in the SQL Server?

Category: Performance
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:

  1. Do a more in-depth investigation using the wait stats.
  2. Look for queries with high numbers of physical reads and then fix those queries.
  3. Try to correlate the PLE drop with scheduled Agent jobs that perform database maintenance.

More information

Microsoft -SQL Server, Buffer Manager Object.

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.