Ring_Buffer_Resource_Monitor

Ring_Buffer_Resource_Monitor

 

• Version : SQL Server 2005, 2008, 2008R2, 2012

 

 

리소스 모니터 및 메모리 브로커 기록은 RM의 핵심 요소이다. 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'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);

 

다음 DMV를 통해서 링 버퍼 리소스 모니터를 확인 할 수 도 있다.

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

 

 

[참고자료]

http://blogs.msdn.com/b/psssql/archive/2009/09/17/how-it-works-what-are-the-ring-buffer-resource-monitor-telling-me.aspx

 

 

2014-01-15 / 강성욱 / http://sqlmvp.kr