How to see deadlock details in Azure SQL Server?-CodePudding

I have some problems with DB deadlocks in my Java Spring Boot Web application that use an Azure SQL Server DB.

Which query can I run in order to get an history of recent deadlocks happend?

Azure AppInsights tells me only some generic informations like:

Transaction (Process ID 79) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction

and the query affected.

But not the transactions details fighting each other.

This query (that I found online) doesn't produce any resultset

 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

and this other query (suggest from my DBA) seems returning only some information lock (non deadlock) about indexes of current transactions.

SELECT  L.request_session_id AS SPID,
    DB_NAME(L.resource_database_id) AS DatabaseName,
    O.Name AS LockedObjectName,
    P.object_id AS LockedObjectId,
    L.resource_type AS LockedResource,
    L.request_mode AS LockType,
    ST.text AS SqlStatementText,       
    ES.login_name AS LoginName,
    ES.host_name AS HostName,
    TST.is_user_transaction as IsUserTransaction,
    AT.name as TransactionName,
    CN.auth_scheme as AuthenticationMethod
FROM    sys.dm_tran_locks L
    JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
    JOIN sys.objects O ON O.object_id = P.object_id
    JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
    JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
    JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
    JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
    CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id

Thanks in advance.

CodePudding user response：

In Azure SQL Database there are already running an Extended Event that captures the deadlocks without any additionally action for customer side.

As @MartinSmith suggested, your first query should run in master DB.

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.
The 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 above code 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>'

Source: Deadlock analysis for SQL Azure Database