SQL SERVER – What is Page Life Expectancy (PLE) Counter

During performance tuning consultation 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 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 (update: 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 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 hard drive to read the data.

Now check your system and post back what is this counter value for you during various time of the day. Is this counter any way relates 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)

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

46 thoughts on “SQL SERVER – What is Page Life Expectancy (PLE) Counter

  1. 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.

    • 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.

  2. 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!!

  3. 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.

    • Hello Sir,

      Thank you for your comment. However, English is my third language and I had tried to communicate that lower value is not good and higher value is much better.

      Additionally, I had also suggested that there are other things yet to come and this counter is not everything.

      Hope to avoid confusion in future.

      Kind Regards,
      Pinal

  4. 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.

  5. 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..

  6. 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!

  7. 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!

  8. 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.

  9. 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.

  10. Hi Pinal,

    At our SQLServer the result is as below.

    Buffer Manager – Page life expectancy – 2328
    Is it good or not ?

  11. Does it really helpful. Its just a SQL Server engine who takes care of it. I tried to clear the cache manually and then provided Lock pages in memory.
    The issue mostly occurs in Standard edition of sql server.

    Please help me to get it fixed.

  12. We have three identical boxes running SQL2005 each with 56GB internal and shared SAN connections, dual qaud core xeons.
    Server 1 is used for OTLP, Normal PLE is around 6000, peaks to 12000 during off-time, drops to 600 or less during login time.
    Server 2 is used for reporting. Normal PLE is around 60000, peaks to 120000 during off-time, drops to as low as 150.
    Server3 is on standby and has a PLE of 142000.

    Trying to find why the PLE drops during login times and if there’s a solution.

  13. Hello Pinal,

    Great Post. I seems to be OS Counter. I executed the query on two different servers, the outcome was different on both of them. Confusing to see two different OS parameter values on two servers with identical configurations.

    Values were : 8925 and 207671

    Is it okay or any action is needed, Please suggest.

    R.Kapil

    • Why would this be confusing? It is a SQL counter which is highly dependent of the SQL server load and also of the query behavior – not only the configuration. Your values however, are both fine.

  14. Pinal – I appreciate your posts and find them helpful. There is much to keep in mind in administering SQL Server, and I think those who have something to contribute (or dispute) can do so with courtesy to the benefit of all, without disparagement.

  15. Hi Pinal,
    I read your blog very often but this is the first time I’m leaving a comment. I know you wrote this a while ago, but it seems that the idea of a best practice value (despite Microsoft’s recommendation) is no longer valid. I just read this: http://bit.ly/nacvaU where they suggest getting a baseline figure and working from there.

  16. Ok maybe don’t wanna retire this blog cause it stand #1 on google search for “page life expectancy” . But PLEASE PLEASE update the 300 sec metric on page life expectancy to a higher value. I’ve always been a fan of your website. When i make recommendations to my company and the recommendations is based on your blogs, I want to make sure they are valid and up to date.

  17. There’s a really good reference here for different database checks (this one is for SQL 2012):
    [link to dropbox is showing 404 so removed]

    If you have multilple numa nodes, you can use the following:

    SELECT @@SERVERNAME AS [Server Name], [object_name], instance_name, cntr_value AS [Page Life Expectancy]
    FROM sys.dm_os_performance_counters WITH (NOLOCK)
    WHERE [object_name] LIKE N’%Buffer Node%’ — Handles named instances
    AND counter_name = N’Page life expectancy’ OPTION (RECOMPILE);

  18. Hello Pinal ,
    Thanks for your post and in my server the counter value is 162238.
    Is it Good r Bad? to have such a value

  19. As this thread still seems active, I thought I’d add a couple of useful links…

    http://www.sqlskills.com/blogs/jonathan/finding-what-queries-in-the-plan-cache-use-a-specific-index/

    …and…

    https://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/

    I have for a number of years considered the recommendation by MS of 300 for PLE to be way too low. Both Jonathan and Paul – very well respected experts in SQL Server – cover this very well, with Jonathan putting forward a reasonable formula for attempting to calculate PLE.

    Hope its useful.

    humbleDBA

    PS You should believe all you read because it is from MS. They make mistakes too.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s