none
Why is my cache clearing? I get low PLE RRS feed

  • Question

  • Monitoring the system today returned the following:

    In our production server we have 32GB of RAM

    SQL uses 26-27GB

    6 GB (static) of that are "stolen" – used for other stuff that are not database caching or managing.

    0.5GB (static) are used for locking.

    All night long the free memory was just below 2GB and that is good. It means we have enough memory for all databases.

    At 7:45 AM the free memory grew to 10GB and at 8:00 to 16GB. We don’t need this memory free, we need it for cache.

    The cache that was all night at 20GB dropped to 11 GB and during the day varied to the lowest level of 4GB.

    The bright green line is our goal, "Page Life expectancy". With hour memory it should be days, but it is now averaging about a minute and a half!

    Free memory is not a need of the system so it is strange the cache is being depleted by it.

    sould I sample data in a higher rate, maybe something else is stealing the cache and returning free memory very quickly.

    what could be the issue?


    • Edited by oky111 Sunday, November 23, 2014 10:03 AM
    Sunday, November 23, 2014 9:56 AM

Answers

  • Hi,

    First thing I would ask you to parallely start a plan to upgrade it to SP2. Do it ASAP

    You have set max server memory to 27 and SQL is using 26 G which is pretty much normal. Now I asked about what all processes run/was running when PLE dropped you did not tell about this.

    Does this happen frequently or it was just one day.

    First think you should do is provide SQL Server service account Locked pages in memory privilege.

    Can you post SQL Server errorlog at time when PLE dropped. Can you look for any paging information in errorlog.

    How is your hypervisor configured ?


    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 oky111 Tuesday, November 25, 2014 12:25 PM
    Sunday, November 23, 2014 3:18 PM
    Moderator
  • Shanky, I will mark your answer to upgrade to SP2 soon.

    For the future use of users running into this issue here is a nice graph of the problem (hold CTRL+roll mouse to enlarge):

    The dark green line is the lazy writer.

    When it wakes up, it raises the free memory (light blue) and decreases the database cache (Blue).

    This causes the PLE (light green) to drop to 0.

    • Marked as answer by oky111 Tuesday, November 25, 2014 12:26 PM
    Tuesday, November 25, 2014 12:25 PM

All replies

  • Issues can be many I would first look at what is going on in SQL Server which is depleting PLE. Some big ETL job running which scans huge table ? Please note carefully what all is running.

    Please post output of following

    select
    (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB,
    (locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB,
    (total_virtual_address_space_kb/1024 )Total_VAS_in_MB,
    process_physical_memory_low,
    process_virtual_memory_low
    from sys. dm_os_process_memory
    

    And

    Select @@Version

    Is this server dedicated to just SQL Server DB engine or there are SSIS,SSRS,SSAS running ?

    Are there multiple instances 

    What is MAX server memory ?

    Please answer all questions


    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

    Sunday, November 23, 2014 11:12 AM
    Moderator
  • Thanks Shanky,

    here are the results:

    Memory_usedby_Sqlserver_MB Locked_pages_used_Sqlserver_MB Total_VAS_in_MB      process_physical_memory_low process_virtual_memory_low

    -------------------------- ------------------------------ -------------------- --------------------------- --------------------------

    26904                      0                              8388607              0                           0

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64)

    Jul 22 2014 15:26:36

    Copyright (c) Microsoft Corporation

    Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

     

    It is dedicated to data only (no ssis,ssrs,ssas)

    One instance.

    max memory 27GB

    Should  I change minimum memory from 0 to 20GB?

    I can cause real memory preasure by reading all transactions. I even managed to produce:

    An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

    But not here is my problem. My issue is when there is pleanty free memory and cache gradually reduces.

    Sunday, November 23, 2014 11:56 AM
  • Hi,

    First thing I would ask you to parallely start a plan to upgrade it to SP2. Do it ASAP

    You have set max server memory to 27 and SQL is using 26 G which is pretty much normal. Now I asked about what all processes run/was running when PLE dropped you did not tell about this.

    Does this happen frequently or it was just one day.

    First think you should do is provide SQL Server service account Locked pages in memory privilege.

    Can you post SQL Server errorlog at time when PLE dropped. Can you look for any paging information in errorlog.

    How is your hypervisor configured ?


    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 oky111 Tuesday, November 25, 2014 12:25 PM
    Sunday, November 23, 2014 3:18 PM
    Moderator
  • PLE and also simply cache normaly are low.

    when I raise them myself by running a query that consumes many pages, I see I can use more cache. Then I see that the Cache gradually drops. So there is no specific proccess running I can actually point out for you.

    No other SQL issue or error occures, so I would guess it is more like file cache consuming the memory. but I see the memory is inside SQL as Free memory (am I wrong with this assumption?).

    It seems this is the situation for a long time. The application is not great and I managed to tune it up a bit ( As far as I can being a DBA and not programmer), but probably this was allways the situation.

    No errors when PLE drops. Just some replication connection problems a few times a day.

    Hypervisor is a VMWARE SERVER. If you have more specific questions there please frase them in a manor I can ask my administrator.

    Sunday, November 23, 2014 3:41 PM
  • I am not quite sure you are actually facing issue mentioned in below blog, but the way PLE drops with almost no message in errorlog makes me believe its kind of bug

    http://blogs.msdn.com/b/axinthefield/archive/2013/10/22/severe-performance-issue-in-sql-2012-with-service-pack-1-page-life-expectancy-drops-near-0.aspx

    As already suggested before please Apply SQL Server 2012 SP2 ASAP because fix for this issue was fixed in SQL Server 2012 SP1 CU4. Please read below support article

    http://support.microsoft.com/kb/2845380

    PS: SP is cumulative so SP2 will include all fixes for SP1 and CU's released after it (including CU4 fix)


    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



    Sunday, November 23, 2014 4:25 PM
    Moderator
  • Thanks,

    Looks promising. I will update in a few days, when they let me do the upgrade to SP2.

    I will avoid at the moment from doing the other tweeks, like minimum memory and lock pages in memory.

    Sunday, November 23, 2014 4:58 PM
  • I will avoid at the moment from doing the other tweeks, like minimum memory and lock pages in memory.

    I would say providing locked pages in memory is not a bad idea at all but before that leave 5-6 G for OS. This would not allow any other rouge process to take SQL Server memory

    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

    Sunday, November 23, 2014 6:56 PM
    Moderator
  • I can cause real memory preasure by reading all transactions. I even managed to produce:

    An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.But not here is my problem. My issue is when there is pleanty free memory and cache gradually reduces.

    But that message is not due to memory pressure in SQL Server, but memory pressure in SQL Server Management Studio.

    Then again, if you ran SSMS on the machine where the server is, you did infringe on the amount of memory available to SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, November 23, 2014 8:17 PM
  • An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

    But that message is not due to memory pressure in SQL Server, but memory pressure in SQL Server Management Studio.

    Then again, if you ran SSMS on the machine where the server is, you did infringe on the amount of memory available to SQL Server.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    I used SSMS on my station. it seems I didn't restart for a while.
    Monday, November 24, 2014 8:45 AM
  • Shanky, I will mark your answer to upgrade to SP2 soon.

    For the future use of users running into this issue here is a nice graph of the problem (hold CTRL+roll mouse to enlarge):

    The dark green line is the lazy writer.

    When it wakes up, it raises the free memory (light blue) and decreases the database cache (Blue).

    This causes the PLE (light green) to drop to 0.

    • Marked as answer by oky111 Tuesday, November 25, 2014 12:26 PM
    Tuesday, November 25, 2014 12:25 PM