Retrieving Deadlock Graphs with SQL Server 2008 Extended Events

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jonathan Kehayias » Retrieving Deadlock Graphs with SQL Server...

59 posts, Page 2 of 6

««1 2 3 4 5 »»»

Retrieving Deadlock Graphs with SQL Server 2008 Extended Events

Rate Topic

Display Mode

Topic Options

Author

Message

Maxer

Posted Wednesday, August 11, 2010 11:09 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, September 21, 2012 9:22 AM
Points: 250, Visits: 815

When I run your query on my server.... it never finishes! :)

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

Post #967630

Jonathan Kehayias

Posted Wednesday, August 11, 2010 12:07 PM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742

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

Post #967705

Jonathan Kehayias

Posted Wednesday, August 11, 2010 12:24 PM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742

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)

Post #967731

Maxer

Posted Wednesday, August 11, 2010 12:28 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, September 21, 2012 9:22 AM
Points: 250, Visits: 815

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?

Post #967738

Jonathan Kehayias

Posted Wednesday, August 11, 2010 12:44 PM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742

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

Post #967750

blakmk

Posted Wednesday, October 13, 2010 6:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 10:35 AM
Points: 136, Visits: 327

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

Post #1003526

rama.mathanmohan

Posted Wednesday, October 13, 2010 6:51 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 3:16 AM
Points: 10, Visits: 140

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]

Post #1003552

Jonathan Kehayias

Posted Wednesday, October 13, 2010 7:08 AM

SSCommitted

Group: General Forum Members
Last Login: Sunday, May 12, 2013 4:26 PM
Points: 1,696, Visits: 1,742

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

Post #1003578

blakmk

Posted Wednesday, October 13, 2010 7:20 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 10:35 AM
Points: 136, Visits: 327

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

Post #1003588

nasi

Posted Tuesday, November 23, 2010 12:25 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, November 23, 2010 1:28 PM
Points: 1, 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.

Post #1025401

« Prev Topic | Next Topic »

59 posts, Page 2 of 6

««1 2 3 4 5 »»»

Permissions