During performance tuning consultationconsultation, there are plenty of counters and values, I often come across. Today we will quickly talk about Page Life Expectancy counter, which is commonly known as PLE as well.
You can find the value of the PLE by running the following query.
SELECT [object_name],
[counter_name],
[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'
The recommended value of the PLE counter is (updated: minimum of) 300 seconds. I have seen on busy system this value to be as low as even 45 seconds and on unused system as high as 1250 seconds. Page Life Expectancy is the number of seconds a page will stay in the buffer pool without references. In simple words, if your page stays longer in the buffer pool (area of the memory cache) your PLE is higher, leading to higher performance as every time request comes there are chances it may find its data in the cache itself instead of going to the hard drive to read the data.
Now check your system and post back what is this counter value for you during various times of the day. Is this counter any way related to performance issues for your system?
Note: There are various other counters which are important to discuss during the performance tuning and this counter is not everything.
Update: PLE counter should be me 300 or more (OLTP Blueprint – A Performance Profile of OLTP applications)
Update2: Paul has done very technical and informative blog post where he explains the reasons and the correct answer for PLE. Read here.
Reference: Pinal Dave (https://blog.sqlauthority.com)
70 Comments. Leave new
PLE = 300 as a recommended value? Utter rubbish.
Paul,
I do not understand why you disagree. Even Microsoft suggests the same: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/cc966401(v=technet.10) read the third topic:
Memory bottleneck if…
Consistently low average page life expectancy. See Average Page Life Expectancy Counter which is in the Perfmon object SQL Server Buffer Manager (this represents is the average number of seconds a page stays in cache). For OLTP, an average page life expectancy of 300 is 5 minutes. Anything less could indicate memory pressure, missing indexes, or a cache flush.
I suggest you correct them as well with proper reasoning.
— Roger
I may be totally wrong but my understanding is that the key is the word ‘average’. The counter does not give an average it gives what is true at any given point of time, which depends again on workload and full scans that affect it and may or may not be a problem. It is difficult to gauge what is average especially on systems with inconsistent workloads, it may be different at different points in time. I have not had much luck using PLE as a reliable performance metric, just two cents, thank you.
I am getting Page life Expectancy is too low error. please list me the steps to resolve the error.
at our server result is as per bellow.
SQLServer:
Buffer Manager – Page life expectancy – 222356
Is it good or not ?
You are good to go. It shows there is very less chances for memory pressure.
Hello Pinal,
I ran the above query on my server, it is giving output as follows
object_name SQLServer:Buffer Manager
counter_name Page life expectancy
cntr_values 510
Please comment on this…. about the server status and what actions i need to take to improve the performance of the server.
This counter is NOT a database specific counter.
Yes. But a box which is used as a database server is expected to use largest portion of memory for storing data/index pages and hence it gives good indication of how long data/index pages remain in memory.
Hi Pinal, I’ve been following this advice for quite some time which was mentioned in a MS whitepaper as well. Need to search for more information since Paul contradicted this!!
Now, one of the highest demand moment of the db, it´s returning 2845
The value of 300 is from 5 years ago (the publication date on that WP is 2006) and is *very* low on current systems. That means your entire in-memory buffer pool is being replaced every five minutes – a very inefficient state of affairs. There’s no right answer – but Pinal’s original blog post said that PLE should EQUAL 300, which is why I called BS in my comment. He’s since altered it.
SQL Server: Buffer Manager: Page life expectancy = 20286
Great post, Pinal! The staff at SSWUG.org liked it so much that they have listed it as one of today’s featured articles.
hi sir,
in my organization one of my server showing page lifeexpectency value is 40.
what i will do? please iam waiting for ur reply
Thanks&Regards
SRINATH
Interesting read on the article. I have looked at many counters and articles over the last week and so I wanted to share what I see on my server….
Problems we see from time to time. Is long pauses in our front end application. But our web applications seem to do fine. The functionality between the two are similar but not the same.
SO HERE ARE MY READINGS…DURING ONE OF THOSE PAUSES…
process (% Processor Time):instance:sqlserv:
….avg=15 max=114
WHY DID IT ALL THE SUDDEN HIT 114?
sqlServer (Buffer Page Life Expectancy )
….avg 909 max 1340
(Not an issue: i believe sql server does cleanup which reduces this)
SQLSERVER:Plan Cache (Cached Object Counts)
….avg 6431 max 8622 min= 2712
ALL THE SUDDEN DROPPED TO 2712
SQLSERVER:Plan Cache (Cache Hit Ratio)
…avg 85 max=90 min=75
ALL THE SUDDEN DROPPED TO 75
SQLSERVER:Plan Cache (Cache Pages)
..avg 107387 max=141381 min=65837
ALL THE SUDDEN DROPPED to 65K
In Summary: The analysis indicated the drastic changes int
Also
Context switches spiked to 6181
%Processor Time spiked to 114
Avg Disk Que Length spiked to 76
Avg Disk sec/Read spiked to .472
Pages/Sec spiked to 31.239
SQLServer Locks (avg wait time ms) spiked to 632
SQLServer Locks (Lock Wait Time ms) spiked to 2847
SQLServer:SQL STATS (SQL COMPILATIONS) spiked to 38
note:recompilations stayed the same
SQL STATS (batch requests) spiked to 118
BUT WHAT I THOUGHT SHOULD ALSO CHANGE IS THIS…
SQLSERVER:BUFFER MANAGER (BUFFER CACHE HIT RATIO )STAYED AT 99.8???
to note: these stats are from 4pm which is not heavy hitting hours….6:30am to 2:30pm is our busiest times.
On another note:
Page Life Expectancy for our database stays above 300.
min 382
max 1450
avg 613
But I thought “page life expectancy” should correlate with “CACHING METRICS” i mentioned in my previous post.
ON OUR SERVER PAGE Expectancy does not rapidly bounce. It slowly rises and once in awhile it appears to “flush”.
But my cache I though would progress similarly and it does not.
PLAN Cache(Object count) gradually goes up and up…then suddenly drops to a low
The SQL SERVER:Plan Cache (Cache Pages) also progress similarly….then suddenly drops to a low
But the CACHE HIT RATIO and Page Life Expectancy keeps humming along even during those sudden drops.
TO NOTE: the graph on the Cache Hit Ratio does bounce pretty good between 84 and 91 during normal operations
Why isn’t this a steady graph similar to Page life expectancy?
… I thought cache was memory and memory was buffered..
SQLServer:Buffer Manager Page life expectancy 1528
What it suggests?
Hi Pinal,
I’m currently working with SCOM 2007 R2. My organization took the decision of start monitoring the PLE (value = 1000), so a new Management Pack was developed.
Now I need to test the Management Pack, so I need to run a script or something in order hit the threshold.
Could you kindly recommend me the better way to do it?
Thanks!
Hi Sir Pinal,
These are my perfmon results for Page Life Expectancy.
Average: 24,731
Median: 21,234
Min: 45
Max: 61,354
Std Deviation: 16,497
Thanks!
I run this on two instances on the same server an get totally different results. I understood this is an OS counter not SQL so why are they different?
Kyle: I believe it is a OS counter wrt SQL instance not with an specific database,
I am continually receiving notifications from our monitoring system that Page Life Expectancy is in warning, and Lock Timeouts/sec are in critical. I ran the above query and the value for PLE varies constantly from below 100 to around 400. I don’t know if we’re seeing performance hits or not, and I’m not sure how to address these issues.
Thanks.
Hi Pinal,regards from colombia, can you explain to me why in my server, this counter go down in a determinate moment quickly. for example I monitoring this with SQLdm, in a moment is 800 and later 200, then increase again.
thanks for your help.
Hi, Pinal I have SQL Server 2008 and my Page Life Expectancy is 80