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.
|
IndicatorsProcess | Process wide indicator using an |= of the following values
|
IndicatorsSystem | System wide indicator an |= of the following values
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.
|
NodeId | Memory Node association of the RM |
[참고자료]
강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp