none
page life cycle Expectancy is coming less than 300 in my SQL server do we required to increase SQL server memory(RAM) RRS feed

  • Question

  • HI All,

    i am new in SQL Server, i have sen  some blog people said if page life cycle Expectancy  less than 300 then we need to increase RAM.

    or i need to check some other parameter that give hint to increase RAM.

    currently i have  8GB RAM in My system and 4 GB assign to SQL Server.

    in my SQL Server instance i have  6 DB use for Application.

    Regards,

    Manish

    Wednesday, August 3, 2016 12:31 PM

Answers

  • Thanks all again,

    I have performance problem. i have 3 DB inside instance of SQL Server 2012. is there any way no where is problem.

    Regards,

    Manish

    I suggest you also proofread your question specially grammar, it makes difficult to understand your question. As of now I see you are worried about PLE.

    1. As I said before PLE 300 mark is useless. To get approx value of PLE on your system you should calculate like (Memory assigned to SQL Server * 300)/4 which is 600 in your case. So for good case PLE on system should be around 600 if it goes below 600 and remains continuously below 600 then you might have memory pressure.

    2. To gauge memory pressure you have to reply on perfmon counters and you should bee below counters

    SQL Server: Buffer Manager: Database Pages

    Page Life Expectancy

    SQL Server: Memory Manager- Free Memory (KB)

    SQL Server: Memory Manager--Database Cache Memory (KB)

    SQL Server:Buffer Manager--Free Pages

    SQL Server:Buffer Manager--Free List Stall/sec

    SQL Server: Memory Manager-- Target Server Memory (KB)

    SQL Server: Memory Manager--Total Server Memory (KB)

    Fire A data collector set

    and record the counters for period of 8 hours when load is maximum on system


    Cheers,

    Shashank

    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 Articles

    MVP

    • Marked as answer by manishcal16PPS Thursday, August 4, 2016 11:03 AM
    Wednesday, August 3, 2016 5:20 PM
    Moderator
  • This is not what I asked for, and what is PLE 4 ? I again reiterate please proof read your question or take buddy help to proofread it. This is  unnecessarily making thread big.

    The snapshot value is not required and I guess nothing can be derived from it. We need cumulative value for 8 hours to actually reach to a conclusion. I suggest you read comments clearly and follow them


    Cheers,

    Shashank

    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 Articles

    MVP


    Thursday, August 4, 2016 9:07 AM
    Moderator

All replies

  • Please read this article from Paul Randall:

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


    Please mark the answer as solved and vote as helpful to help others

    Wednesday, August 3, 2016 12:35 PM
  • Hello manishcal

    Please assign 80% memory to SQL and then monitor.

    below Script use to set Max Server memory

    sp_configure 'show advanced options', 1  
    GO  
    RECONFIGURE  
    GO  
    sp_configure 'max server memory', 6553
    GO  
    RECONFIGURE
    GO 

    below is the link you can refer.

    Fixing Page Life Expectancy (PLE)

    Please Mark me as answer if my post helps you

    Regards

    ChetanV


    Wednesday, August 3, 2016 12:51 PM
  • HI All,

    i am new in SQL Server, i have sen  some blog people said if page life cycle Expectancy  less than 300 then we need to increase RAM.

    or i need to check some other parameter that give hint to increase RAM.

    currently i have  8GB RAM in My system and 4 GB assign to SQL Server.

    in my SQL Server instance i have  6 DB use for Application.


    I cannot understand your question clearly, what are you asking ?

    Just forgot about 300 figure it was very very old recommendation. For example even if you have PLE of 400 for some systems  it can be symbol of memory pressure. PLE is calculated is different way.


    Cheers,

    Shashank

    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 Articles

    MVP


    Wednesday, August 3, 2016 12:53 PM
    Moderator
  • 4GB to SQL Server is very small amount of memory given to, definitely you need to increase memory

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Proposed as answer by Enric Vives Wednesday, August 3, 2016 1:13 PM
    Wednesday, August 3, 2016 12:55 PM
    Answerer
  • Hello,

    Before adding more RAM to the server take a look at the value of scans/sec on that server using Performance Monitor. Missing indexes, outdated statistics and fragmented indexes can increase the amount of scans and the use of memory.

    Identifying queries requesting memory grants or needing a lot of memory is the next step. Optimize those queries if possible.

    After that, I suggest you collect the PLE counter again based on the following article this time.

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


    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com


    Wednesday, August 3, 2016 1:17 PM
    Moderator
  • Hi All,

    I have seen the article in this discussion

    https://simplesqlserver.com/2013/08/19/fixing-page-life-expectancy-ple/

    They suggest some point to fix the PLE issue. So i need to fix these issue first then only demanding ask for additional RAM.

    Or how can we calculate PLE for my production server so I decide I required additional memory

    Regards,

    Manish

    Wednesday, August 3, 2016 1:42 PM
  • Well, here's the first question: is your system running fast enough?

    If it is, then don't worry, be happy!

    That said - RAM is crazy cheap these days, and SQL Server runs much, much better when you can load pretty much your whole database into buffers.  How large are your databases?  You should have at least that much RAM.

    Also, when PLE is low, what that says is that you don't have enough RAM for the *code* you are running, too.  It may be you can pop in a missing index and boom, your PLE goes sky-high.  I just say that because you can go and upgrade your RAM to a terabyte and still have low PLE and then don't blame me, blame your code. But good code and lots of RAM go really well together.

    Josh


    • Edited by JRStern Wednesday, August 3, 2016 2:26 PM
    Wednesday, August 3, 2016 2:24 PM
  • Thanks all again,

    I have performance problem. i have 3 DB inside instance of SQL Server 2012. is there any way no where is problem.

    Regards,

    Manish

    Wednesday, August 3, 2016 3:02 PM
  • The PLE likely has nothing to do with your performance problem.  It is completely normal and expected behavior for PLE to drop below 300 for a period of time after index maintenance.

    What exactly is your symptom?  Also, please post the results of SELECT @@VERSION.

    You may have a blocking issue or an issue easily resolved by adding an index, or index maintenance.

    Wednesday, August 3, 2016 4:28 PM
    Moderator
  • Thanks all again,

    I have performance problem. i have 3 DB inside instance of SQL Server 2012. is there any way no where is problem.

    Regards,

    Manish

    I suggest you also proofread your question specially grammar, it makes difficult to understand your question. As of now I see you are worried about PLE.

    1. As I said before PLE 300 mark is useless. To get approx value of PLE on your system you should calculate like (Memory assigned to SQL Server * 300)/4 which is 600 in your case. So for good case PLE on system should be around 600 if it goes below 600 and remains continuously below 600 then you might have memory pressure.

    2. To gauge memory pressure you have to reply on perfmon counters and you should bee below counters

    SQL Server: Buffer Manager: Database Pages

    Page Life Expectancy

    SQL Server: Memory Manager- Free Memory (KB)

    SQL Server: Memory Manager--Database Cache Memory (KB)

    SQL Server:Buffer Manager--Free Pages

    SQL Server:Buffer Manager--Free List Stall/sec

    SQL Server: Memory Manager-- Target Server Memory (KB)

    SQL Server: Memory Manager--Total Server Memory (KB)

    Fire A data collector set

    and record the counters for period of 8 hours when load is maximum on system


    Cheers,

    Shashank

    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 Articles

    MVP

    • Marked as answer by manishcal16PPS Thursday, August 4, 2016 11:03 AM
    Wednesday, August 3, 2016 5:20 PM
    Moderator
  • HI.

    i got this matrix from production, can any body tell me any problem

    PLE i have -4

    Regards,

    Manish

    Thursday, August 4, 2016 8:31 AM
  • This is not what I asked for, and what is PLE 4 ? I again reiterate please proof read your question or take buddy help to proofread it. This is  unnecessarily making thread big.

    The snapshot value is not required and I guess nothing can be derived from it. We need cumulative value for 8 hours to actually reach to a conclusion. I suggest you read comments clearly and follow them


    Cheers,

    Shashank

    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 Articles

    MVP


    Thursday, August 4, 2016 9:07 AM
    Moderator
  • Thanks a lot.

    i have taken this data for one day (Temp DB restart before one day).

    Any way if this matrix will not help i will follow your information given in

    To get approx value of PLE on your system you should calculate like (Memory assigned to SQL Server * 300)/4 which is 600 in your case. So for good case PLE on system should be around 600 if it goes below 600 and remains continuously below 600 then you might have memory pressure.

    Regards

    Manish

    Thursday, August 4, 2016 11:03 AM

  • To get approx value of PLE on your system you should calculate like (Memory assigned to SQL Server * 300)/4 which is 600 in your case. So for good case PLE on system should be around 600 if it goes below 600 and remains continuously below 600 then you might have memory pressure.

    Regards

    Manish

    Sometimes when there is lot of I/O activity probably due to bad query PLE can fluctuate but in that case instead of focusing on memory you should tune the query. PLE should never be lone parameter in deciding memory pressure you must also use other perfmon counters I have listed. If you get the data I will tell you what actually is happening.

    Plus 4 G is really less memory I suggest you increase SQL Server memory because on long run when data increases this would eventually come to lack of memory


    Cheers,

    Shashank

    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 Articles

    MVP

    Thursday, August 4, 2016 11:11 AM
    Moderator