Follow Me!
-
-
Recent Posts
- Microsoft SQL Server MVP
- I am joining SQLskills
- Getting Your Worth the Struggles with Moving Ahead
- #Summit14 Embarcadero Book Signing
- Managing Geeks – A journey of leading by doing
My Book
Recent Comments
- Greg Smith on Cannot create Cluster – the computer X is joined to a cluster
- Paul on Cannot create Cluster – the computer X is joined to a cluster
- Sudhir Giri on Cannot create Cluster – the computer X is joined to a cluster
- John Sterrett on How to check for last SQL Server backup
- efyuze on How to check for last SQL Server backup
Archives
- July 2015
- December 2014
- November 2014
- October 2014
- September 2014
- July 2014
- June 2014
- May 2014
- April 2014
- March 2014
- February 2014
- January 2014
- November 2013
- October 2013
- September 2013
- August 2013
- July 2013
- June 2013
- March 2013
- February 2013
- January 2013
- November 2012
- September 2012
- August 2012
- July 2012
- June 2012
- May 2012
- April 2012
- March 2012
- January 2012
- December 2011
- October 2011
- September 2011
- August 2011
- July 2011
- June 2011
- May 2011
- April 2011
- January 2011
- December 2010
- October 2010
- September 2010
Categories
- Backups/Recovery
- BMC Control-M
- PowerShell
- Presentations
- Query Tidbits
- SQL Tuning
- SQLServerPedia Syndication
- SSIS – How To
- Tibits
- Training/Events
- Uncategorized
Google+
Find us on Google+
What is Page Life Expectancy (PLE) in SQL Server
Posted on March 8, 2013 by Tim Radney
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’
This entry was posted in Query Tidbits, SQL Tuning, SQLServerPedia Syndication and tagged Page Life Expectancy, SQL Server Performance. Bookmark the permalink.
3 Responses to What is Page Life Expectancy (PLE) in SQL Server
Pingback: SQL SERVER – Good Value for Page Life Expectancy – Notes from the Field #026 | Journey to SQL Authority with Pinal Dave
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.