Deadlocks – Azure SQL Database

Would you like to troubleshoot a deadlock in Azure SQL Database? To do this you probably will be after the deadlock graph. So does this mean that you need to setup your own extended event session? No, it doesn’t.

Already running is a session so you can issue the following query to get the details. (I created a deadlock on purpose here).


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  

xmldeadlocl
Analyse the XML as you would for on-premises SQL Server – its no different in terms of looking out for the victim and what lock types were waiting etc.

deadlockfullgraph.JPG

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s