none
SQL server leveraging free memory to fill buffer RRS feed

  • Question

  • I have a strange situation where one of my SQL server 2012 instance refuses to fill the buffer and displays very poor page life expectancy.

    The instance is capped at 55GB.

    when displaying the performance counter from the Memory manage of my instance:

    Total memory flat at 55GB.  (no drop)

    I have an average between 1GB to 2GB of buffer (database cache).  the page life expectancy constantly is below 5 seconds.

    43GB of free memory

    the rest is split between working set, stolen memory, etc. with no noticeable peaks.

    Over two days, the buffer rased once to 40GB, consuming the free memory, for one hour or so outside work hours before lowering once more.

    Wondering kind of situation could cause a SQL server instance keep the memory as "Free Memory" while keeping the buffer low. What could cause and instance to leave 43GB as "Free memory"?


    • Edited by Antoine F Thursday, December 1, 2016 1:48 AM
    Thursday, December 1, 2016 1:40 AM

Answers

  • 11.0.3128.0 

    SP1 

    Enterprise Edition (64-bit) 

    0

    Part of a always on availability group.

    Antonie,

    Always keep in mind that whenever you face something weird with SQL Server memory like in your case where you have enough memory still PLE is plummeting, make sure the SQL server is on latest SP/CU.

    What you are facing is Bug fixed in SQL Server 2012 Sp1 CU4 which causes PLE to fluctuate and plummet.

    Since SQL Server 2012 Sp3 is released I suggest you apply it.

    Let me know if you still face the issue


    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 Antoine F Saturday, December 10, 2016 1:07 PM
    Friday, December 2, 2016 6:25 AM
    Moderator

All replies

  • Can you execute the below query and check the output?

    select
    sum(pages_kb
    + virtual_memory_committed_kb
    + shared_memory_committed_kb
    + awe_allocated_kb)/1024 as [Used by BPool with AWE, MB]
    from
    sys.dm_os_memory_clerks
    where
    type = 'MEMORYCLERK_SQLBUFFERPOOL'

    Thursday, December 1, 2016 2:04 AM
  • We are off hours right now, so the reading might begin to be less representative. 

    here is the result:

    Used by BPool with AWE, MB
    7570

    that 5964 MB of pages and 1875 of virtual memory committed. the next two ones a re 0.

    Thursday, December 1, 2016 2:15 AM
  • Need to clarify few things;

    You're saying Total server memory is flat at 55GB. This means the total memory used by SQL Server Buffer Pool is 55GB. So your SQL Server memory is fully utilized since the allocated memory also 55GB to the SQL Server instance. You also mentioned 43GB is free. What you mean by this? Do you've additional memory available in the machine but not yet allocated to SQL Server? (Assuming you've set max server memory to 55GB in SQL Server)

    In this situation your PLE is 5 seconds, would mean to me that SQL Server is facing lot of IO activities. As a result it can not keep the data pages in memory long time. I would further monitor following counters as well.

    SQL Server:Buffer Manager\Page Life Expectancy
    SQL Server:Buffer Manager\Page reads/sec
    Physical Disk\Disk Reads/sec

    Thursday, December 1, 2016 2:20 AM
  • By buffer, I refer to the database pages kept in memory which would be the "database cache memory" counter of the memory manager object. https://msdn.microsoft.com/en-us/library/ms190924.aspx

    Free Memory (KB) Specifies the amount of committed memory currently not used by the server.
    Database Cache Memory (KB) Specifies the amount of memory the server is currently using for the database pages cache.
    Free Memory (KB) Specifies the amount of committed memory currently not used by the server.
    Total Server Memory (KB) Specifies the amount of memory the server has committed using the memory manager.

    There are multiple instances on this server, I capped this specific instance at 55GB. (from ssms, properties, memory, max...)

    The total server memory counter is flat at 55GB which corresponds to the cap I set for the instance.

    The database cache memory is between 1 GB and 2 GB meaning there is approximately 1GB to 2GB of data kept in memory.  (I believe this is the definition of buffer)

    There is 43GB kept reserved by the instance but displayed under the Free memory counter. 

    If I add up the Connection Memory (KB), Database Cache Memory (KB), Free Memory (KB), Granted Workspace Memory (KB), Lock Memory (KB), Log Pool Memory (KB), Optimizer Memory (KB), Reserved Server Memory (KB), SQL Cache Memory (KB) and Stolen Server Memory (KB), I get to roughly the equivalent of Total Server Memory (KB)

    which is roughly:

    Free memory 43GB

    database cache 1GB

    all the other counters (except total server memory) added together 11GB

    43 + 1 + 11 = 55 which is my counter total server memory which corresponds to what I allocated to my instance.

    the size of the database cache reported by the counter also corresponds to the sum of the page reporded from the sys.dm_os_buffer_descriptors dmv which I extracted using Paul Randall's script found here: http://www.sqlskills.com/blogs/paul/performance-issues-from-wasted-buffer-pool-memory/

    So roughly, my server keeps only 1GB to 2GB of databases pages (data) out of 55GB dedicated and reporting 43 GB as committed but not used.

    • Edited by Antoine F Thursday, December 1, 2016 3:20 AM
    Thursday, December 1, 2016 3:04 AM
  • I will start another data collection for the disk read per sec, buffer page read and page life expectancy.  We are currently off hour so the current data would not be representative...

    The value I outputted for the page life expectancy, below 5 seconds, Is taken from solarwinds app insight for SQL.  I will corroborate against perfmon.

    Still what kind of situation would cause only 2GB to be dedicated to database cache or reported in the sys.dm_os_buffer_descriptors out of 55GB allocated to the instance?

    Thursday, December 1, 2016 3:36 AM
  • I get it. You can run another round of testing and verify the counters again. Assuming the counter values that you mentioned are correct for the instance being troubleshoot, it seems like the system has faced a cold buffer cache situation which is similar to a situation after running DBCC DROPCLEANBUFFERS.
    Thursday, December 1, 2016 4:10 AM
  • Just for sure - could you please send us output of next query?:

    SELECT SERVERPROPERTY('ProductVersion') as [Version]
    	, SERVERPROPERTY('productlevel') as [SP_Level]
    	, SERVERPROPERTY ('edition') as [Edition]
    	, SERVERPROPERTY('IsClustered') as [Clustered];



    • Edited by David KRIZ Thursday, December 1, 2016 3:11 PM
    Thursday, December 1, 2016 3:10 PM
  • 11.0.3128.0 

    SP1 

    Enterprise Edition (64-bit) 

    0

    Part of a always on availability group.

    Friday, December 2, 2016 12:19 AM
  • 11.0.3128.0 

    SP1 

    Enterprise Edition (64-bit) 

    0

    Part of a always on availability group.

    Antonie,

    Always keep in mind that whenever you face something weird with SQL Server memory like in your case where you have enough memory still PLE is plummeting, make sure the SQL server is on latest SP/CU.

    What you are facing is Bug fixed in SQL Server 2012 Sp1 CU4 which causes PLE to fluctuate and plummet.

    Since SQL Server 2012 Sp3 is released I suggest you apply it.

    Let me know if you still face the issue


    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 Antoine F Saturday, December 10, 2016 1:07 PM
    Friday, December 2, 2016 6:25 AM
    Moderator
  • Thanks a lot, I should have considered the possibility of a bug.

    One thing I wonder, since my setup uses always on, is the sp update considered a version update. i.e will I end up in a situation where I cannot read from the secondary replica, data movement will be paused with the impossibility to failback to the original instance until all the replicas are upgraded?

    I also wonder if I should I modify the routing defined on my listener if the secondary replica would not be readable or would be automatically handled? I am refering to the impacts listed here: https://msdn.microsoft.com/en-us/library/dn178483.aspx


    • Edited by Antoine F Wednesday, December 7, 2016 4:02 AM
    Wednesday, December 7, 2016 3:57 AM
  • Thanks a lot, I should have considered the possibility of a bug.

    One thing I wonder, since my setup uses always on, is the sp update considered a version update. i.e will I end up in a situation where I cannot read from the secondary replica, data movement will be paused with the impossibility to failback to the original instance until all the replicas are upgraded?

    I also wonder if I should I modify the routing defined on my listener if the secondary replica would not be readable or would be automatically handled? I am refering to the impacts listed here: https://msdn.microsoft.com/en-us/library/dn178483.aspx


    I created a separate post for this question.  Thank you very much Shashank, Suthana and David.
    Saturday, December 10, 2016 1:09 PM