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'c:\XEvent\RingBuffer.etx', metadatafile = N'c:\XEvent\RingBuffer.mta', max_file_size = 50, max_rollover_files = 10)

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

select * from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'

<ResourceMonitor>

<Notification>RESOURCE_MEMPHYSICAL_HIGH</Notification>

<IndicatorsProcess>0</IndicatorsProcess>

<IndicatorsSystem>1</IndicatorsSystem>

<NodeId>0</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="0">

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 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