Catching Deadlock Information in SQL Logs

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 Alejandro Pelc » Catching Deadlock Information in SQL Logs

Catching Deadlock Information in SQL Logs

Rate Topic

Display Mode

Topic Options

Author

Message

Ale Pelc

Posted Sunday, September 18, 2011 9:59 PM

Ten Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 10:59 AM
Points: 1,109, Visits: 523

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

Alejandro Pelc

Post #1177065

mohammed moinudheen

Posted Monday, September 19, 2011 4:30 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:43 PM
Points: 2,174, Visits: 3,586

Thanks Alejandro. Good one.

Mohammed Moinudheen

Post #1177157

lital-494950

Posted Monday, September 19, 2011 4:55 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 1:10 AM
Points: 242, Visits: 381

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

Post #1177165

rama.mathanmohan

Posted Monday, September 19, 2011 5:24 AM

Grasshopper

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

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]

Post #1177175

rbartram-847800

Posted Monday, September 19, 2011 9:44 AM

SSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 09, 2013 12:44 PM
Points: 43, Visits: 87

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.

Post #1177324

seven.srikanth

Posted Tuesday, September 20, 2011 5:56 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:29 AM
Points: 33, Visits: 373

Script not working for me... :)

Version 2005 RTM

Post #1177803

rama.mathanmohan

Posted Tuesday, September 20, 2011 6:59 AM

Grasshopper

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

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

Post #1177871

« Prev Topic | Next Topic »

Permissions