What is Page Life Expectancy (PLE) in SQL Server
Being a production DBA means having to quickly look at a SQL Server and diagnose issue with it. We know that a quick look at perfmon or task manager can tell us of high CPU usage, but what is a good way to quickly look at proper memory usage.
Checking out the Page Life Expectancy (PLE) counter is a great way to check for memory pressure. A value too low for your system indicates that pages are being flushed from the buffer pool too quickly. The longer a page can stay in the buffer pool and be read from memory the better. We all know that having to constantly go back to disk to fetch data is costly.
I won’t get into the specifics of which number is too high or too low for a PLE counter, but I will say that any system in the double digit seconds is bad. I have seen systems in single digits and some systems that I measure PLE in days. I have updated my query to reflect those situations. I also like to include the uptime counter just in case I get on a system that SQL was recently restarted, if the server has only been up for 10 minutes, I cannot expect the PLE counter to be more than 10 minutes.
I highly recommending trending this value during the day to monitor usage on your servers. If you find this value consistently low it does not mean you need to rush and add more memory, you should first look at your queries and indexes to make sure you are not reading unnecessary data. One bad query “select * from bigtable” can crush your PLE counter.
I have included two screen shots showing the results from two servers, one showing in days and one in hours.
Notice on the image above that the PLE is the uptime of the server.
In the image above the server has been up for almost two weeks and has a PLE of 235 minutes.
SELECT @@servername AS INSTANCE
,[object_name]
,[counter_name]
, UPTIME_MIN = CASE WHEN[counter_name]= ‘Page life expectancy’
THEN (SELECT DATEDIFF(MI, MAX(login_time),GETDATE())
FROM master.sys.sysprocesses
WHERE cmd=‘LAZY WRITER’)
ELSE ”
END
, [cntr_value] AS PLE_SECS
,[cntr_value]/ 60 AS PLE_MINS
,[cntr_value]/ 3600 AS PLE_HOURS
,[cntr_value]/ 86400 AS PLE_DAYS
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Manager%’
AND[counter_name] = ‘Page life expectancy’
4 Comments
The sys.sysprocesses table is going away, so I made one minor change to get the command from “sys.dm_exec_sessions” and “sys.dm_exec_requests”:
SELECT @@SERVERNAME AS 'INSTANCE',
[object_name],
[counter_name],
CASE
WHEN [counter_name] = 'Page life expectancy'
THEN (
SELECT DATEDIFF(MI, MAX([login_time]), GETDATE())
FROM sys.dm_exec_sessions DMES
INNER JOIN sys.dm_exec_requests DMER
ON [DMES].[session_id] = [DMER].[session_id]
WHERE [command] = 'LAZY WRITER'
)
ELSE ''
END AS 'UPTIME_MIN',
[cntr_value] AS 'PLE_SECS',
[cntr_value] / 60 AS 'PLE_MINS',
[cntr_value] / 3600 AS 'PLE_HOURS',
[cntr_value] / 86400 AS 'PLE_DAYS'
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy';
Cool Ken, thanks for posting the code. This will certainly come in handy on v.Next or which ever version finally drops the sys.sysprocesses table.
Nice.. 🙂