Query deadlock info in Azure SQL DB

A couple of weeks ago, a developer came to me and wanted to know how to figure out what was causing a deadlock. I honestly didn’t know where to look or if this was even being captured in Azure SQL DB already. It turns out that Microsoft has you covered with deadlock tracking. At least for a period of time. It looks like you can go back about a month, maybe.

Table of Contents

What is a deadlock?

A deadlock occurs when two or more tasks permanently block one another because each task has a lock on a resource the other task is trying to lock. Blocking between sessions in Azure SQL DB is minimized because all new databases have read committed snapshot isolation level (RCSI) enabled by default, but deadlock may still occur. Queries that modify data may block one another. Queries might run under different isolation levels that increase blocking. RCSI may also be disabled which will cause the database to use shared locks.

Source: https://learn.microsoft.com/en-us/azure/azure-sql/database/analyze-prevent-deadlocks?view=azuresql&tabs=ring-buffer#an-example-deadlock

Microsoft suggests that the lowest-risk approach to preventing deadlocks from reoccurring is to tune nonclustered indexes to optimize queries involved in the deadlock. Of course, tuning indexes is an art and science and not particularly uncomplicated. I would also recommend tuning queries to use the indexes optimally.

How can you see if you have deadlocks?

This query shows whether they are happening or not, but we need more information to troubleshoot them. Run this in the master db:

SELECT * FROM sys.event_log
WHERE event_type = 'deadlock';

If you’ve had any deadlocks, the previous query will give you some results. I made a deadlock happen so you can see my results.

This query gives you the information you need to determine which query was the victim and which was not. Also, run this in the master db:

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

This query returns results if you’ve had a deadlock. You will see a column for deadlock_xml.

If you click on that deadlock_xml, you will get the details. You want to look for the victim process to figure out which query was the victim. Mine is this <victimProcess id=”process1a79cd0b848″ />. Make sure you are looking at the right process id. Here’s the query that was the victim.

If you scroll down more, you will see the query that was not the victim.

How can you create a deadlock to test your ability to capture deadlocks?

Here’s more info from Microsoft and how to create a deadlock to see the above queries in action. I used their queries to create the deadlock above to test my queries and provide you with screenshots.

What if you need more data collected like wait types or errors?

If you need to capture more than deadlocks in Azure SQL DB, you will need to create an extended event for this, as it does not have the system_health xevent by default. Here’s a good example from Grant.

The post Query deadlock info in Azure SQL DB appeared first on sqlkitty.