CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

How It Works: What are the RING_BUFFER_RESOURCE_MONITOR telling me?

How It Works: What are the RING_BUFFER_RESOURCE_MONITOR telling me?

17 Sep 2009 10:08 AM
  • Comments 1

The ring buffer records (which can be sent to XEvent) for Resource Monitor and Memory Broker are the key aspects to understanding RM.  The record is produced when a change is detected in state monitored by RM.   

CREATE EVENT SESSION RingBufferInfo

ON SERVER

    ADD EVENT sqlos.resource_monitor_ring_buffer_recorded,

    ADD EVENT sqlos.memory_broker_ring_buffer_recorded

    ADD TARGET package0.asynchronous_file_target

       (SET filename = N'E:\XEvent\RingBuffer.etx', metadatafile = N'E:\XEvent\RingBuffer.mta',

                        max_file_size = 50, max_rollover_files = 10)

    WITH (MAX_MEMORY=4MB, MAX_EVENT_SIZE=4MB,STARTUP_STATE = ON);

Allow me to breakdown the following record.    ( select * from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'  )

<ResourceMonitor>

<Notification>RESOURCE_MEMPHYSICAL_LOW</Notification>  <---------------- Current notification state being broadcast to clerks

<IndicatorsProcess>2</IndicatorsProcess>   <-----------------------   Indicator applies to the process as low physical memory

<IndicatorsSystem>0</IndicatorsSystem> <-----------------------  0 means it is NOT a system wide indicator situation

<NodeId>0</NodeId>

<Effect type="APPLY_LOWPM" state="EFFECT_OFF" reversed="0">0</Effect>

<Effect type="APPLY_HIGHPM" state="EFFECT_IGNORE" reversed="0">128163281</Effect>

<Effect type="REVERT_HIGHPM" state="EFFECT_OFF" reversed="0">0</Effect>

</ResourceMonitor>  

The RM record has 3 major components showing various memory details.  

                The <RESOURCEMONITOR> portion of the record is handled from the local resource monitor.   You have a RM per scheduling node. 
                The <MEMORYNODE> details are from the memory node association or the RM.

                The <MEMORYRECORD> comes from global state information.  

The key for understanding why RM is running is in the ResourceMonitor section of the output.   This can be broken down into the Notification, Indicators and Effects.  

Notification

Considered the broadcasted notification state.       

RESOURCE_MEMPHYSICAL_HIGH - SQL can grow memory usage
RESOURCE_MEMPHYSICAL_LOW - System or internal physical memory - shrink
RESOURCE_MEM_STEADY 
RESOURCE_MEMVIRTUAL_LOW  –  Virtual address range for SQL Server process is becoming exhausted.   Commonly the largest free block is less than 4MB.

IndicatorsProcess

Process wide indicator using an |= of the following values 

IDX_MEMPHYSICAL_HIGH = 1
IDX_MEMPHYSICAL_LOW = 2
IDX_MEMVIRTUALL_LOW = 4

IndicatorsSystem

System wide indicator an |= of the following values

IDX_MEMPHYSICAL_HIGH = 1
IDX_MEMPHYSICAL_LOW = 2
IDX_MEMVIRTUALL_LOW = 4

It is considered a system indicator if the query routine returns TRUE.   SQL Server listens to the Windows physical memory notifications so it can be signaled when physical memory becomes low or available.


This state is often the windows memory notifications unless an override occurs because of the the EFFECT information.

Effect

Currently 3 types of effects exist so a row for each is produced.

 

Type = indicator type

State = current effect state  (ON, OFF or IGNORE are valid states)

Reserved= this maps to an applied state that toggles from 0 or 1 based on if the effect has been applied.  Applied indicates that the memory state has broadcast and we have achieved somewhat of a steady state for this indicator.  

Value = duration that the effect has been in the reported state.

NodeId

Memory Node association of the RM. 

 

The notification is the current RM state.  For example, once RM detects LOW PM state it is going to execute and attempt to shrink caches and other memory users.     

The indicators tell you if this is an internal or external condition.   System wide indicates the system had a hand in the indication and process means it was something internal that SQL Server detected.  

The Effects represent the state SQL thinks it is in by looking at the working set size and basic memory targets.   The EFFECT logic is used during system level checks.   For example the RM can look at the physical memory and it shows high but the effects indicate Windows paged SQL Server out so the high should be ignored so we don’t consume more memory and cause more paging.    The effects logic can be disabled using a startup trace flag –T8020 to avoid honoring the memory effects.   This might be an option on the servers to narrow down what can trigger RM to execute as long as the working sets of the SQL Server instances are running steady with the target memory but it should only be used for troubleshooting purposes.  

From the ring buffer example is stating that this is not a system wide issue but a process issue.   The effects are off or ignore so it is not a working set type of issue so what turns this on to low physical?  

There are memory broker decisions that come into play at this time.   What the information is is saying is that a cache or the cache’s predicted usage will exceed internal targets and we need to start doing cache cleanup.    By looking at the memory broker, ring buffer entries, you can see the behavior that broker it asking of RM.   Using the clock hands you can see the internal and external hand movement.   This will help you determine which caches are involved.    The dbcc memorystatus() show good details and last notification states as well.  

When the SHRINK appears this is when it could tell RM to help out and result in RM showing the ring buffer above of LOW PM.  

select * from sys.dm_os_memory_cache_clock_hands

 

MEMORYBROKER_FOR_CACHE (default)         Pages

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

Allocations                              1778

Rate                                     0

Target Allocations                       460798

Future Allocations                       0

Overall                                  482733

Last Notification                        1  ß---------------------------  Memory Broker NOTIFICATION

        ACTIONS (STABLE) = 0
        ACTIONS (GROW) = 1
        ACTIONS (SHRINK) = 2

 Bob Dorr - Principal SQL Server Escalation Engineer

, , , , ,
Leave a Comment
  • Please add 8 and 8 and type the answer here:
  • Post
Comments
  • Bob Dorr
    10 Feb 2010 7:33 AM

    From: Robert Dorr

    Sent: Wednesday, February 10, 2010 9:31 AM

    Subject: RE: Help Understanding Ring Buffer Notifications

    This is a good ring buffer to start with but generally you need the MEMORY BROKER entries as well to tie it all together.

    Blog was based on SQL 2008 and when I looked at the code for SQL 2005 I see the Indicators and it is not broken out like system and process.

    indicators_bits

           INDICATOR_MEMPHYSICAL_HIGH            = 1

           INDICATOR_MEMPHYSICAL_LOW             = 2

           INDICATOR_MEMVIRTUAL_LOW              = 4

    In your case it is a memory physical low indication.   However, there is not an exposed system or process level on SQL 2005.

    In this case the Effects are OFF so it is not a working set issue so you then have to look at the memory broker entries to see if a SHRINK was indicated which would be a process indicator versus the system indication.

    Sent: Wednesday, February 10, 2010 8:53 AM

    Subject: Help Understanding Ring Buffer Notifications

    I was at a customer site recently and I collected some ring buffer info.  It seems like the server is low on memory so I collected some ring buffers to see if I can find any more details.  I haven't seen any detailed documentation regarding how to decipher these messages.  Can someone point me to the right direction?

    The ring buffer outputs are below.  From the Available Physical Memory it looks like we're well below the 100MB threshold and I'd expect memory pressure notifications to occur.  So question 1: How come there would be a RESOURCE_MEM_STEADY notification if Available MBs is so low.

    Next we see the Resource_Memphysical_Low notification which is good.  But what does this section mean:

         Effect type="APPLY_LOWPM" state="EFFECT_OFF" reversed="0">0</Effect>

     <Effect type="APPLY_HIGHPM" state="EFFECT_IGNORE" reversed="0">-2065788311</Effect>

     <Effect type="REVERT_HIGHPM" state="EFFECT_OFF" reversed="0">0</Effect>

    [[rdorr]] The effect type information is the working set of the process.   In this case none are on so SQL has not detected that the working set has been trimmed without notification or such activities.

    From my digging it looks like the notifications are telling SQL to ignore this message?  Is that correct?  And if so, why?  Could it be because of LPIM?

    In Bob Dorr's post http://blogs.msdn.com/psssql/archive/2009/09/17/how-it-works-what-are-the-ring-buffer-resource-monitor-telling-me.aspx

    I should see something like this:

    <IndicatorsProcess>2</IndicatorsProcess>   <-----------------------   Indicator applies to the process as low physical memory

    <IndicatorsSystem>0</IndicatorsSystem> <-----------------------  0 means it is NOT a system wide indicator situation

    But I don't, I only see a <Indicators> tag.  Am I missing something.  Help please.

    Output 1:

    - <Record id="212130" type="RING_BUFFER_RESOURCE_MONITOR" time="6524366015">

    - <ResourceMonitor>

     <Notification>RESOURCE_MEM_STEADY</Notification>

     <Indicators>0</Indicators>

     <NodeId>0</NodeId>

     <Effect type="APPLY_LOWPM" state="EFFECT_OFF" reversed="0">0</Effect>

     <Effect type="APPLY_HIGHPM" state="EFFECT_IGNORE" reversed="0">-2065788311</Effect>

     <Effect type="REVERT_HIGHPM" state="EFFECT_OFF" reversed="0">0</Effect>

     </ResourceMonitor>

    - <MemoryNode id="0">

     <ReservedMemory>589304</ReservedMemory>

     <CommittedMemory>45928</CommittedMemory>

     <SharedMemory>0</SharedMemory>

     <AWEMemory>113936</AWEMemory>

     <SinglePagesMemory>45880</SinglePagesMemory>

     <MultiplePagesMemory>30096</MultiplePagesMemory>

     <CachedMemory>15808</CachedMemory>

     </MemoryNode>

    - <MemoryRecord>

     <MemoryUtilization>100</MemoryUtilization>

     <TotalPhysicalMemory>523656</TotalPhysicalMemory>

     <AvailablePhysicalMemory>46060</AvailablePhysicalMemory>

     <TotalPageFile>2581908</TotalPageFile>

     <AvailablePageFile>1719544</AvailablePageFile>

     <TotalVirtualAddressSpace>8589934464</TotalVirtualAddressSpace>

     <AvailableVirtualAddressSpace>8589099800</AvailableVirtualAddressSpace>

     <AvailableExtendedVirtualAddressSpace>0</AvailableExtendedVirtualAddressSpace>

     </MemoryRecord>

     </Record>

    Output 2:

    - <Record id="212129" type="RING_BUFFER_RESOURCE_MONITOR" time="6524365960">

    - <ResourceMonitor>

     <Notification>RESOURCE_MEMPHYSICAL_LOW</Notification>

     <Indicators>2</Indicators>

     <NodeId>0</NodeId>

     <Effect type="APPLY_LOWPM" state="EFFECT_OFF" reversed="0">0</Effect>

     <Effect type="APPLY_HIGHPM" state="EFFECT_IGNORE" reversed="0">-2065788311</Effect>

     <Effect type="REVERT_HIGHPM" state="EFFECT_OFF" reversed="0">0</Effect>

     </ResourceMonitor>

    - <MemoryNode id="0">

     <ReservedMemory>589304</ReservedMemory>

     <CommittedMemory>45928</CommittedMemory>

     <SharedMemory>0</SharedMemory>

     <AWEMemory>113936</AWEMemory>

     <SinglePagesMemory>65920</SinglePagesMemory>

     <MultiplePagesMemory>30256</MultiplePagesMemory>

     <CachedMemory>35896</CachedMemory>

     </MemoryNode>

    - <MemoryRecord>

     <MemoryUtilization>100</MemoryUtilization>

     <TotalPhysicalMemory>523656</TotalPhysicalMemory>

     <AvailablePhysicalMemory>46064</AvailablePhysicalMemory>

     <TotalPageFile>2581908</TotalPageFile>

     <AvailablePageFile>1719544</AvailablePageFile>

     <TotalVirtualAddressSpace>8589934464</TotalVirtualAddressSpace>

     <AvailableVirtualAddressSpace>8589099800</AvailableVirtualAddressSpace>

     <AvailableExtendedVirtualAddressSpace>0</AvailableExtendedVirtualAddressSpace>

     </MemoryRecord>

     </Record>

Page 1 of 1 (1 items)
Locations of visitors to this page
Recent Posts