Page Life Expectancy (PLE) in SQL Server

Hey guys, In this blog I am going to explain you about Page Life Expectancy and How to monitor it.

Page Life Expectancy (PLE)-

Page Life Expectancy (PLE) is an age of a data page in seconds in the buffer cache or buffer memory after querying the tables with the loading data page into the buffer memory. Page Life Expectancy value indicates the memory pressure in allocated memory to the SQL Server instance.

To measure the Page Life Expectancy, there are two methods

Method-1: Using SQL Server Query,

With the help of a T-SQL command, obtain PLE for your SQL Server instance-

select * from sys.dm_os_performance_counters where counter_name like '%page life%'

Fig.1

In this case, cntr value stands in for the PLE, which in the results of the previous query is 29570. We always presume that the buffer memory lake is affecting query speed and that SQL Server does not have enough buffer pool space to store the data.

Method-2: Using Performance monitor,

Monitoring Page Life Expectancy-

An important way to gauge the pressure on the buffer memory in terms of seconds is to use the PLE counter value. Users can record it using SSMS, any other external tools, or Microsoft Performance Monitor; it always produces an average result. The T-SQL code above allows users to find the current value. Still, investigation is needed in a certain time frame to determine whether fluctuations occur at a particular point in time. By adding the PLE counter, users can utilize the Windows Performance Monitor to track the same.

Step 1- Go to the search option in Jump Station and log in to the server.

Step 2- Go to the SQL Server Buffer Manager in the performance monitoring tool on the server.

Step 3- In SQL Server Buffer Manager Search Page Life expectancy and this PLE option to the add counter.

Fig.2

Fig.3

Fig.4

Fig.5

The PLE graph is displayed here with the help of Performance Monitoring Tools.

How to Determine the Life Expectancy of a Page-

We agree to a 300-second rule set by Microsoft. Memory pressure is particularly severe if PLE lasts shorter than 300 seconds (5 minutes), and we must take care of the performance side. However, that computation only included 4GB of memory for the SQL Server. 

Conclusion-

There are numerous more buffer manager parameters in addition to PLE (Page Life Expectancy) that might impact SQL Server speed. If detected, we can assume that the primary problem is buffer memory pressure. Nowadays, larger memory boxes are prevalent, and PLE in the thousands is also relatively frequent.