Catching Deadlock Information in SQL Logs

  • Ale Pelc

    SSCrazy

    Points: 2421

    Comments posted to this topic are about the item Catching Deadlock Information in SQL Logs

    Alejandro Pelc

  • M&M

    SSC-Insane

    Points: 21679

    Thanks Alejandro. Good one.

    M&M

  • lital-494950

    SSC Veteran

    Points: 283

    by mistake i vote 4 stars but i meant it to be 5 stars.my mistake.

    Thanks a lot

  • rama.mathanmohan

    SSC Veteran

    Points: 268

    A better way is to use SQL Server extended events in SQL2008 and the server already captures the deadlock information in the default system health check extended event. Here is the code ....

    ------------------------------------------------------------------- Analyse Data -------------------------------------------------------------------

    SET NOCOUNT ON

    set dateformat ymd

    Select Min([login_time]) as 'Server Restart' from master..sysprocesses (nolock) where [lastwaittype] like '%LAZY%'

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

    CREATE NONCLUSTERED INDEX [idx1] ON [#TheProcess] ([Entry No])

    CREATE NONCLUSTERED INDEX [idx1] ON [#TheResource] ([Entry No])

    CREATE NONCLUSTERED INDEX [idx1] ON [#TheAnalysis] ([Entry No])

    -------------- First Workout if it is a buggy XML Graph version or not --------------

    Declare @BugFlag int

    Set @BugFlag = (Select Max(Case When charindex('deadlock-list',XEventData.XEvent.value('(data/value)[1]','varchar(max)')) > 0 Then 1 Else 0 End) As BugFlag

    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(max)') = 'xml_deadlock_report'

    )

    ---------------------------------------------------------------------------------------

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

    If @BugFlag = 1

    Begin

    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(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(max)') = 'xml_deadlock_report'

    Order By [Timestamp]

    End

    Else

    Begin

    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(max)') = 'xml_deadlock_report'

    Order By [Timestamp]

    End

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

    If @DeadlockGraph.exist('/deadlock-list/deadlock[1]') = 1

    SELECT @DeadlockGraph = @DeadlockGraph.query('/deadlock-list/deadlock[1]')

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

    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

    --------- List All Deadlocks & Summerise the count by table, Deadlocked User, Successful User, date ---------

    Select --LEFT(CONVERT(varchar,[DeadlockTime],120),10) as [Date],

    --[ObjectName],

    --[Successful User],

    [Deadlocked User],

    --IndexName,

    Count(*) as [Deadlock Count]

    From #TheAnalysis

    Group By --LEFT(CONVERT(varchar,[DeadlockTime],120),10)

    --[ObjectName]

    --[Successful User]

    [Deadlocked User]

    --IndexName

    Order By [Deadlock Count] Desc

    --Order By [Date]

    ------------------------------ Generate Deadlock Detail Log ------------------------------

    Select [Entry No],[DeadlockTime],

    CAST(LEFT(CONVERT(varchar,[DeadlockTime],120),10) AS datetime) as [Date],

    [Successful User],[Successful SPID],[Successful Host],[Successful SQL],

    Case When (len(ObjectName) - len(REPLACE(ObjectName,'$','')) in (1,3)) or

    (PATINDEX('%[0-9]$[0-9]',ObjectName) <> 0) Then

    Substring(ObjectName,PATINDEX('%$%',ObjectName)+1,len(ObjectName))

    Else

    ObjectName End as TableName,

    [Deadlocked User],[Deadlocked SPID],[Deadlocked Host],[Deadlocked SQL],

    Case When (len(ObjectName2) - len(REPLACE(ObjectName2,'$','')) in (1,3)) or

    (PATINDEX('%[0-9]$[0-9]',ObjectName2) <> 0) Then

    Substring(ObjectName2,PATINDEX('%$%',ObjectName2)+1,len(ObjectName2))

    Else

    ObjectName2 End as TableName2

    From #TheAnalysis Order by [Entry No]

  • rbartram-847800

    SSC Veteran

    Points: 224

    I use a Free product called SQL Heartbeat by SQL solutions. It lets you see visually the deadlocks as they happen and with graphic links of who is causing the deadlock. It's saved me many times when running large reports on high transaction servers in production. It's also great for after vendor updates of new software to show where issues are with their new db and stored proc code. Instead of custom doing all your own code, which is nightmare if you ever quit and another db takes your place, I prefer to purchase an inexpensive solution from a vendor so you have support, graphs and charting, alerts, etc. something like Redgate or SQL Solutions Deadlock detector.

  • seven.srikanth

    Ten Centuries

    Points: 1152

    Script not working for me... 🙂

    Version 2005 RTM

  • rama.mathanmohan

    SSC Veteran

    Points: 268

    As stated it only works on SQL2008 and above for SQL2005 you need to use WMI Query method see http://msdn.microsoft.com/en-us/library/ms186385.aspx

Viewing 7 posts - 1 through 7 (of 7 total)

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