SQL Server ‐ Deadlocks - shaysalomon12/Data-Engineer GitHub Wiki

1. Starting from SQL Server 2012, we have a default system event session named 'system_health'.

From SSMS nagivate to: Management > Extended Events > Sessions > sytem_health

image

2. Double click on ‘package0.event_file’ to view Target Data collected by extended event.

You shall get a screen as below:

image

Now, click filter from ‘Extended Events’ menu, or by directly clicking the filters button. Choose ‘name’ as field, and provide value equal to 'xml_deadlock_report' under value and then Press Apply. We can also, apply filter on date & time.

3. Once filters applied, we should see only deadlock report items on Target Data window. Now, select any xml_deadlock_report line item, and double click on value field inside Details tab.

image

This will open deadlock report in xml format in another window.

image

4. alternatively, you can run the script below to get deadlocks:

CREATE TABLE #errorlog (
            LogDate DATETIME 
            , ProcessInfo VARCHAR(100)
            , [Text] VARCHAR(MAX)
            );
DECLARE @tag VARCHAR (MAX) , @path VARCHAR(MAX);
INSERT INTO #errorlog EXEC sp_readerrorlog;
SELECT @tag = text
FROM #errorlog 
WHERE [Text] LIKE 'Logging%MSSQL\Log%';

DROP TABLE #errorlog;

SET @path = SUBSTRING(@tag, 38, CHARINDEX('MSSQL\Log', @tag) - 29);

SELECT 
  CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport,
  CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]', 'datetime') 
  AS Execution_Time
FROM sys.fn_xe_file_target_read_file(@path + '\system_health*.xel', NULL, NULL, NULL)
WHERE OBJECT_NAME like 'xml_deadlock_report'
order by 2 desc
;

Output

image

To create a Deadlock Graph - Click on any of the XMLs under the DeadlockReport column. It will open in a new SSMS tab.

5. Save the newly opened *.xml window as *.xdl (Deadlock File).

6. Open the file using "SQL Sentry Plan Explorer" (Open Session -> Select xdl file):

image

7. To view the order of events and objects lock requests between the sessions click the play icon at the bottom-left corner of "SQL Sentry Plan Explorer":

image

8. You can also view a detailed properties for each event in the Deadlock Graph by clicking it:

image

For more details look in the link below. At the bottom of the page you can find a short presentation explaining how to analyze Deadlock Graph using "SQL Sentry Plan Explorer": https://ajaydwivedi.com/errors/analyzing-deadlock-issues/

9. How to read the Deadlock Graph

https://www.sqlshack.com/understanding-the-xml-description-of-the-deadlock-graph-in-sql-server/