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

       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

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.



Leave a Reply

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

You are commenting using your 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