Nucleus Datakonsult Log Page Life Expectancy over time

Home

About me and Nucleus
Partners

Articles, tips etc.
My blog
Links
Microsoft articles
Forums

Courses and training




Microsoft Most Valuable Professional


Skor
R

Overview
Page Life Expectancy (PLE) is a performance monitor counter that will tell you for how long time a page is expected to live in cache from when it was brought into cache. A low value is an indication of high turnover of the cache. I.e., lots of I/O going on, basically. The thing with PLE is that looking at a particular point in time doesn't say much. You might not have had a "monster-query" for several hours and if you then look at PLE it might seem just fine. Or perhaps you do periodic index rebuilds, which will "turnover the cache" and if you look shortly after that you again don't see the picture for when the interesting things are (daytime). You get the picture. The idea behind this script is to log PLE every 5 minutes, or whatever frequency that you want.

Versions etc.
SQL Server 2008 and later.

Details
A table is created, defined as narrow as possible. this means that even if you log every 5 minutes over several years, it will only use some 10 - 20 MB. I usually create this table in a utility database, named sqlmaint. If you want to have it somewhere else, then adjust the scripts accordingly.

Then schedule an Agent job that does an insert into this table. Schedule as you wish. I recommend between every minute and every 10 minutes. If you want to log more frequent that every minute, you have to change the datatype for the dt column from datetime2(0) to datetime2(2).

Finally, you query the table with whatever SELECT statement you wish.

Limitations
For NUMA systems, you could have memory pressure in one NUMA node but not the other nodes. Hence logging the overall PLE could mask that situation. For such situations you want to modify this solution for use the MSSQL$P:Buffer Node object (instead of the MSSQL$P:Buffer Manager instance), and log every NUMA node instance.

Table definition
CREATE TABLE ple_log(dt datetime2(0) PRIMARY KEY, ple_value int)


INSERT statement
Schedule below in an Agent job, executed in the database where you created the table.

INSERT INTO ple_log (dt, ple_value)
SELECT SYSDATETIME(), cntr_value 
FROM sys.dm_os_performance_counters 
WHERE object_name LIKE '%:Buffer Manager%' 
  AND counter_name = 'Page life expectancy'


SELECT to read the data
Here you can be as imaginative as you wish. Below are some queries that can serve as a starting point. The information is excellent for displaying in diagrams using tools such Excel, Reporting Services, etc.
--All rows, most recent first
SELECT 
 dt
,ple_value AS ple_in_seconds
,ple_value/60 AS ple_in_minutes 
,CAST(DATEADD(ms, ple_value * 1000, 0) AS time(0)) AS ple
FROM ple_log 
ORDER BY dt DESC

--Exclude the time period for a batch executed at 21:18, which drops PLE.
--We exclude between 21:15 and 23:55. The only change from above query is the WHERE clause
SELECT 
 dt
,ple_value AS ple_in_seconds
,ple_value/60 AS ple_in_minutes 
,CAST(DATEADD(ms, ple_value * 1000, 0) AS time(0)) AS ple
FROM ple_log
WHERE CAST(dt AS time) NOT BETWEEN '21:15:00' AND '23:55:01'
ORDER BY dt DESC