SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Retrieving Deadlock Graphs with SQL Server 2008 Extended Events


Retrieving Deadlock Graphs with SQL Server 2008 Extended Events

Author
Message
Maxer
Maxer
SSC Eights!
SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)

Group: General Forum Members
Points: 950 Visits: 1603
When I run your query on my server.... it never finishes! Smile

I have let it run for up to 13 minutes... still nothing.

----------
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'

-----------

When I run it against my dev server, it completes quickly, but with no results.

Am I missing something obvious here or some additional step I need to attend to?

Thank you
Jonathan Kehayias
Jonathan Kehayias
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3778 Visits: 1807
You probably have a lot of events in the ring_buffer target that is causing the XML parsing to be slow. What is the output of SELECT @@VERSION on the server, and how big is the XML document in the target_data?

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Jonathan Kehayias
Jonathan Kehayias
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3778 Visits: 1807
Looking at the XQuery code a year later, I do see that there are some things that are not necessarily written well. Try this one out:


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[@name="xml_deadlock_report"]') AS XEventData (XEvent)



If that doesn't quite get it, try pulling the target_data into a xml variable and then working off of that:


declare @xml xml
select @xml = target_data
from sys.dm_xe_session_targets
join sys.dm_xe_sessions on event_session_address = address
where name = 'system_health'

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 @xml as TargetData) AS Data
CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)



Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Maxer
Maxer
SSC Eights!
SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)SSC Eights! (950 reputation)

Group: General Forum Members
Points: 950 Visits: 1603
Thank you, that second set of queries executed almost immediately!

It looks like they return zero rows though, so if I understand that correctly it means there have been no deadlocks since our last server restart?
Jonathan Kehayias
Jonathan Kehayias
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3778 Visits: 1807
Not necessarily, it means that there are none in the current data in the ring_buffer target. The ring_buffer is a in memory target and works in a FIFO manner once the 4MB of buffer space is full. Depending on how much data is in there it could mean that none have occured, I can't really tell you without seeing it.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
blakmk
blakmk
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 337
Great script. The modified version works great (maybe you should update the original article)

Only problem is I get the error:

Msg 9436, Level 16, State 1, Line 7
XML parsing: line 3, character 15, end tag does not match start tag

Sql Server Blog
Sql Server Consultancy

rama.mathanmohan
rama.mathanmohan
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 164
Please find the complete and working solution for this

------------------------------------------------------------------- Analyse Data -------------------------------------------------------------------
SET NOCOUNT ON

IF OBJECT_ID('tempdb..#ZZ_DeadlockEvents2008') IS NOT NULL Drop Table #ZZ_DeadlockEvents2008
IF OBJECT_ID('tempdb..#TheProcess') IS NOT NULL Drop Table #TheProcess
IF OBJECT_ID('tempdb..#TheLog') IS NOT NULL Drop Table #TheLog
IF OBJECT_ID('tempdb..#TheResource') IS NOT NULL Drop Table #TheResource
IF OBJECT_ID('tempdb..#TheAnalysis') IS NOT NULL Drop Table #TheAnalysis

Create Table #ZZ_DeadlockEvents2008([EntryNo] BIGINT IDENTITY CONSTRAINT [ZZ_DeadlockEvents2008_PK] PRIMARY KEY CLUSTERED,[Timestamp] datetime,[AlertTime] datetime,DeadlockGraph xml)
Create Table #TheLog ([Entry No] bigint CONSTRAINT [ZZ_TheLog_PK] PRIMARY KEY CLUSTERED,[DeadlockTime] datetime,DeadlockPID nvarchar(max))
Create Table #TheProcess ([Entry No] bigint,PID nvarchar(max),UserName nvarchar(max),SQL nvarchar(max),SPID int,HostName nvarchar(max))
Create Table #TheResource ([Line No] BIGINT IDENTITY CONSTRAINT [ZZ_TheResource_PK] PRIMARY KEY CLUSTERED,
[Entry No] bigint,OwnerPID nvarchar(max),WaiterPID nvarchar(max),ObjectName nvarchar(max),IndexName nvarchar(max))
Create Table #TheAnalysis ([Entry No] bigint,[DeadlockTime] datetime,DatabaseName nvarchar(max),ObjectName nvarchar(max),IndexName nvarchar(max),ObjectName2 nvarchar(max),IndexName2 nvarchar(max),
[Successful User] nvarchar(max),[Successful SPID] int,[Successful Host] nvarchar(max),[Successful SQL] nvarchar(max),
[Deadlocked User] nvarchar(max),[Deadlocked SPID] int,[Deadlocked Host] nvarchar(max),[Deadlocked SQL] nvarchar(max))

-------------- Load Row Data Into ZZ_DeadlockEvents Table --------------

Insert into #ZZ_DeadlockEvents2008([Timestamp],[AlertTime],DeadlockGraph)

Select XEventData.XEvent.value('@timestamp', 'datetime') as [Timestamp]
,XEventData.XEvent.value('@timestamp', 'datetime') + cast(GETDATE() - GETUTCDATE() as time) as [DeadlockTime]
,Cast(XEventData.XEvent.value('(data/value)[1]','varchar(max)') 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'
Order By [Timestamp]

--------- End of loading Row Data Into ZZ_DeadlockEvents Table ---------

Declare @EntryNo bigint
Declare @DeadlockTime datetime
Declare @DeadlockGraph xml
Declare @PID nvarchar(max)
Declare @Cnt int, @TotCnt int,@OwnerCnt int,@OwnerTotCnt int,@WaiterCnt int,@WaiterTotCnt int
Declare @child xml,@owner xml,@waiter xml

Declare Get_Tables CURSOR FAST_FORWARD FOR (

Select [EntryNo],[AlertTime],[DeadlockGraph] From #ZZ_DeadlockEvents2008 (nolock)
--Where [AlertTime] > '2009-04-01'

)
Open Get_Tables
FETCH NEXT FROM Get_Tables INTO @EntryNo,@DeadlockTime,@DeadlockGraph
WHILE @@FETCH_STATUS = 0
BEGIN
Insert Into #TheLog
Select @EntryNo,@DeadlockTime,Cast(@DeadlockGraph.query('data(/deadlock/victim-list/victimProcess[1]/@id)') as NVARCHAR(MAX))

--- Insert Into List of Process ---
Select @Cnt = 1,@TotCnt = @DeadlockGraph.value('count(/deadlock/process-list/process)','INT')

WHILE @Cnt <= @TotCnt Begin
SELECT @child = @DeadlockGraph.query('deadlock/process-list/process[position()=sql:variable("@Cnt")]')

Insert Into #TheProcess
Select @EntryNo,
Cast(@child.query('data(/process[1]/@id)') as NVARCHAR(MAX)),
@child.value('(/process)[1]/@loginname', 'NVARCHAR(MAX)'),
@child.value('(/process/inputbuf)[1]', 'NVARCHAR(MAX)'),
@child.value('(/process)[1]/@spid', 'int'),
@child.value('(/process)[1]/@hostname', 'NVARCHAR(MAX)')
Select @Cnt = @Cnt + 1
End

--- Insert Into List of Resources (pagelock) ---
Select @Cnt = 1,@TotCnt = @DeadlockGraph.value('count(/deadlock/resource-list/pagelock)','INT')

WHILE @Cnt <= @TotCnt Begin
SELECT @child = @DeadlockGraph.query('/deadlock/resource-list/pagelock[position()=sql:variable("@Cnt")]')

Select @OwnerCnt = 1,@OwnerTotCnt = @child.value('count(/pagelock[1]/owner-list/owner)','INT')
WHILE @OwnerCnt <= @OwnerTotCnt Begin
Select @owner = @child.query('/pagelock[1]/owner-list/owner[position()=sql:variable("@OwnerCnt")]')

Select @WaiterCnt = 1,@WaiterTotCnt = @child.value('count(/pagelock[1]/waiter-list/waiter)','INT')
WHILE @WaiterCnt <= @WaiterTotCnt Begin

Select @waiter = @child.query('/pagelock[1]/waiter-list/waiter[position()=sql:variable("@WaiterCnt")]')

Insert Into #TheResource ([Entry No],OwnerPID,WaiterPID,ObjectName,IndexName)
Select @EntryNo,
Cast(@owner.query('data(/owner[1]/@id)') as NVARCHAR(MAX)),
Cast(@waiter.query('data(/waiter[1]/@id)') as NVARCHAR(MAX)),
@child.value('(/pagelock)[1]/@objectname', 'NVARCHAR(MAX)'),
' '

Select @WaiterCnt = @WaiterCnt + 1
End
Select @OwnerCnt = @OwnerCnt + 1
End
Select @Cnt = @Cnt + 1
End

--- Insert Into List of Resources (objectlock) ---
Select @Cnt = 1,@TotCnt = @DeadlockGraph.value('count(/deadlock/resource-list/objectlock)','INT')

WHILE @Cnt <= @TotCnt Begin
SELECT @child = @DeadlockGraph.query('/deadlock/resource-list/objectlock[position()=sql:variable("@Cnt")]')

Select @OwnerCnt = 1,@OwnerTotCnt = @child.value('count(/objectlock[1]/owner-list/owner)','INT')
WHILE @OwnerCnt <= @OwnerTotCnt Begin
Select @owner = @child.query('/objectlock[1]/owner-list/owner[position()=sql:variable("@OwnerCnt")]')

Select @WaiterCnt = 1,@WaiterTotCnt = @child.value('count(/objectlock[1]/waiter-list/waiter)','INT')
WHILE @WaiterCnt <= @WaiterTotCnt Begin

Select @waiter = @child.query('/objectlock[1]/waiter-list/waiter[position()=sql:variable("@WaiterCnt")]')

Insert Into #TheResource ([Entry No],OwnerPID,WaiterPID,ObjectName,IndexName)
Select @EntryNo,
Cast(@owner.query('data(/owner[1]/@id)') as NVARCHAR(MAX)),
Cast(@waiter.query('data(/waiter[1]/@id)') as NVARCHAR(MAX)),
@child.value('(/objectlock)[1]/@objectname', 'NVARCHAR(MAX)'),
' '

Select @WaiterCnt = @WaiterCnt + 1
End
Select @OwnerCnt = @OwnerCnt + 1
End
Select @Cnt = @Cnt + 1
End

--- Insert Into List of Resources (keylock) ---
Select @Cnt = 1,@TotCnt = @DeadlockGraph.value('count(/deadlock/resource-list/keylock)','INT')

WHILE @Cnt <= @TotCnt Begin
SELECT @child = @DeadlockGraph.query('/deadlock/resource-list/keylock[position()=sql:variable("@Cnt")]')

Select @OwnerCnt = 1,@OwnerTotCnt = @child.value('count(/keylock[1]/owner-list/owner)','INT')
WHILE @OwnerCnt <= @OwnerTotCnt Begin
Select @owner = @child.query('/keylock[1]/owner-list/owner[position()=sql:variable("@OwnerCnt")]')

Select @WaiterCnt = 1,@WaiterTotCnt = @child.value('count(/keylock[1]/waiter-list/waiter)','INT')
WHILE @WaiterCnt <= @WaiterTotCnt Begin

Select @waiter = @child.query('/keylock[1]/waiter-list/waiter[position()=sql:variable("@WaiterCnt")]')

Insert Into #TheResource ([Entry No],OwnerPID,WaiterPID,ObjectName,IndexName)
Select @EntryNo,
Cast(@owner.query('data(/owner[1]/@id)') as NVARCHAR(MAX)),
Cast(@waiter.query('data(/waiter[1]/@id)') as NVARCHAR(MAX)),
@child.value('(/keylock)[1]/@objectname', 'NVARCHAR(MAX)'),
@child.value('(/keylock)[1]/@indexname', 'NVARCHAR(MAX)')

Select @WaiterCnt = @WaiterCnt + 1
End
Select @OwnerCnt = @OwnerCnt + 1
End
Select @Cnt = @Cnt + 1
End

FETCH NEXT FROM Get_Tables INTO @EntryNo,@DeadlockTime,@DeadlockGraph
END
Close Get_Tables
DEALLOCATE Get_Tables

Insert Into #TheAnalysis

SELECT #TheLog.[Entry No],
CAST(LEFT(CONVERT(varchar,#TheLog.[DeadlockTime],120),19) AS datetime) as [TimeStamp],
Case When Charindex('.',Res.[ObjectName]) = 0 Then
Res.[ObjectName]
Else
Substring(Res.[ObjectName],1,Charindex('.',Res.[ObjectName],1)-1) End as [DatabaseName],
Case When Charindex('.dbo.',Res.[ObjectName],1) = 0 Then
Res.[ObjectName]
Else
Substring(Res.[ObjectName],Charindex('.dbo.',Res.[ObjectName],1)+5,len(Res.[ObjectName])) End as [ObjectName],
Res.IndexName,
CASE When Charindex('.dbo.', Res2.[ObjectName], 1) = 0 Then
Res2.[ObjectName]
Else Substring(Res2.[ObjectName], Charindex('.dbo.', Res2.[ObjectName], 1) + 5, len(Res2.[ObjectName])) End as ObjectName2,
Res2.IndexName AS IndexName2,
SP.UserName AS [Successful User],
SP.SPID AS [Successful SPID],
SP.HostName AS [Successful HostName],
SP.SQL AS [Successful SQL],
DP.UserName AS [Deadlocked User],
DP.SPID AS [Deadlocked SPID],
DP.HostName AS [Deadlocked HostName],
DP.SQL AS [Deadlocked SQL]
FROM (SELECT MIN([Line No]) AS [Line No], [Entry No], WaiterPID
FROM #TheResource
GROUP BY [Entry No], WaiterPID) AS WPID LEFT OUTER JOIN
#TheProcess AS SP RIGHT OUTER JOIN
(SELECT DISTINCT [Entry No], OwnerPID, WaiterPID, ObjectName, IndexName
FROM #TheResource) AS Res2 RIGHT OUTER JOIN
#TheResource AS Res ON Res2.[Entry No] = Res.[Entry No] AND Res2.WaiterPID = Res.OwnerPID AND Res2.OwnerPID = Res.WaiterPID ON
SP.PID = Res.OwnerPID AND SP.[Entry No] = Res.[Entry No] ON WPID.[Line No] = Res.[Line No] RIGHT OUTER JOIN
#TheProcess AS DP RIGHT OUTER JOIN
#TheLog ON DP.[Entry No] = dbo.#TheLog.[Entry No] AND DP.PID = dbo.#TheLog.DeadlockPID ON WPID.[Entry No] = dbo.#TheLog.[Entry No] AND
WPID.WaiterPID = dbo.#TheLog.DeadlockPID
ORDER BY #TheLog.[Entry No]

----------------------------------------- Summerise Data -----------------------------------------

--------- Analyse The Deadlock Query By User------------------------

Select Max([Entry No]) as [Last Entry No]
,Count([Entry No]) as [Deadlock Count]
,Max([DeadlockTime]) as [Last Occurred]
,[Deadlocked SQL],[Successful SQL]
--,[Deadlocked User],[Successful User]
--,[ObjectName],IndexName
From #TheAnalysis
Group By
[Deadlocked SQL],[Successful SQL]
--,[Deadlocked User],[Successful User]
--,[ObjectName],IndexName
Order By [Deadlock Count] Desc

--------- List All Deadlocks & Summerise the count by date with moving average ---------

Declare @DLockCount Table([Entry No] BIGINT IDENTITY,[Date] datetime,[Dead Lock Count] decimal)

Insert into @DLockCount ([Date],[Dead Lock Count])

Select LEFT(CONVERT(varchar,[DeadlockTime],120),10) as [Date]
,Count(*) as [Deadlock Count]
From #TheAnalysis
Group By LEFT(CONVERT(varchar,[DeadlockTime],120),10)
Order By [Date]

SELECT
LEFT(CONVERT(varchar, Date, 120), 10) AS Date,
[Dead Lock Count],
Case When [Entry No] < 7 Then
( SELECT Cast(Round(Avg([Dead Lock Count]),2) as numeric(20,2))
FROM @DLockCount AS Ave
Where Ave.[Date] <= Main.[Date] and Ave.[Date] > DATEADD(dd,-7,Main.[Date]))
Else
( SELECT Cast(Round(Sum([Dead Lock Count])/7,2) as numeric(20,2))
FROM @DLockCount AS Ave
Where Ave.[Date] <= Main.[Date] and Ave.[Date] > DATEADD(dd,-7,Main.[Date])) End AS [Dead Lock Ave (1W)]
FROM @DLockCount AS Main

------------------------------ Generate Output ------------------------------
Select * From #TheAnalysis Order by [Entry No]
Jonathan Kehayias
Jonathan Kehayias
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3778 Visits: 1807
blakmk,

What is the @@VERSION of the server that you got that error on? The bug that caused invalid deadlock XML to occur was fixed in one of the latest CU's and should be in SP2 as well, so the workaround isn't needed.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
blakmk
blakmk
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 337
Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM)

I guess I need to go for sp2

Sql Server Blog
Sql Server Consultancy

nasi
nasi
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 1
great article, extremely helpful. Rather new to SQL 2008, and I was only used to looking for things in the logs in SQL 2000... so this was a revelation. The XML files are perfect to see which query(ies) are causing the deadlock.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search