|Log Page Life Expectancy over time|
About me and Nucleus
Articles, tips etc.
Courses and training
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.
SQL Server 2008 and later. See comment block in procedure source code for version history of the procedure.
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.
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.
CREATE TABLE sqlmaint.dbo.ple_log(dt datetime2(0) PRIMARY KEY, ple_value int)
Schedule below in an Agent job, executed in the database where you created the table.
INSERT INTO dbo.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 sqlmaint..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 sqlmaint..ple_log WHERE CAST(dt AS time) NOT BETWEEN '21:15:00' AND '23:55:01' ORDER BY dt DESC