locked
Page Life Expectancy is 98 very low for 1 hour ? RRS feed

  • Question

  • Hi,

    Ver: SQL 2008 R2

    RAM : 64 GB

    Page Life Expectancy very low, it is 100 for 1 hour after it is crossing 300 .

    can you suggest me , how can resolove and what are limitaions on this .

    There  is any other factors to impact on PLE.

    Where  can find  the cause?

    What are the reason for this?

    If i missed any thing. please share with me.

    I will update here.

    Thanks in advance.

    Friday, October 31, 2014 7:49 AM

Answers

  • Hello Vijay,

    What is the Max Server memory allocated to SQL Server? Also what is the size of Page file size configured?


    Regards, Don Rohan [MSFT]


    Friday, October 31, 2014 9:51 AM
  • Does SQl Server service account has Locked pages in memory privilege ? PLE can be low if poor query is running on system and jumping to conclusion that this is memory pressure will not be correct.

    Monitor perfmon counters mentioned in this link. Monitor for at least hour or two and post result here. Please press CtRl+R and see numeric report not graphic.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Friday, October 31, 2014 10:32 AM
    Answerer
  • It is normal and expected for PLE to drop temporarily after DBCC CHECKDB, rebuilding indexes and other maintenance functions.

    • Marked as answer by vijay_1234 Monday, November 3, 2014 11:03 AM
    Friday, October 31, 2014 12:48 PM
    Answerer
  • Hi vijay_1234,

    According to your description, you want to know:

    1. What are the limitations on low Page Life Expectancy (PLE)?
    2. What are the reasons to cause a low Page Life Expectancy (PLE)?
    3. Where can we find the cause of low Page Life Expectancy (PLE)?

    Right?

    Question 1: What are the limitations on low Page Life Expectancy (PLE)?

    If lots of pages are being overwritten with new data very often, the average PLE will be low. If most pages in our Buffer Pool (BP) remain there for a long time without being overwritten, the average PLE will be high. So if we're doing a big data load, low PLE is ok, but if we're doing read-mostly activities, we will want PLE to be as high as possible.

    Question 2: What are the factors to cause a low Page Life Expectancy (PLE)?

    • Situation 1: The queries being run have changed to look at data that they weren't before, hence we now read in this new data into the BP. Or we're reporting just after a data load of different data, or just a large data load.
    • Situation 2: The queries being run are inefficient, and are reading unnecessary data into the BP which is therefore being constantly turned over.
    • Situation 3: There are large numbers of efficient queries (or a few very large efficient queries) which are saturating the BP.

    Question 3: Where can we find the cause of low Page Life Expectancy (PLE)?

    1. About situation 1, it is normal, but it should be short-lived. we'd expect to see the PLE drop off a cliff, then build back up over the threshold and stay there until the next shift in data requirement. If it drops and stays there, we're probably in situation 2 or 3.
    2. we can find this out by looking at the top queries for physical IO usage and checking out the query plans. If we see lots of scans, there may be some tuning to be done! Since 80% of performance problems come down to poor T-SQL (which may therefore create inefficient Query Plans), consider this before we think we're in situation 3!
    3. About situation 3, we've done our best, the queries are as efficient as they can get, but our PLE is still low. We can either spread out the queries (maybe running Agent Jobs one after the other instead of at the same time), or increase the amount of Buffer Pool memory. NEVER leave less than 1GB free for the OS, 2GB for servers with >64GB RAM, 4GB for servers with >128GB RAM. It's not that it's "free" and never used, the OS will use it and release it in much less than a second - less time than PerfMon or most tools' data gather interval, so it's being used but we don't get to see it. So if our PerfMon counter "Memory:Available Memory in MB" is under these thresholds and we're in situation 3, we need more physical memory so we can increase BP AND keep enough memory for the OS.

    For more details about SQL Server Page Life Expectancy, please refer to the following article: http://blogs.msdn.com/b/mcsukbi/archive/2013/04/12/sql-server-page-life-expectancy.aspx

    If you have any question, please feel free to let me know.

    Regards,
    Jerry Li


    • Edited by Donghui Li Monday, November 3, 2014 8:22 AM
    • Marked as answer by vijay_1234 Monday, November 3, 2014 11:03 AM
    Monday, November 3, 2014 8:22 AM
  • Hi Tom,

    Yes, When PLE level is low during the time Rebuiling index job is runing

    We changed job schedule time even after same issue coming.

    The problem is with your queries running and index rebuild task you are doing. I asked you to capture perfmon counters you did not, how do you expect us to give correct answer ?

    I am sure you are rebuilding index through maintenance  plan which is bad. Create a custom script which only rebuilds those indexes(having fragmentation >40) which are fragmented not all of it. Reorganize index with fragmentation between 10 and 40.You can also use Ola Hallengern script

    You already have got lot of explanation about PLE

    I have requested you many times don't just post question for sake of doing it if you really have issue come with complete details so that we can help otherwise its just wastage of time


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Marked as answer by vijay_1234 Monday, November 3, 2014 11:09 AM
    Monday, November 3, 2014 10:51 AM
    Answerer

All replies

  • Hello Vijay,

    What is the Max Server memory allocated to SQL Server? Also what is the size of Page file size configured?


    Regards, Don Rohan [MSFT]


    Friday, October 31, 2014 9:51 AM
  • Does SQl Server service account has Locked pages in memory privilege ? PLE can be low if poor query is running on system and jumping to conclusion that this is memory pressure will not be correct.

    Monitor perfmon counters mentioned in this link. Monitor for at least hour or two and post result here. Please press CtRl+R and see numeric report not graphic.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP


    Friday, October 31, 2014 10:32 AM
    Answerer
  • It is normal and expected for PLE to drop temporarily after DBCC CHECKDB, rebuilding indexes and other maintenance functions.

    • Marked as answer by vijay_1234 Monday, November 3, 2014 11:03 AM
    Friday, October 31, 2014 12:48 PM
    Answerer
  • Hi vijay_1234,

    According to your description, you want to know:

    1. What are the limitations on low Page Life Expectancy (PLE)?
    2. What are the reasons to cause a low Page Life Expectancy (PLE)?
    3. Where can we find the cause of low Page Life Expectancy (PLE)?

    Right?

    Question 1: What are the limitations on low Page Life Expectancy (PLE)?

    If lots of pages are being overwritten with new data very often, the average PLE will be low. If most pages in our Buffer Pool (BP) remain there for a long time without being overwritten, the average PLE will be high. So if we're doing a big data load, low PLE is ok, but if we're doing read-mostly activities, we will want PLE to be as high as possible.

    Question 2: What are the factors to cause a low Page Life Expectancy (PLE)?

    • Situation 1: The queries being run have changed to look at data that they weren't before, hence we now read in this new data into the BP. Or we're reporting just after a data load of different data, or just a large data load.
    • Situation 2: The queries being run are inefficient, and are reading unnecessary data into the BP which is therefore being constantly turned over.
    • Situation 3: There are large numbers of efficient queries (or a few very large efficient queries) which are saturating the BP.

    Question 3: Where can we find the cause of low Page Life Expectancy (PLE)?

    1. About situation 1, it is normal, but it should be short-lived. we'd expect to see the PLE drop off a cliff, then build back up over the threshold and stay there until the next shift in data requirement. If it drops and stays there, we're probably in situation 2 or 3.
    2. we can find this out by looking at the top queries for physical IO usage and checking out the query plans. If we see lots of scans, there may be some tuning to be done! Since 80% of performance problems come down to poor T-SQL (which may therefore create inefficient Query Plans), consider this before we think we're in situation 3!
    3. About situation 3, we've done our best, the queries are as efficient as they can get, but our PLE is still low. We can either spread out the queries (maybe running Agent Jobs one after the other instead of at the same time), or increase the amount of Buffer Pool memory. NEVER leave less than 1GB free for the OS, 2GB for servers with >64GB RAM, 4GB for servers with >128GB RAM. It's not that it's "free" and never used, the OS will use it and release it in much less than a second - less time than PerfMon or most tools' data gather interval, so it's being used but we don't get to see it. So if our PerfMon counter "Memory:Available Memory in MB" is under these thresholds and we're in situation 3, we need more physical memory so we can increase BP AND keep enough memory for the OS.

    For more details about SQL Server Page Life Expectancy, please refer to the following article: http://blogs.msdn.com/b/mcsukbi/archive/2013/04/12/sql-server-page-life-expectancy.aspx

    If you have any question, please feel free to let me know.

    Regards,
    Jerry Li


    • Edited by Donghui Li Monday, November 3, 2014 8:22 AM
    • Marked as answer by vijay_1234 Monday, November 3, 2014 11:03 AM
    Monday, November 3, 2014 8:22 AM
  • Hi Castelino,

    Thanks for your response.

    Max Memory 80% allocated of total memory to sql.

    Page file : 40 GB


    • Edited by vijay_1234 Monday, November 3, 2014 11:08 AM
    Monday, November 3, 2014 10:37 AM
  • Hi Shanky,

    Thank you for your to spending on my quetion

    Yes ! SQL Server Services Account added to "lock pgaes"

    Monday, November 3, 2014 10:39 AM
  • Hi Tom,

    Yes, When PLE level is low during the time Rebuiling index job is runing

    We changed job schedule time even after same issue coming.

    Monday, November 3, 2014 10:41 AM
  • Hi Tom,

    Yes, When PLE level is low during the time Rebuiling index job is runing

    We changed job schedule time even after same issue coming.

    The problem is with your queries running and index rebuild task you are doing. I asked you to capture perfmon counters you did not, how do you expect us to give correct answer ?

    I am sure you are rebuilding index through maintenance  plan which is bad. Create a custom script which only rebuilds those indexes(having fragmentation >40) which are fragmented not all of it. Reorganize index with fragmentation between 10 and 40.You can also use Ola Hallengern script

    You already have got lot of explanation about PLE

    I have requested you many times don't just post question for sake of doing it if you really have issue come with complete details so that we can help otherwise its just wastage of time


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    • Marked as answer by vijay_1234 Monday, November 3, 2014 11:09 AM
    Monday, November 3, 2014 10:51 AM
    Answerer
  • Hi Jerry,

    Thanks so much for your valid information.

    When i see your valid information, I  have done analysis.

    i shared with our client . so he is  happy.

    Thanks very much

    Monday, November 3, 2014 11:01 AM
  • Yes Shanky,

    Thank you so much.

    All perfmon counters were good. i am sharing major counters.

    Buffer cache hit Ratio :100

    Avalibale Mb bytes : 4 Gb

    remaining counters also good.

    Diks Queue lenth : 3

    Monday, November 3, 2014 11:12 AM