Identifying DeadLock Issue/Rootcause using Extended Events!

SQL Server Extended Events (Extended Events) is a general event-handling system for server systems. It has been intorduced in SQL Server 2008!

So, We don't want to enable/apply Trace Flag on StartUp parameters! to log detailed DeadLock info in SQL Server Error Log. Instead, DeadLock info can be retrived using Extended Events!

It has some issue with system_health session! But, Still it can be used...

/*Sample Table and test data*/
Create Table Table1
(
Id Int Identity(1,1),
Column1 Varchar(25)
)
Go

Create Table Table2
(
Id Int Identity(1,1),
Column1 DateTime
)
Go

Insert
Table1(Column1) Values('SQL Server')

Insert Table2(Column1) Values(GETDATE())
Go

/*Session #1(i.e: 59)*/
Begin Tran
Update Table1 Set Column1 ='SQL Server Buddy' Where Id=1
Waitfor Delay '00:00:02'
Update Table2 Set Column1 =GETDATE() Where Id=1


/*Session #2(i.e: 61)*/
Begin Tran
Update Table2 Set Column1 =GETDATE() Where Id=1
Waitfor Delay '00:00:02'
Update Table1 Set Column1 ='SQL Server Buddy' Where Id=1

/*DeadLock occurred in one of the session*/
Msg 1205, Level 13, State 45, Line 4
Transaction (Process ID 61) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.


/*Trackdown and Extract the DeadLock info from Extended Events*/
If OBJECT_ID('DeadLockInfo') Is Null
Create Table DeadLockInfo
(
Id Int Identity(1,1),
DeadLockInfo XML
)
Go

If
OBJECT_ID('DeadLockInfo_Temp') Is Null
Create Table DeadLockInfo_Temp
(
Id Int Identity(1,1),
DeadLockInfo NVARCHAR(MAX)
)
Go

Truncate
Table DeadLockInfo
Truncate Table DeadLockInfo_Temp
Go

Declare
@Max Int, @Min Int

Insert DeadLockInfo_Temp(DeadLockInfo)
select
REPLACE(
REPLACE(XED.X.value('(data/value)[1]', 'varchar(max)'),
'', ''),
'','
') as DlockInfo
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 XED (X)
where XED.X.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

Select @Max = @@IDENTITY
Select @Min = 1

While(@Min <= @Max)
Begin
Begin Try
Insert DeadLockInfo(DeadLockInfo)
Select DeadLockInfo from DeadLockInfo_Temp where Id = @Min
End Try
Begin Catch
--Just Skip the Error/Unformated Record
End Catch

Select @Min = @Min +1

End

;With CTE
As
(
Select DeadLockInfo.value('(deadlock-list/deadlock/victim-list/victimProcess/@id)[1]','varchar(20)') [Victim Process],
DeadLockInfo.value('(deadlock-list/deadlock/process-list/process/@spid)[1]','Int') [SPID1]
From DeadLockInfo
), FinalCTE
AS
(
Select Id [Batch No],case when [Victim Process] Is Not Null Then 'Yes' Else 'No' End [Victim Process],[Process],[Wait Resource],[Wait Time],[Transaction Name],[Lock Mode],[SPID],[Batch Started],
[Batch Ended],[Client Application],[Hosted From],[Login],[Isolation],[DBID],[Proc Name],[Line],[SQL Handle],
[Script] from
(

Select Id,
DeadLock.Node.value('(@id)[1]','varchar(20)') [Process],
DeadLock.Node.value('(@waitresource)[1]','varchar(50)') [Wait Resource],
DeadLock.Node.value('(@waittime)[1]','BigInt') [Wait Time],
DeadLock.Node.value('(@transactionname)[1]','varchar(100)') [Transaction Name],
DeadLock.Node.value('(@lockMode)[1]','varchar(25)') [Lock Mode],
DeadLock.Node.value('(@spid)[1]','Int') [SPID],
DeadLock.Node.value('(@lastbatchstarted)[1]','Datetime') [Batch Started],
DeadLock.Node.value('(@lastbatchcompleted)[1]','Datetime') [Batch Ended],
DeadLock.Node.value('(@clientapp)[1]','Varchar(100)') [Client Application],
DeadLock.Node.value('(@hostname)[1]','Varchar(100)') [Hosted From],
DeadLock.Node.value('(@loginname)[1]','Varchar(100)') [Login],
DeadLock.Node.value('(@isolationlevel)[1]','Varchar(100)') [Isolation],
DeadLock.Node.value('(@currentdb)[1]','Int') [DBID],
DeadLock.Node.value('(executionStack/frame/@procname)[1]','Varchar(100)') [Proc Name],
DeadLock.Node.value('(executionStack/frame/@line)[1]','Int') [Line],
DeadLock.Node.value('(executionStack/frame/@sqlhandle)[1]','Varchar(50)') [SQL Handle],
DeadLock.Node.value('(inputbuf)[1]','Varchar(Max)') [Script]
From DeadLockInfo Cross Apply DeadLockInfo.nodes('//deadlock-list/deadlock/process-list/process') DeadLock(Node)
) as A Left Join CTE On (A.Process = CTE.[Victim Process] And A.SPID = CTE.SPID1)
)

Select * from FinalCTE Order By [Batch No] Desc
Go

/*Result*/

5 comments:

  1. hEY sErVeR bUDdY!
    vAlUbLe PoSt :)

    ReplyDelete
  2. I had to remove deadlock-list/ to get this to work. But I still think this article i great.

    ReplyDelete
    Replies
    1. Thanks for your valuable comment on this post!

      Extended Events still some known bugs on preparing XML node! So, that I have concatenated here! then only I could able to fetch the actual Info.,

      Delete
  3. Thanks. It helps me to find dead lock without enabling disabling trace flag in sql server

    ReplyDelete