Question

A production Azure SQL Database intermittently reports transaction rollbacks that users describe as 'the app just freezes and then errors out.' You suspect deadlocks but the errors are sporadic and you cannot reproduce them on demand. You need to capture the full deadlock graph, including the resource and process lists, with the least ongoing overhead so you can analyze the victim and surviving transactions after the fact. Which approach should you use?

Accepted Answer

Create an Extended Events session in Azure SQL Database that captures the sqlserver.xml_deadlock_report event, storing output to a ring_buffer or event file target for later analysis.

Answer

Enable trace flag 1222 on the database so deadlock graphs are written to the error log.

Answer

Query sys.dm_tran_locks joined to sys.dm_os_waiting_tasks on a repeating schedule to catch the deadlock as it occurs.

Answer

Enable Query Store and review the top resource-consuming queries in the regressed plans report to identify the deadlock.