Deadlock analysis for SQL Azure Database


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):

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 

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.

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

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.

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>'
Comments (0)

Skip to main content