How to capture deadlocks using system_health Extended Events

19 April,2021 by Jack Vamvas

An application using SQL Server as the database backend was experiencing some application rollbacks. I decided to investigate the SQL Server to identify any errors which could be correlated to the application timeouts experienced by the users. 

I started reviewing the errors in the Extended Events system health logs, which are normally running by default on a SQL Server. They have a ton of useful information . I noticed a steady stream of deadlocks . This is the code used to create a permanent table to store the deadlock details , for review by the application team. 

Extracting details from Extended Events can be challenging but there there is an  Extended Events function -   sys.fn_xe_file_target_read_file , which  will parse the xel file.  The xel file is  a reference to the file name extension of the event file target. 

In the code below - I'm creating a permanent table - to store the data for future use - but I also need to dynamically grab the location the xel file to parse the data. I grab that data by using the sp_readerrorlog procedure. Derive the file path , place in a variable and then use the @path variable to construct the full file path of the event data file (xel).

 

CREATE TABLE DeadlockLog
(dealock_info XML,
logDate DATETIME)

CREATE TABLE #deadlocklog (
            LogDate DATETIME 
            , ProcessInfo VARCHAR(100)
            , [Text] VARCHAR(MAX)
            );
DECLARE @tag VARCHAR (MAX) , @path VARCHAR(MAX);
INSERT INTO #deadlocklog EXEC sp_readerrorlog;
SELECT @tag = text
FROM #deadlocklog 
WHERE [Text] LIKE 'Logging%MSSQL\Log%';
DROP TABLE #deadlocklog;
SET @path = SUBSTRING(@tag, 38, CHARINDEX('MSSQL\Log', @tag) - 29);
INSERT INTO DeadlockLog
SELECT 
  CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockDetails,
  CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]', 'datetime') 
  AS Execution_dateTime
FROM sys.fn_xe_file_target_read_file(@path + '\system_health*.xel', NULL, NULL, NULL)
WHERE OBJECT_NAME like 'xml_deadlock_report';

Read more on Deadlocks 

Number of deadlocks since last SQL Server restart

SQL Server deadlocks analysis


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to capture deadlocks using system_health Extended Events


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer