Read ALL SQL deadlock information from Extended Events Log

How to read single deadlock information from extend event log

Read SQL deadlock information from Extended Events Log

So if we want to read all deadlock information which log into extend event,

how I doing ?

1. Run the below query in ssms to retrieve deadlock information from extend event.

Remember to change the folder name based on sqlversion

 "FROM sys.fn_xe_file_target_read_file('D:\MSSQL11.MSSQLSERVER\MSSQL"

 2. Copy the result into notepad and save

3. Open the notepad from excel with delimited "|"

4. All deadlock information will show in excel worksheet

create table #tempdeadlock
(rownumber int,
 Code varchar(max),
 Code2 varchar(max),
 Code3 varchar(max),
 Code4 varchar(max),
  Code5 varchar(max),
   Code6 varchar(max),
   PagelockObject varchar(200),
   DeadlockObject varchar(200),
   [processid] varchar(200),
 [KeylockObject] varchar(200),
 [Index] varchar(200),
 [IndexLockMode] varchar(5),
 [Victim] char(1),
  VictimProcessID NVarChar(50),
 [Procedure]  varchar(200),
 [LockMode] char(1),
 [ClientApp]  varchar(100),
 [HostName]  varchar(20),
 [LoginName] varchar(20),
 [spid] varchar(10),
 [TransactionTime] datetime,
   [InputBuffer] varchar(1000))


declare @max int
declare @min int
declare @intFlag int=1
declare @textall varchar(max)
select @max=max(rownumber),@min=min(rownumber) from #temp2
set @intflag=@min
WHILE (@intFlag <=@max)
BEGIN
    PRINT @intFlag
    set @textall=
    '
    declare @text varchar(max)
    declare @xmltext xml
    select @text=''''+replace(cast(xml1 as varchar(max)),'''''''','''''''''''')+'''' from #temp2 where rownumber='+cast(@intflag as varchar(10))+'
     set @xmltext=cast(@text as xml)
    insert into #tempdeadlock
    select '+ cast(@intflag as varchar(10))+ ' as rownumber,
 [Code] =  replace(replace(replace(replace(Deadlock.Process.value(''executionStack[1]/frame[1]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '') ,
[Code2] =  replace(replace(replace(replace(Deadlock.Process.value(''executionStack[1]/frame[2]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '') ,
[Code3] =  replace(replace(replace(replace(Deadlock.Process.value(''executionStack[1]/frame[3]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '') ,
[Code4] =  replace(replace(replace(replace(Deadlock.Process.value(''executionStack[1]/frame[4]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '') ,
[Code5] =  replace(replace(replace(replace(Deadlock.Process.value(''executionStack[1]/frame[5]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '') ,
[Code6] = replace(replace(replace(replace(Deadlock.Process.value(''executionStack[1]/frame[6]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '') ,
  [PagelockObject] = @xmltext.value(''/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname'', ''varchar(200)''),
 [DeadlockObject] =@xmltext.value(''/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname'', ''varchar(200)''),
[processid]=Deadlock.Process.value(''@id'', ''varchar(200)''),
 [KeylockObject] = Keylock.Process.value(''@objectname'', ''varchar(200)''),
 [Index] = Keylock.Process.value(''@indexname'', ''varchar(200)''),
 [IndexLockMode] = Keylock.Process.value(''@mode'', ''varchar(5)''),
 [Victim] = case when Deadlock.Process.value(''@id'', ''varchar(50)'') = DeadlockList.Graphs.value(''(victim-list/victimProcess[1]/@id)[1]'', ''NVarChar(50)'')  then 1 else 0 end,
 DeadlockList.Graphs.value(''(victim-list/victimProcess[1]/@id)[1]'', ''NVarChar(50)'') AS VictimProcessID,
 [Procedure] = Deadlock.Process.value(''executionStack[1]/frame[1]/@procname[1]'', ''varchar(200)''),
 [LockMode] = Deadlock.Process.value(''@lockMode'', ''char(1)''),
 [ClientApp] = Deadlock.Process.value(''@clientapp'', ''varchar(100)''),
 [HostName] = Deadlock.Process.value(''@hostname'', ''varchar(20)''),
 [LoginName] = Deadlock.Process.value(''@loginname'', ''varchar(20)''),
 [spid] = Deadlock.Process.value(''@spid'', ''varchar(10)''),
 [TransactionTime] = Deadlock.Process.value(''@lasttranstarted'', ''datetime''),
  [InputBuffer] = replace(replace(replace(replace(Deadlock.Process.value(''inputbuf[1]'', ''varchar(1000)''),CHAR(13)+CHAR(10),''''),char(13),''''),char(10),''''),'' '','' '')

 from @xmltext.nodes(''/deadlock-list/deadlock/process-list/process'') as Deadlock(Process)
      LEFT JOIN @xmltext.nodes(''/deadlock-list/deadlock/resource-list/keylock'') as Keylock(Process)
        ON Keylock.Process.value(''owner-list[1]/owner[1]/@id'', ''varchar(50)'') =
           Deadlock.Process.value(''@id'', ''varchar(50)'')
      CROSS JOIN  @xmltext.nodes(''/deadlock-list/deadlock'') AS DeadlockList(Graphs)
      '
  exec (@textall)
    SET @intFlag = @intFlag + 1
 END

select * from #tempdeadlock



select distinct rownumber,'|' as '|',
 REPLACE(REPLACE(REPLACE(Code, CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), '') as Code ,'|' as '|',
  REPLACE(REPLACE(REPLACE(Code2, CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), '') as Code2 ,'|' as '|',
   REPLACE(REPLACE(REPLACE(Code3, CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), '') as Code3 ,'|' as '|',
    REPLACE(REPLACE(REPLACE(Code4, CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), '') as Code4 ,'|' as '|',
     REPLACE(REPLACE(REPLACE(Code5, CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), '') as Code5 ,'|' as '|',
      REPLACE(REPLACE(REPLACE(Code6, CHAR(13) + CHAR(10), ''), CHAR(13), ''), CHAR(10), '') as Code6 ,'|' as '|',

 PagelockObject ,'|' as '|',   DeadlockObject ,'|' as '|',   [processid] ,'|' as '|', [KeylockObject] ,'|' as '|',
 [Index] ,'|' as '|', [IndexLockMode] ,'|' as '|', [Victim] ,'|' as '|',  VictimProcessID ,'|' as '|', [Procedure]  ,'|' as '|', [LockMode] ,'|' as '|',
 [ClientApp] ,'|' as '|', [HostName]  ,'|' as '|', [LoginName] ,'|' as '|',
 [spid] ,'|' as '|', [TransactionTime] ,'|' as '|',   [InputBuffer]  from
#tempdeadlock where 1=1

Read SQL deadlock information from Extended Events Log

Analysis Deadlock from Extended Event Log is difficult.
It will give result in XML Code


Do google and found out script to convert the XML code to table format so we can easy analysis the deadlock information


SQLServerCentral.com - Reading Deadlock Trace
http://www.sqlservercentral.com/Forums/Topic955603-360-1.aspx


we paste the xml format get from the extended event log;
1) SSMS- >Servername->Management->Extended Events-> Session-> system_health->Package0.event_file
2) Right click -> View Target data
3) filter with xml_deadlock_report
4) in the row return with xml_deadlock_report, click on detail window -> values
    xml data will be appear in another window
5) copy the deadlock graph xml data and add at the start of xml file and to the end of the xml data
6) pass the xml into "put your deadlock graph here"
http://www.sqlservercentral.com/Forums/Topic955603-360-1.aspx
declare @deadlock xml set @deadlock = 'put your deadlock graph here'
select [PagelockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'), [DeadlockObject] = @deadlock.value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'), [KeylockObject] = Keylock.Process.value('@objectname', 'varchar(200)'), [Index] = Keylock.Process.value('@indexname', 'varchar(200)'), [IndexLockMode] = Keylock.Process.value('@mode', 'varchar(5)'), [Victim] = case when Deadlock.Process.value('@id', 'varchar(50)') = @deadlock.value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') then 1 else 0 end, [Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'), [LockMode] = Deadlock.Process.value('@lockMode', 'char(1)'), [Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'), [ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'), [HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'), [LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'), [TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'), [InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
 from @deadlock.nodes('/deadlock-list/deadlock/process-list/process') as Deadlock(Process)
      LEFT JOIN @deadlock.nodes('/deadlock-list/deadlock/resource-list/keylock') as Keylock(Process)
        ON Keylock.Process.value('owner-list[1]/owner[1]/@id', 'varchar(50)') = Deadlock.Process.value('@id', 'varchar(50)')