SQL Server – Deadlock diagnosis in SQL Server – A very quick guide to locate problem

Do You have problems with deadlocks on SQL Server or on Portal Azure? Do You want locate problem in quick time? Below I present very quick guide how in the easiest way perform analyse without additional or sophisticated tools. In case of deadlocs, problem not bases on fixing problematic sql statements but on finding source of problem. I love simple solutions and below I present step by step solution with pure sql statement:
1. Please open sql command window on Master DB – Very important: NOT on DB where is problem
2. Paste following sql statemnt to sql command window

WITH deadlockcte 
     AS (SELECT Cast(event_data AS XML) AS [target_data_XML] 
         FROM   sys.Fn_xe_telemetry_blob_target_read_file('dl', NULL, NULL, NULL 
                )) 
SELECT 
target_data_xml.value('(/event/@timestamp)[1]', 'DateTime2') 
AS Timestamp, 
target_data_xml.query('/event/data[@name="xml_report"]/value/deadlock') 
AS deadlock_xml, 
target_data_xml.query('/event/data[@name="database_name"]/value').value('(/value)[1]', 'nvarchar(100)') AS db_name 
FROM   deadlockcte

3. Execute sql statement and as result You should see below similar result


4. When You click on deadlock_xml You will see operations responsible for deadlocks on Your DB 🙂

 

September 12th, 2017

Leave a Reply

Your email address will not be published. Required fields are marked *