none
SQL Engine releasing memory when there is free memory available.

    Question

  • All,

    We had a situation on one of our production servers today. The max memory is set to 110 GB. At a certain point the SQL Process started to release memory and got all the way down to 50 GB. At this point it started to have performance problems for the application hooked up to it. IT also threw an error like this in the log: AppDomain 6 (mssqlsystemresource.dbo[runtime].5) is marked for unload due to memory pressure.

    During this period of time, about an hour the CPU level went very high. IT stayed high as the memory consumption gradually grew again. I eventually cleared the procedure cache and the CPU came down.

    During this period of time there was not memory pressure from any other applicaiton -- the free memory was increasing as the amount of memory sql was using decreased. 

    I have checked windows event logs and sql errorlogs and find nothing.

    Has anyone seen this before? Any ideas on why this happened?


    Thanks

    John Tracy


    John

    Monday, September 16, 2013 1:34 PM

Answers

  • Well.. There you go.. On x64 it is a best practice to set this. Even though max memory is set to 110GB OS can still force SQL server to trim the memory up on memory starvation.

    make sure to set this option..

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Marked as answer by John Tracy Monday, September 16, 2013 3:02 PM
    Monday, September 16, 2013 1:57 PM
  • Ok.  Exactly how are you measuring that "SQL Process started to release memory and got all the way down to 50 GB"?

    You need to distinguish between two different possibilities.

    1) SQL Server reduced memory usage itself, like when it receives a memory pressure signal from Windows.

    2) Windows reduced the amount of SQL Server's Virtual Address Space that's mapped to physical RAM (ie SQL was getting "paged out"). 

    If you had LPIM enabled, possibility 2) couldn't happen.

    David


    David http://blogs.msdn.com/b/dbrowne/



    Monday, September 16, 2013 2:07 PM
  • John,

    Based on this graph and knowing that LPIM is not set, I'm inclined to agree with David. It seems as though some other application on the server (or windows process) sent a low memory signal and trimmed your working set. This seems to coincide with the CPU which I would assume from the amount of paging done (my guess would be kernel mode was high).

    -Sean


    Sean Gallardy | Blog | Twitter

    • Marked as answer by John Tracy Monday, September 16, 2013 3:02 PM
    Monday, September 16, 2013 2:28 PM
    Answerer
  • We just determined this was the read ahead cache because of a large file copy that was happening.  LPIM would have prevented this.

    John


    • Marked as answer by John Tracy Monday, September 16, 2013 3:55 PM
    • Edited by John Tracy Monday, September 16, 2013 3:55 PM
    Monday, September 16, 2013 3:55 PM

All replies

  • You might have plan cache bloat

    Do you deal with lots of ad-hoc SQL?

    SELECT objtype AS [CacheType]
            , count_big(*) AS [Total Plans]
            , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
            , avg(usecounts) AS [Avg Use Count]
            , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
            , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
    FROM sys.dm_exec_cached_plans
    GROUP BY objtype
    ORDER BY [Total MBs - USE Count 1] DESC
    go
    
    

    Monday, September 16, 2013 1:37 PM
  • you are using SQL Server 32 bit or 64 bit,

    64 bit SQL Server will never releases memory


    Ramesh Babu Vavilla MCTS,MSBI

    Monday, September 16, 2013 1:39 PM
  • 64 bit SQL 2008 SP3 Enterprise Edition.  Yes i have never seen it release memory either.

    John

    Monday, September 16, 2013 1:40 PM
  • CacheType Total Plans Total MBs Avg Use Count Total MBs - USE Count 1 Total Plans - USE Count 1
    Prepared 24201 4085.765625 545 1644.843750 10113
    Adhoc 134810 2671.132812 178 1402.742187 59358
    Proc 205 101.273437 4574 11.875000 35
    Check 13 0.289062 3 0.078125 4
    View 466 90.937500 314 0.000000 0
    UsrTab 1 0.218750 2 0.000000 0

    This is with only less than a day after a fail over to the other node in the cluster.

    How does Cache Plan Bloat result in memory being released?


    John

    Monday, September 16, 2013 1:42 PM
  • Do you have LPIM(lock page in memory) enabled.

    VT


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, September 16, 2013 1:48 PM
  • No LPIM is not enabled.

    John

    Monday, September 16, 2013 1:52 PM
  • Well.. There you go.. On x64 it is a best practice to set this. Even though max memory is set to 110GB OS can still force SQL server to trim the memory up on memory starvation.

    make sure to set this option..

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Marked as answer by John Tracy Monday, September 16, 2013 3:02 PM
    Monday, September 16, 2013 1:57 PM
  • But there was no memory starvation!  There was as much free memory as sql was releasing.  If the memory was being used I would agree with you, but the memory was not being used by anything else.

    John

    Monday, September 16, 2013 1:58 PM
  • How do you know that there were no memory starvation when this happened. Point is SQL will not release memory unless its been told to release memory by OS

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, September 16, 2013 2:01 PM
  • We have monitoring on this box, and the amount of free memory reported went up as the amount of memory sql was using went down.  Also there was no message in the errorlog about paging out any part of the sql process.

    John


    • Edited by John Tracy Monday, September 16, 2013 2:06 PM
    Monday, September 16, 2013 2:02 PM
  • Ok.  Exactly how are you measuring that "SQL Process started to release memory and got all the way down to 50 GB"?

    You need to distinguish between two different possibilities.

    1) SQL Server reduced memory usage itself, like when it receives a memory pressure signal from Windows.

    2) Windows reduced the amount of SQL Server's Virtual Address Space that's mapped to physical RAM (ie SQL was getting "paged out"). 

    If you had LPIM enabled, possibility 2) couldn't happen.

    David


    David http://blogs.msdn.com/b/dbrowne/



    Monday, September 16, 2013 2:07 PM
  • change the max server memory setting

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/d5c8f848-260e-4f88-8d97-2a0b06363a39/appdomain-6-dboruntime5-is-marked-for-unload-due-to-memory-pressure

    Monday, September 16, 2013 2:08 PM
  • Ignore by previous posts.. this Appdomain.. do you have any CLR procedure running

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker


    Monday, September 16, 2013 2:12 PM
  • David,

    The total memory used on the box was down to 50 GB as reporting by monitoring.  (Total RAM used).  I don't know how to determine which of the two happened to cause this.  I do know there were not messages in the errorlog about sql process getting paged out.


    John


    • Edited by John Tracy Monday, September 16, 2013 2:15 PM
    Monday, September 16, 2013 2:13 PM
  • VT,

    NO CLR procedures running.


    John

    Monday, September 16, 2013 2:14 PM
  • what was the commands that you used for clearing the cache?

    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Monday, September 16, 2013 2:17 PM
  • Rama:  
    DBCC FREEPROCCACHE

    John

    Monday, September 16, 2013 2:19 PM
  • Here are the memory and CPU graphs for the period of time in question:


    John

    Monday, September 16, 2013 2:23 PM

  • John

    Monday, September 16, 2013 2:23 PM
  • Could you please run the code bellow and post the details (please use code block window)

    SELECT CAST(dorb.record AS XML) AS xRecord,
    
    dorb.timestamp
    
    FROM sys.dm_os_ring_buffers AS dorb
    
    WHERE dorb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Monday, September 16, 2013 2:27 PM
  • John,

    Based on this graph and knowing that LPIM is not set, I'm inclined to agree with David. It seems as though some other application on the server (or windows process) sent a low memory signal and trimmed your working set. This seems to coincide with the CPU which I would assume from the amount of paging done (my guess would be kernel mode was high).

    -Sean


    Sean Gallardy | Blog | Twitter

    • Marked as answer by John Tracy Monday, September 16, 2013 3:02 PM
    Monday, September 16, 2013 2:28 PM
    Answerer
  • Hello,

    >>AppDomain 6 (mssqlsystemresource.dbo[runtime].5) is marked for unload due to memory pressure.
    This error comes when CLR is used and when it comes on 64 bit system, it points out to fact that there is not sufficient memory in non buffer pool to load a SQLCLR (MTL)may be MAX server memory is not set to optimum value.Please check again there must be some SQLCLR running.

    What is total memory (physical ram) present on windows box.SQLCLR is not able to get complete memory it requires.

    Use this link to configure it properly.

    http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

    and

    http://mssqlwiki.com/2013/04/22/max-server-memory-do-i-need-to-configure/

    Edit:Agree please grant SQl server service LPIM privilege.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    • Edited by Shanky_621 Monday, September 16, 2013 2:48 PM
    Monday, September 16, 2013 2:31 PM
  • is there OS under pressure during that time ,looks like SQL paging the data to VIrtual memory on the disk level,might be locked pages need to enable Also during that time needed an dbcc memorystatus outcome result when it was high and during down.


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Monday, September 16, 2013 2:33 PM
  • <Record id="0" type="RING_BUFFER_RESOURCE_MONITOR" time="2490730646">
      <ResourceMonitor>
        <Notification>RESOURCE_MEMPHYSICAL_HIGH</Notification>
        <IndicatorsProcess>0</IndicatorsProcess>
        <IndicatorsSystem>1</IndicatorsSystem>
        <NodeId>1</NodeId>
        <Effect type="APPLY_LOWPM" state="EFFECT_OFF" reversed="0">0</Effect>
        <Effect type="APPLY_HIGHPM" state="EFFECT_ON" reversed="0">0</Effect>
        <Effect type="REVERT_HIGHPM" state="EFFECT_OFF" reversed="0">0</Effect>
      </ResourceMonitor>
      <MemoryNode id="1">
        <ReservedMemory>134911940</ReservedMemory>
        <CommittedMemory>690044</CommittedMemory>
        <SharedMemory>0</SharedMemory>
        <AWEMemory>0</AWEMemory>
        <SinglePagesMemory>10720</SinglePagesMemory>
        <MultiplePagesMemory>20656</MultiplePagesMemory>
      </MemoryNode>
      <MemoryRecord>
        <MemoryUtilization>100</MemoryUtilization>
        <TotalPhysicalMemory>134206372</TotalPhysicalMemory>
        <AvailablePhysicalMemory>124879656</AvailablePhysicalMemory>
        <TotalPageFile>136456108</TotalPageFile>
        <AvailablePageFile>130550632</AvailablePageFile>
        <TotalVirtualAddressSpace>8589934464</TotalVirtualAddressSpace>
        <AvailableVirtualAddressSpace>8454601104</AvailableVirtualAddressSpace>
        <AvailableExtendedVirtualAddressSpace>0</AvailableExtendedVirtualAddressSpace>
      </MemoryRecord>
    </Record>


    John

    Monday, September 16, 2013 2:35 PM
  • Shanky,

    FYI - on 64-bit windows machines, the virtual address space is 8TB and AFAIK Windows Server 2012 max is 4TB physical ram. VAS is generally an issue with 32-bit machines and only having 2GB of user mode addressable space.

    -Sean


    Sean Gallardy | Blog | Twitter

    Monday, September 16, 2013 2:35 PM
    Answerer
  • John,

    Unfortunately this is the latest entry in the ring buffer and will not show the information from the time period when the issue occurred. If this happens again please run the query and post back the results.

    -Sean


    Sean Gallardy | Blog | Twitter

    Monday, September 16, 2013 2:45 PM
    Answerer
  • Shanky,

    FYI - on 64-bit windows machines, the virtual address space is 8TB and AFAIK Windows Server 2012 max is 4TB physical ram. VAS is generally an issue with 32-bit machines and only having 2GB of user mode addressable space.

    -Sean


    Sean Gallardy | Blog | Twitter


    Ok ,ya you are right ..missed that point . But if feel here that MAX server meory is not set to proper value and SQLCLR memory allocations are from non buffer pool i.e MTL so max server memory set too high can also be cause.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, September 16, 2013 2:45 PM
  • Here is the main troubleshooting guide for this: How to reduce paging of buffer pool memory in the 64-bit version of SQL Server

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, September 16, 2013 3:00 PM
  • I guess based upon this feedback we will set LIPM to keep this from happening.

    Thanks for all your feedback!!


    John

    Monday, September 16, 2013 3:03 PM
  • John,

    LPIM (as David pointed out) will stop SQL Server from being paged out. If there is a misbehaving (or max memory is set too high) application or windows really does need memory, it could have detrimental effects on windows itself. If you find the issues continuing (such as memory pressure indicated by windows) then I would start looking for 3rd party applications or set the max server memory lower.

    Just posting this as something to keep an eye out for, not necessarily something you will run into.

    -Sean


    Sean Gallardy | Blog | Twitter

    Monday, September 16, 2013 3:09 PM
    Answerer
  • We just determined this was the read ahead cache because of a large file copy that was happening.  LPIM would have prevented this.

    John


    • Marked as answer by John Tracy Monday, September 16, 2013 3:55 PM
    • Edited by John Tracy Monday, September 16, 2013 3:55 PM
    Monday, September 16, 2013 3:55 PM