Deadlock analysis for SQL Azure Database
Published Mar 13 2019 06:40 PM 20K Views
Microsoft
First published on MSDN on Apr 19, 2017
If you have ever had a deadlock in Azure SQL Database you know they can be frustrating to get more information about what is causing the issue. Recently working on a case I built a few queries to help analyze the deadlocks you are having and drill down to the database objects that are locked and causing deadlocks.

First our basic "get all deadlocks" query (all queries unless otherwise noted should be run from your master database):

[code language="sql"]
WITH CTE 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 CTE
[/code]

You can analyze your deadlock graphs by copying the deadlock_xml result into an xdl file and opening it with SSMS. This query can run slowly if you have a large number of deadlocks, it also is hard to get an overall of what is going on if you have a large number of deadlocks.

Our next query will help you analyze a large number of deadlocks by giving you a count of deadlocks by database, query, and the resource it is waiting on.

[code language="sql"]
WITH CTE 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 [db_name], [query_text], [wait_resource], COUNT(*) as [number_of_deadlocks] FROM (
SELECT LTRIM(RTRIM(Replace(Replace(c.value('.', 'nvarchar(250)'),CHAR(10),' '),CHAR(13),' '))) as query_text,
D.value('@waitresource', 'nvarchar(250)') AS [wait_resource],
target_data_XML.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(250)') AS [db_name]
from CTE CROSS APPLY target_data_XML.nodes('(/event/data/value/deadlock/process-list/process/inputbuf)') AS T(C)
CROSS APPLY target_data_XML.nodes('(/event/data/value/deadlock/process-list/process)') AS Q(D)
) deadlock
GROUP BY [query_text], [wait_resource], [db_name]
ORDER BY [number_of_deadlocks] DESC
[/code]

From this you can get a trend of what specific queries and objects are causing deadlocking with in your databases.

Finally you can get the deadlock graphs for a specific object with this query. replace <YourDB> with the database name and <wait_resource> with the wait resource identified from the query above.

[code language="sql"]
WITH CTE 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 [db_name], [wait_resource], [deadlock_xml] FROM (
SELECT target_data_XML.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(250)') AS [db_name],
waitresource_node.value('@waitresource', 'nvarchar(250)') AS [wait_resource],
deadlock_node.query('.') as [deadlock_xml]
FROM CTE CROSS APPLY target_data_XML.nodes('(/event/data/value/deadlock)') AS T(deadlock_node)
CROSS APPLY target_data_XML.nodes('(/event/data/value/deadlock/process-list/process)') AS U(waitresource_node)
) deadlock
WHERE [db_name] = '<YourDB>'
AND [wait_resource] = '<wait_resource>'
[/code]
Version history
Last update:
‎Mar 13 2019 06:40 PM
Updated by: