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