확장이벤트를 사용하여 데드락 정보 확인

확장이벤트를 사용하여 데드락 정보 확인

 

• Version : SQL 2008, 2008R2, 2012

 

SQL Server extenede event(확장이벤트)를 사용하여 데드락 정보를 확인해보자.

 

다음 스크립트를 사용하여 교착 상태를 유발 한다. 그리고 데이터를 조회해 보면 이 중 하나는 교착 상태 모니터에 의해 제거 된다.

세션1	세션2
CREATE TABLE t1 (c1 INT); INSERT INTO t1 VALUES (1); GO   BEGIN TRAN UPDATE t1 SET c1 = 2; GO
	CREATE TABLE t2 (c1 INT); INSERT INTO t2 VALUES (1); GO   BEGIN TRAN UPDATE t2 SET c1 = 2; GO
SELECT * FROM t2; GO
	SELECT * FROM t1; GO

 

 

 

다음 스크립트를 사용하여 교착 상태의 정보를 확인 할 수 있다. (2008R2에서 테스트)

SELECT CAST ( REPLACE ( REPLACE ( XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'), '<victim-list>', '<deadlock><victim-list>'), '<process-list>', '</victim-list><process-list>') AS XML) AS DeadlockGraph FROM (SELECT CAST (target_data AS XML) AS TargetData FROM sys.dm_xe_session_targets st JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address WHERE [name] = 'system_health') AS Data CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent) WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

 

 

XML 결과값을 클릭하면 다음과 같이 상세 정보를 확인 할 수 있다.

<deadlock-list> <deadlock> <victim-list> <victimProcess id="process53b3048" /> </victim-list> <process-list> <process id="process53b3048" taskpriority="0" logused="256" waitresource="RID: 1:1:196:0" waittime="9915" ownerId="7397" transactionname="user_transaction" lasttranstarted="2014-02-26T10:10:22.270" XDES="0x8212f950" lockMode="S" schedulerid="3" kpid="4152" status="suspended" spid="52" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-02-26T10:10:41.283" lastbatchcompleted="2014-02-26T10:10:22.270" clientapp="Microsoft SQL Server Management Studio - 쿼리" hostname="WIN-PA3GMI7VEAB" hostpid="3152" loginname="WIN-PA3GMI7VEAB\Administrator" isolationlevel="read committed (2)" xactid="7397" currentdb="1" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200"> <executionStack> <frame procname="" line="2" stmtstart="4" sqlhandle="0x0200000002e8711e46c5b16c64c59932cd9f731c2f071b92" /> </executionStack> <inputbuf>   SELECT * FROM t2; </inputbuf> </process> <process id="process5399288" taskpriority="0" logused="256" waitresource="RID: 1:1:192:0" waittime="3378" ownerId="7429" transactionname="user_transaction" lasttranstarted="2014-02-26T10:10:33.757" XDES="0x82361950" lockMode="S" schedulerid="2" kpid="4144" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2014-02-26T10:10:47.813" lastbatchcompleted="2014-02-26T10:10:33.757" clientapp="Microsoft SQL Server Management Studio - 쿼리" hostname="WIN-PA3GMI7VEAB" hostpid="3152" loginname="WIN-PA3GMI7VEAB\Administrator" isolationlevel="read committed (2)" xactid="7429" currentdb="1" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200"> <executionStack> <frame procname="" line="1" sqlhandle="0x02000000c70b7b1daba27f2ddf3e772600949464d524b6f2" /> </executionStack> <inputbuf> SELECT * FROM t1; </inputbuf> </process> </process-list> <resource-list> <ridlock fileid="1" pageid="196" dbid="1" objectname="" id="lock80110500" mode="X" associatedObjectId="72057594039173120"> <owner-list> <owner id="process5399288" mode="X" /> </owner-list> <waiter-list> <waiter id="process53b3048" mode="S" requestType="wait" /> </waiter-list> </ridlock> <ridlock fileid="1" pageid="192" dbid="1" objectname="" id="lock8010f900" mode="X" associatedObjectId="72057594039107584"> <owner-list> <owner id="process53b3048" mode="X" /> </owner-list> <waiter-list> <waiter id="process5399288" mode="S" requestType="wait" /> </waiter-list> </ridlock> </resource-list> </deadlock> </deadlock-list>

 

 

 

[참고자료]

http://www.sqlskills.com/blogs/paul/getting-historical-deadlock-info-using-extended-events/

 

 

2014-02-26 / 강성욱 / http://sqlmvp.kr