How It Works: What are the RING_BUFFER_RESOURCE_MONITOR telling me?

对于查询时Insternal 或者External 内存压力很有帮助。http://blogs.msdn.com/b/psssql/archive/2009/09/17/how-it-works-what-are-the-ring-buffer-resource-monitor-telling-me.aspx

 

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.   

CREATEEVENTSESSION RingBufferInfo

ONSERVER

    ADDEVENT sqlos.resource_monitor_ring_buffer_recorded,

    ADDEVENT sqlos.memory_broker_ring_buffer_recorded

    ADDTARGET package0.asynchronous_file_target

       (SETfilename=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*fromsys.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*fromsys.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