Page Life Expectancy

Page Life Expectancy is the number of seconds a page will stay in the buffer pool without references. In simple words, if your page stays longer in the buffer pool (area of the memory cache) your PLE is higher, leading to higher performance as every time request comes there are chances it may find its data in the cache itself instead of going to the hard drive to read the data.

The basic fundamental of Microsoft SQL Server for querying data is to load the necessary data pages into the buffer pool from the disk and return it to the client. If the buffer does not have enough space to process it, then the oldest data pages will be eradicated to make some room as required for new pages.

PLE can raise performance issues by building pressure on buffer when your PLE goes down and not coming up for a while. There can be many more activities which can cause this issue.

• Index Maintenance
• Big Queries (i.e., Report Query)
• Queries with an improper and less efficient execution plan
• Data Migration Activity
• Parameter Sniffing issue
• Duplicate Indexes
• Multiple Execution Plans for a single procedure and many more

The recommended value of the PLE counter is (updated: minimum of) 300 seconds.

--You can find the value of the PLE by 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'

How to Calculate Page Life Expectancy

As per Microsoft standard, we follow a rule of 300 seconds. If PLE goes less than 300 seconds (5 Minutes), then memory pressure is very high, and we have to take care of the performance side. Still, that calculation was for just 4GB memory allocation to the SQL Server. For your server, it should be different as per the formula. You should worry about the SQL Server instance when PLE goes down than the below calculation:

PLE (Page Life Expectancy) threshold = ((Buffer Memory Allocation (GB)) / 4 ) * 300

For example, the machine is configured with 128GB, and 110GB is allocated to the SQL Server instance, then the PLE threshold will be as below:

PLE (Page Life Expectancy) threshold = (110 / 4) * 300

PLE (Page Life Expectancy) threshold = 8250 Seconds