Retrieving Deadlock Graphs with SQL Server 2008 Extended Events

  • 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

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

    SelectXEventData.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------------------------

    SelectMax([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

    WhereAve.[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

    WhereAve.[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]

  • 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[/url]

  • 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

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

  • I copied below code and it is giving me the xml parsing error, note that I am running it on SQL Server 2008 R2, Patch level 1753.

    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)

  • Hi,

    Remember there was a bug in the XML structure returned and the REPLACE commands are there to fix it. Given microsoft has already filed it and you are on the latest build of SQL2008 R2 no need to have REPLACE commans. So try

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

  • I get the bad XML error because of tags like this that show up intermittently--

    <victim-list>

    <victimProcess id="process541c988">

    <victimProcess id="process6e8bdc8"/>

    </victim-list>

    I've started doing this--

    WITH SessionData as (

    SELECT

    XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') VarcharResults

    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)

    )

    SELECT

    substring(VarcharResults, charindex( 'lasttranstarted="', VarcharResults) + len('lasttranstarted="'),19) ApproxDateGuess

    , cast (

    REPLACE(REPLACE(

    case

    -- bad tag, should be <victimProcess id="process_______"/>

    when patindex( '%<victimProcess id="process_______">%', VarcharResults ) > 1 then

    stuff( VarcharResults , patindex( '%<victimProcess id="process_______">%', VarcharResults )+34,0,'/')

    else VarcharResults

    end

    , '<victim-list>', '<deadlock><victim-list>'), '<process-list>','</victim-list><process-list>')

    as XML ) DeadlockTree

    FROM SessionData

    ORDER BY 1;

    But it seems that I don't get the most recent few weeks of data. I know I've gotten deadlocks in the past two weeks, but when I run it today, the most recent record is from 5-6-2011

    Here's my @@version:

    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)

  • Hello Guys,

    I am trying to run one of the scripts to capture the Deadlock info. I could not get a working version .

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

    [highlight=#ffff11]I am on SQL SERVER 2008 R2 CU5. [/highlight]

    I could get the output but I could not open the xml as xdl file to get a graphicla view.

    Your help is much appreciated.

    Regards

    Murali

  • murali.jillellamudi 85871 (11/4/2011)


    I could get the output but I could not open the xml as xdl file to get a graphicla view.

    Your help is much appreciated.

    Regards

    Murali

    This is covered in the article at the end.

    From here, you can click on each XML graph and have it open expanded as a XML document in Management Studio. However, unlike the deadlock graph that is generated by SQL Trace, you can't save this XML document as a .XDL file and open it graphically in Management Studio. The reason for this is because the Extended Events Deadlock Graph is a new XML schema and format that differs from the one that is being output by SQL Trace and the Trace Flags for backwards compatibility. The new deadlock graph output will display multiple victims which was a short coming in the old format, so it is necessary to actually be able to read the deadlock graph if you plan to use the information generated by Extended Events for troubleshooting deadlocks.

    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[/url]

  • Jon,

    thanks for your quick response and clarifying things.

    Cheers

    Murali

  • Great article. During our morning meeting my boss commented that he saw a deadlock on a high profile server that goes live Friday and couldn't find any information on it. By the end of the meeting (about 10 minutes) I had found this article, and was able to give him both queries and both SPIDs so we could start tracking down what happend. Very cool!

    Thanks

    Kenneth

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • Hi Jonathan,

    Do you have pointers to create a SQL job that sends deadlock notifications , for sql server 2008 by using the extended events?

    Thanks.

  • No, but then again I wouldn't use Extended Events for this type of task, I would instead use Event Notifications as shown in my PASS 2011 demos:

    http://www.sqlskills.com/resources/conferences/300_UsingEventNotificationsSQLServer20052008.zip

    Cheers,

    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[/url]

  • I'm a little late getting into Extended Events but the code provided by Jonathan is great. I needed to trace some deadlock events on a SQL 2008 SP1 server and need to alter the replace logic a little to deal with a missing / in the process victim list. Here it is, hopefully it helps someone else:

    select @@VERSION

    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)

    Steve
    http://stevefibich.net
    http://utilitydb.codeplex.com/

Viewing 15 posts - 16 through 30 (of 83 total)

You must be logged in to reply to this topic. Login to reply