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.

  1. SELECT [object_name],
  2. [counter_name],
  3. [cntr_value] FROM sys.dm_os_performance_counters
  4. WHERE [object_name] LIKE '%Manager%'
  5. 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.

Jose

Jose

I am an Enterprise SQL Server Database Administrator with 10+ years of experience working with complex transactional environments.

Leave a Reply

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

Call Us Now

Or, Let Us Know How We Can Help

Tight On Time?

Schedule A Call: