Viewing a deadlocks from extended events

Problem: How to view data captured in the extended event session and look for blocking or long running or deadlocks inside a database?

Solution: sys.dm_xe_session_targets (Transact-SQL)

Once you create Extended Events sessions to diagnose SQL Server tracing It logs the sessions into a log folder and stores the events occurred in an xml format inside the file and the extensions for this files are generally (.xel)

There are dynamic management views which helps us to look for the sessions and sessions target data which will show the xml of the target data from the session, file location and other details.

Example:

In this example, I am going to simulate a deadlock for showing how to trace a deadlock using the extended events and I would like to show how we can view the xml of an extended event session and events occurred in the xml:

create three new sessions for creating a deadlock. And copy text from each session and execute one by one

Session 0: --check wether table exits

IF  EXISTS (SELECT 1 FROM Information_schema.tables WHERE table_schema = 'dbo'AND table_name = N'DeadLockTest'              AND table_type = 'BASE TABLE')

BEGIN

DROP TABLE dbo.DeadLockTest

END

GO

CREATE TABLE dbo.DeadLockTest (col1 INT)

INSERT dbo.DeadLockTest SELECT 1

GO

IF EXISTS (SELECT 1 FROM Information_schema.tables WHERE table_schema = 'dbo' AND table_name = N'DeadLockTest2' AND table_type = 'BASE TABLE'              )

BEGIN

DROP TABLE dbo.DeadLockTest2

END

GO

CREATE TABLE dbo.DeadLockTest2 (col1 INT)

INSERT dbo.DeadLockTest2 SELECT 1

GO

Session 1:

BEGIN TRAN

UPDATE dbo.DeadLockTest SET col1 = 1

--run after the second session

--UPDATE dbo.DeadLockTest2 SET col1 = 1

Session 2:

BEGIN TRAN

UPDATE dbo.DeadLockTest2 SET col1 = 1

UPDATE dbo.DeadLockTest SET col1 = 1

Please see the below image to simulate a deadlock.

  

Example 1: viewing the target data from the SSMS object explorer.

Example 2: viewing the target data of a session from T-SQL by querying the dynamic management views.

SELECT XEvent.query('(event/data/value/deadlock/process-list/process/inputbuf)[1]') AS DeadlockText 

FROM ( SELECT XEvent.query('.') AS XEvent

       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 s.name = 'system_health'

                    AND st.target_name = 'ring_buffer'

              ) AS Data

              CROSS APPLY

                 TargetData.nodes

                    ('RingBufferTarget/event[@name="xml_deadlock_report"]')

              AS XEventData ( XEvent )

      ) AS src;

OUTPUT:

  

DeadlockGraph

<inputbuf>UPDATE dbo.DeadLockTest2 SET col1 = 1   </inputbuf>