데이터베이스 개발자 Tip & 강좌

SQLER의 개발자들이 만들어가는 데이터베이스 사용자 Tip & 강좌 게시판입니다. SQL서버, Oracle, MySQL 등 여러 클라우드/오픈소스 기반 데이터베이스 개발 및 운영 관련 팁과 쿼리 노하우를 이곳에서 가장 먼저 접하실 수 있습니다. 많은 도움 되시길 바랍니다.

DDL 트리거를 활용한 ERRORLOG에 XEVENT 상태 기록하기

 

  • Version : SQL Server 2012

 

SQL Server에 적용되어 있는 XEVENT의 활동을 중지하거나 시작하거나, 또는 생성, 삭제, 수정이 발생 하였을 경우 어떻게 감지 할 수 있을까?

 

다음은 DDL 트리거를 활용하여 XEVENT의 상태 변경이 발생 하였을 경우 이벤트 로그에 남기는 법을 알아 보자.

 

XEVENT를 생성하고 삭제하고 또는 중지 하였을 경우 에로로그 파일에 기록이 남지 않는다.

 

다음은 기존의 확장 이벤트를 삭제하고 등록하는 예제이다.

 

XEVENT를 삭제해 보았다.

DROP EVENT SESSION [system_health] ON SERVER

GO

 

그리고 XEVENT를 새로 생성하였다. 아래 스크립트는 시스템헬스 체크를 하는 이벤트 이다.

CREATE EVENT SESSION [system_health] ON SERVER

ADD EVENT sqlclr.clr_allocation_failure(

ACTION(package0.callstack,sqlserver.session_id)),

ADD EVENT sqlclr.clr_virtual_alloc_failure(

ACTION(package0.callstack,sqlserver.session_id)),

ADD EVENT sqlos.memory_broker_ring_buffer_recorded,

ADD EVENT sqlos.memory_node_oom_ring_buffer_recorded(

ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)),

ADD EVENT sqlos.scheduler_monitor_deadlock_ring_buffer_recorded,

ADD EVENT sqlos.scheduler_monitor_non_yielding_iocp_ring_buffer_recorded,

ADD EVENT sqlos.scheduler_monitor_non_yielding_ring_buffer_recorded,

ADD EVENT sqlos.scheduler_monitor_non_yielding_rm_ring_buffer_recorded,

ADD EVENT sqlos.scheduler_monitor_stalled_dispatcher_ring_buffer_recorded,

ADD EVENT sqlos.scheduler_monitor_system_health_ring_buffer_recorded,

ADD EVENT sqlos.wait_info(

ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text)

WHERE ([duration]>(15000) AND ([wait_type]>(31) AND ([wait_type]>(47) AND [wait_type]<(54) OR [wait_type]<(38) OR [wait_type]>(63) AND [wait_type]<(70) OR [wait_type]>(96) AND [wait_type]<(100) OR [wait_type]=(107) OR [wait_type]=(113) OR [wait_type]>(174) AND [wait_type]<(179) OR [wait_type]=(186) OR [wait_type]=(207) OR [wait_type]=(269) OR [wait_type]=(283) OR [wait_type]=(284)) OR [duration]>(30000) AND [wait_type]<(22)))),

ADD EVENT sqlos.wait_info_external(

ACTION(package0.callstack,sqlserver.session_id,sqlserver.sql_text)

WHERE ([duration]>(5000) AND ([wait_type]>(365) AND [wait_type]<(372) OR [wait_type]>(372) AND [wait_type]<(377) OR [wait_type]>(377) AND [wait_type]<(383) OR [wait_type]>(420) AND [wait_type]<(424) OR [wait_type]>(426) AND [wait_type]<(432) OR [wait_type]>(432) AND [wait_type]<(435) OR [duration]>(45000) AND ([wait_type]>(382) AND [wait_type]<(386) OR [wait_type]>(423) AND [wait_type]<(427) OR [wait_type]>(434) AND [wait_type]<(437) OR [wait_type]>(442) AND [wait_type]<(451) OR [wait_type]>(451) AND [wait_type]<(473) OR [wait_type]>(484) AND [wait_type]<(499) OR [wait_type]=(365) OR [wait_type]=(372) OR [wait_type]=(377) OR [wait_type]=(387) OR [wait_type]=(432) OR [wait_type]=(502))))),

ADD EVENT sqlserver.connectivity_ring_buffer_recorded(SET collect_call_stack=(1)),

ADD EVENT sqlserver.error_reported(

ACTION(package0.callstack,sqlserver.database_id,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_stack)

WHERE ([severity]>=(20) OR ([error_number]=(17803) OR [error_number]=(701) OR [error_number]=(802) OR [error_number]=(8645) OR [error_number]=(8651) OR [error_number]=(8657) OR [error_number]=(8902)))),

ADD EVENT sqlserver.security_error_ring_buffer_recorded(SET collect_call_stack=(1)),

ADD EVENT sqlserver.sp_server_diagnostics_component_result(SET collect_data=(1)

WHERE ([sqlserver].[is_system]=(1) AND [component]<>(4))),

ADD EVENT sqlserver.xml_deadlock_report

ADD TARGET package0.event_file(SET filename=N'system_health.xel',max_file_size=(5),max_rollover_files=(4)),

ADD TARGET package0.ring_buffer(SET max_events_limit=(5000),max_memory=(4096))

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=120 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)

GO

 

 

XEVENT의 변경 사항은 어디에서 확인 할 수 있을까? 에러로그에도 기록은 남아 있지 않다.

 

 

다음 스크립트를 실행하여 서버전체의 DDL 트리거를 생성하자. 이벤트 세션의 변화에 대해서 감지하도록 하였다. 그리고 그 내용을 에러로그에 기록하도록 하였다.

CREATE TRIGGER XEventLogging

ON ALL SERVER

    FOR DDL_EVENT_SESSION_EVENTS

 

AS

 

BEGIN

    SET NOCOUNT ON;

 

    DECLARE @EventData XML = EVENTDATA();

    DECLARE @EventType NVARCHAR(256) = @EventData.value('(EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(256)')

    DECLARE @SessionName NVARCHAR(256) = @EventData.value('(EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(256)')

    DECLARE @LoginName NVARCHAR(256) = @EventData.value('(EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(256)')

    DECLARE @Command NVARCHAR(MAX) = @EventData.value('(EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)');

    DECLARE @msg NVARCHAR(440) = CASE

        WHEN @EventType = 'CREATE_EVENT_SESSION' THEN 'Extended Event session created. Session Name = ''%s''. Login Name = ''%s''.'

        WHEN @EventType = 'ALTER_EVENT_SESSION' AND LOWER(@Command) LIKE LOWER('%STATE%=%START%') THEN 'Extended Event session started. Session Name = ''%s''. Login Name = ''%s''.'

        WHEN @EventType = 'ALTER_EVENT_SESSION' AND LOWER(@Command) LIKE LOWER('%STATE%=%STOP%') THEN 'Extended Event session stopped. Session Name = ''%s''. Login Name = ''%s''.'

        WHEN @EventType = 'DROP_EVENT_SESSION' THEN 'Extended Event session dropped. Session Name = ''%s''. Login Name = ''%s''.' END

 

RAISERROR(@msg, 10, 1, @SessionName, @LoginName) WITH LOG;

 

END

 

GO

 

 

트리거 생성 후 이벤트 세션을 중지하거나 상태 변경을 하였을 경우 에러로그 파일에 기록되는 것을 확인 할 수 있다.

 

트리거를 사용하는 것은 시스템에 오버헤드를 유발하지만 상황에 따라 필요한 정보를 남겨야 하는 경우 매우 유용하게 사용 할 수 있다. 자신이 관리하는 시스템의 특성을 잘 파악하여 사용 할 수 있도록 하자.

 



강성욱 / jevida@naver.com
Microsoft SQL Server MVP
Blog : http://sqlmvp.kr
Facebook : http://facebook.com/sqlmvp

No. Subject Author Date Views
1871 DMV를 활용한 CPU 트러블슈팅 - Sys.dm_exec_query_stats, sys.dm_os_ring_buffers 활용 [1] jevida(강성욱) 2016.10.08 1907
1870 백업 미디어 세트에 압축 백업 추가하기 jevida(강성욱) 2016.10.08 1451
1869 Collation에 따른 실행계획 변경과 성능 문제 jevida(강성욱) 2016.10.08 1667
1868 SQL Connection Timeout 디버깅 with BizTalk Server jevida(강성욱) 2016.10.08 2279
1867 인스턴스 파일 초기화 활성 jevida(강성욱) 2016.10.08 1710
1866 누락된 인덱스 확인하기 jevida(강성욱) 2016.10.08 2871
1865 비클러스터 인덱스 페이지 내용 jevida(강성욱) 2016.10.08 2011
1864 ATTACH DATABASE 오류 1314 jevida(강성욱) 2016.10.08 1161
1863 SQL Server 커넥션 풀링 jevida(강성욱) 2016.10.08 4051
1862 가상 SQL Server에 Hot Add vCPU 사용하기 jevida(강성욱) 2016.10.08 1112
» DDL 트리거를 활용한 ERRORLOG에 XEVENT 상태 기록하기 jevida(강성욱) 2016.10.08 1348
1860 쉐어포인트의 SQL Server 접속 문제 jevida(강성욱) 2016.10.08 1446
1859 Lazy Log Truncation jevida(강성욱) 2016.10.08 1290
1858 인덱스 구성과 상황에 따른 인덱스 성능 jevida(강성욱) 2016.10.08 1462
1857 Max worker thread 초과 이슈 jevida(강성욱) 2016.10.08 2663
1856 SQL Server Failover 클러스터 설치 트러블슈팅 jevida(강성욱) 2016.10.08 2342
1855 MAXDOP 극대화 하기 jevida(강성욱) 2016.10.08 2018
1854 SQL Server 가상화 팁 jevida(강성욱) 2016.10.08 2019
1853 Net Framework 4.0과 SQL Server 2008 설치 오류 jevida(강성욱) 2016.10.08 1842
1852 SQL Server 인덱스 튜닝 접근 jevida(강성욱) 2016.10.07 3441





XE Login