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

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

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

 

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

 

 



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

No. Subject Author Date Views
1951 성능분석 17탄 – SQLServer 블록킹 / 네트워크 관련 성능 카운터 jevida(강성욱) 2016.10.15 3721
1950 성능분석 16탄 – 메모리 / CPU 관련 성능 카운터 jevida(강성욱) 2016.10.15 5134
1949 성능분석 15탄 – I/O 관련 성능 카운터 jevida(강성욱) 2016.10.15 4006
1948 성능분석 14탄 – SQL Server 사용 성능 카운터 jevida(강성욱) 2016.10.15 3955
1947 성능분석 13탄 – 누락된 인덱스(missing index) jevida(강성욱) 2016.10.15 2347
1946 성능분석 12탄 – 문제 쿼리 식별 jevida(강성욱) 2016.10.15 2125
1945 성능분석 11탄 – 실행 계획 분석 jevida(강성욱) 2016.10.15 4166
1944 성능분석 10탄 – 쿼리 실행 대기 시간 분석(xevent) jevida(강성욱) 2016.10.15 1909
1943 성능분석 9탄 – 쿼리 실행 분석 jevida(강성욱) 2016.10.15 3947
1942 성능분석 8탄 – IO 통계 (DISK 활동 분석) jevida(강성욱) 2016.10.15 1462
1941 성능분석 7탄 – 프로파일러 대기 유형 및 PREEMPTIVE_OS_WRITEFILEGATHER jevida(강성욱) 2016.10.15 1603
1940 성능분석 6탄 – CPU 경합 및 동시성 관련 대기 유형 jevida(강성욱) 2016.10.15 1884
1939 성능분석 5탄 – 메모리 및 네트워크 관련 대기 유형 jevida(강성욱) 2016.10.15 1938
1938 성능분석 4탄 – 디스크 및 IO 관련 대기 유형 jevida(강성욱) 2016.10.15 2077
1937 성능분석 3탄 – 집계 대기 통계 jevida(강성욱) 2016.10.15 1917
1936 성능분석 2탄 – 실행 요청을 기다리는 작업 확인 및 분석 (병렬 처리 대기 확인) jevida(강성욱) 2016.10.15 1354
1935 성능분석 1탄 – 실행 요청을 기다리는 작업 확인 및 분석 jevida(강성욱) 2016.10.15 1990
» 확장이벤트를 사용하여 데드락 정보 확인 jevida(강성욱) 2016.10.15 1549
1933 확장 이벤트를 사용한 CPU 고부하 쿼리 추적 [1] jevida(강성욱) 2016.10.15 2116
1932 데이터에 대한 이해와 spill in tempdb jevida(강성욱) 2016.10.13 1764





XE Login