· 6 years ago · Apr 17, 2019, 05:46 PM
1
2--Below is the Code from the Video for Tracing Deadlock with
3--SQL Server Extended Events, works for 2008R2, 2012, 2014
4
5--=====================================
6-- DEAD LOCK QUERY for EXTENDED EVENTS
7--=====================================
8SELECT
9 DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), DeadlockEventXML.value('(event/@timestamp)[1]', 'datetime2')) AS [EventTime],
10 DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@hostname)[1]', 'nvarchar(max)') AS HostName,
11 DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@clientapp)[1]', 'nvarchar(max)') AS ClientApp,
12 DB_NAME(DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@currentdb)[1]', 'nvarchar(max)')) AS [DatabaseName],
13 DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@transactionname)[1]', 'nvarchar(max)') AS VictimTransactionName,
14 DeadlockEventXML.value('(//process[@id[//victim-list/victimProcess[1]/@id]]/@isolationlevel)[1]', 'nvarchar(max)') AS IsolationLevel,
15 DeadlockEventXML.query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS DeadLockGraph,
16 DeadlockEventXML
17FROM
18(
19 SELECT
20 XEvent.query('.') AS DeadlockEventXML,
21 Data.TargetData
22 FROM
23 (
24 SELECT
25 CAST(target_data AS XML) AS TargetData
26 FROM sys.dm_xe_session_targets st
27 JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
28 WHERE s.name = 'system_health' AND
29 st.target_name = 'ring_buffer'
30 ) AS Data
31 CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(XEvent)
32) AS DeadlockInfo
33
34
35--=====================================
36-- Windows/Session #1
37--=====================================
38SELECT @@SPID
39
40IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'ParentTable')
41 DROP TABLE [ParentTable]
42
43CREATE TABLE [ParentTable]
44(
45 [Id] [int] IDENTITY(1,1) NOT NULL,
46 [Name] [varchar](100) NULL,
47 [Value] [varchar](100) NULL,
48 [DateChanged] [datetime] DEFAULT(GETDATE()) NULL,
49 CONSTRAINT [PK_ParentTable] PRIMARY KEY CLUSTERED ([Id] ASC)
50)
51
52IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'ChildTable')
53 DROP TABLE [ChildTable]
54
55CREATE TABLE [ChildTable]
56(
57 [Id] [int] IDENTITY(1,1) NOT NULL,
58 [Name] [varchar](100) NULL,
59 [Value] [varchar](100) NULL,
60 [DateChanged] [datetime] DEFAULT(GETDATE()) NULL,
61 CONSTRAINT [PK_ChildTable] PRIMARY KEY CLUSTERED ([Id] ASC)
62)
63
64
65
66INSERT INTO [ParentTable](Name, Value)
67SELECT 'Name1', 'Value1'
68UNION ALL
69SELECT 'Name2', 'Value2'
70UNION ALL
71SELECT 'Name3', 'Value3'
72
73
74INSERT INTO [ChildTable](Name, Value)
75SELECT 'Name1', 'Value1'
76UNION ALL
77SELECT 'Name2', 'Value2'
78UNION ALL
79SELECT 'Name3', 'Value3'
80
81
82SELECT * FROM ParentTable
83SELECT * FROM ChildTable
84
85
86--=====================================
87-- Windows/Session #2
88--=====================================
89
90-----------------------------------------------------
91-- This window/session is default CASE DEADLOCK --
92-----------------------------------------------------
93
94SET DEADLOCK_PRIORITY LOW
95
96BEGIN TRAN
97 UPDATE ParentTable SET Name = Name + Name WHERE ID=2
98 WAITFOR DELAY '00:00:10'
99 UPDATE ParentTable SET Name = Name + Name WHERE ID=1
100COMMIT TRAN
101
102
103--=====================================
104-- Windows/Session #3
105--=====================================
106
107-----------------------------------------------------
108-- This window/session is default CASE DEADLOCK --
109-----------------------------------------------------
110
111SET DEADLOCK_PRIORITY NORMAL
112
113BEGIN TRAN
114 UPDATE ParentTable SET Name = Name + Name WHERE ID=1
115 WAITFOR DELAY '00:00:10'
116 UPDATE ParentTable SET Name = Name + Name WHERE ID=2
117COMMIT TRAN
118SQLServerExtendedEvents.txt
119Displaying SQLServerExtendedEvents.txt.