SQL Deadlock Report - Extended Events

 

SQL Deadlock Report - Extended Events


Event Creation

create EVENT SESSION EE_Deadlock ON SERVER

    ADD EVENT sqlserver.xml_deadlock_report

    ADD TARGET package0.ring_buffer

    (SET max_memory = 4096)

WITH (MAX_DISPATCH_LATENCY = 30 SECONDS);



create Procedure  DeadlockReport

as

Begin


alter event session EE_Deadlock ON SERVER state=Stop;


if not exists (select * from Archival.sys.objects where name like '%Deadlock_Data%')

Begin

Create TABLE Archival..Deadlock_Data (

    Rowid  INT IDENTITY PRIMARY KEY,

    event_data XML)

End


Truncate Table Archival..Deadlock_Data


insert into Archival..Deadlock_Data(event_data)

(

select 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 = 'EE_Deadlock') AS Data

CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)

where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

)


if not exists (select * from Archival.sys.objects where name like '%Deadlock_Master%')

Begin

Create TABLE Archival..Deadlock_Master (

    Rowid  INT IDENTITY PRIMARY KEY,

    event_data XML)

End

insert into Archival..Deadlock_Master(event_data) select EVENT_DATA from archival..deadlock_data(nolock)


if not exists (select * from Archival.sys.objects where name like '%deadlock_report%') 

Begin  

Create table Archival..deadlock_report

(

spid varchar(10),

processid varchar(25),

victimprocessid varchar(25),

dbid varchar(10),

waitresource varchar(100),

lockmode varchar(100),

Timestamp varchar(100),

clientapp varchar(100),

hostname varchar(100),

loginname varchar(100),

victim_list varchar(max)

)

End

Truncate Table Archival..deadlock_report

insert into Archival..deadlock_report(spid,processid,victimprocessid,dbid,waitresource,lockmode,Timestamp,clientapp,hostname,loginname,victim_list)

SELECT 

T.X.value('(./@spid)', 'varchar(10)') as spid,

T.X.value('(./@id)', 'varchar(25)') as processid,

T3.Z.value('(./@id)','varchar(25)') as victimprocessid,

T.X.value('(./@currentdb)', 'varchar(10)') as dbid,

T.X.value('(./@waitresource)', 'varchar(100)') as waitresource,

T.X.value('(./@lockMode)', 'varchar(100)') as lockmode,

T.X.value('(./@lasttranstarted)', 'varchar(100)') as TimeStamp,

T.X.value('(./@clientapp)', 'varchar(100)') as clientapp,

T.X.value('(./@hostname)', 'varchar(100)') as hostname,

T.X.value('(./@loginname)', 'varchar(100)') as loginname,

T2.Y.value('.', 'varchar(max)') as victim_list

FROM

  Archival..Deadlock_Data  

CROSS APPLY

 event_data.nodes('/deadlock/process-list/process') T(X)

OUTER APPLY

 T.X.nodes('inputbuf') T2(Y)

CROSS APPLY

 T.X.nodes('/deadlock/victim-list/victimProcess') T3(Z)order by TimeStamp desc

 

alter event session EE_Deadlock ON SERVER state=Start;


End  



CREATE Procedure DeadlockReport

as

Begin


if not exists (select * from Archival.sys.objects where name like '%Deadlock_Data%')

Begin

Create TABLE Archival..Deadlock_Data (

    Rowid  INT IDENTITY PRIMARY KEY,

    event_data XML)

End


Truncate Table Archival..Deadlock_Data


insert into Archival..Deadlock_Data(event_data)

(

select 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 = 'EE_Deadlock') AS Data

CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)

where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

)


if not exists (select * from Archival.sys.objects where name like '%Deadlock_Master%')

Begin

Create TABLE Archival..Deadlock_Master (

    Rowid  INT IDENTITY PRIMARY KEY,

    event_data XML)

End

insert into Archival..Deadlock_Master(event_data) select EVENT_DATA from archival..deadlock_data(nolock)


if not exists (select * from Archival.sys.objects where name like '%deadlock_report%') 

Begin  

Create table Archival..deadlock_report

(

spid varchar(10),

processid varchar(25),

victimprocessid varchar(25),

dbid varchar(10),

waitresource varchar(100),

lockmode varchar(100),

Timestamp varchar(100),

clientapp varchar(100),

hostname varchar(100),

loginname varchar(100),

victim_list varchar(max)

)

End

Truncate Table Archival..deadlock_report

insert into Archival..deadlock_report(spid,processid,victimprocessid,dbid,waitresource,lockmode,Timestamp,clientapp,hostname,loginname,victim_list)

SELECT 

T.X.value('(./@spid)', 'varchar(10)') as spid,

T.X.value('(./@id)', 'varchar(25)') as processid,

T3.Z.value('(./@id)','varchar(25)') as victimprocessid,

T.X.value('(./@currentdb)', 'varchar(10)') as dbid,

T.X.value('(./@waitresource)', 'varchar(100)') as waitresource,

T.X.value('(./@lockMode)', 'varchar(100)') as lockmode,

T.X.value('(./@lasttranstarted)', 'varchar(100)') as TimeStamp,

T.X.value('(./@clientapp)', 'varchar(100)') as clientapp,

T.X.value('(./@hostname)', 'varchar(100)') as hostname,

T.X.value('(./@loginname)', 'varchar(100)') as loginname,

T2.Y.value('.', 'varchar(max)') as victim_list

FROM

  Archival..Deadlock_Data  

CROSS APPLY

 event_data.nodes('/deadlock/process-list/process') T(X)

OUTER APPLY

 T.X.nodes('inputbuf') T2(Y)

CROSS APPLY

 T.X.nodes('/deadlock/victim-list/victimProcess') T3(Z)order by TimeStamp desc

 

alter event session EE_Deadlock ON SERVER state=Stop;

alter event session EE_Deadlock ON SERVER state=Start;


End  


 

Link: http://www.mssqltips.com/sqlservertip/1036/finding-and-troubleshooting-sql-server-deadlocks/

Comments

Post a Comment

Popular posts from this blog

SQL Server – How to re-initialize just a single article in transaction replication

  SQL Server – How to re-initialize just a single article in transaction replication -- Upskill 2020 .  1. Turn off @allow_anonymous and @immediate_sync on the publication. use PUBLICATION_DB_NAME go EXEC sp_changepublication @publication = 'Upskill2020_Test', @property = N'allow_anonymous', @value = 'false' GO   EXEC sp_changepublication @publication = 'Upskill2020_Test', @property = N'immediate_sync', @value = 'false' GO The reason we must disable @immediate_sync is because every time you add a new article, and if @immediate_sync is  enabled, it will cause the entire snapshot to be applied. Our objective is to only apply a particular article. 2. Add new article. EXEC sp_addarticle @publication = 'Upskill2020_Test', @article = 'Employee', @source_object = 'Employee', @force_invalidate_snapshot = 1 3. Refresh the subscription EXEC sp_refreshsubscriptions @publication = 'Repl_RBD' GO 4. Check the current sna...
Read more

DBA Daily Commands in Handy

Image
sp_who2 active:  View details of active processes on SQL Server Engine DBCC INPUTBUFFER(session id):  View the current query being executed by a specific proc ess Check the open transaction in SQL Server: DBCC OPENTRAN Check the blocked process: SELECT * FROM sysprocesses where blocked > 0 and spid = 23; select * from sys.sysprocesses where blocked != 0; Check the lock information: SELECT * from sys.dm_tran_lock Check the connection information from view in SQL Server: SELECT * FROM sys.dm_exec_sessions; Check the database files location, size, and max size in SQL Server SELECT * FROM sysaltfiles Check the collation Setting on the SQL Server SP_HELPSORT Check the log Usage on SQL Server databases select * from sys.databases where name like '%log_reuse_wait_desc%'
Read more