How do I identify a deadlock in SQL Azure?
Run the following query on "Master" database in SQL Azure db,
select * from sys.event_log where event_type='deadlock' and database_name='<Databasename>';
There was a performance issue with this query, if it gets timed out try following,
SELECT *
,CAST(event_data AS XML).value('(/event/@timestamp)[1]', 'datetime2') AS TIMESTAMP
, CAST(event_data AS XML).value('(/event/data[@name="error"]/value)[1]', 'INT') AS error
,CAST(event_data AS XML).value('(/event/data[@name="state"]/value)[1]', 'INT') AS STATE
,CAST(event_data AS XML).value('(/event/data[@name="is_success"]/value)[1]', 'bit') AS is_success
,CAST(event_data AS XML).value('(/event/data[@name="database_name"]/value)[1]', 'sysname') AS database_name
FROM sys.fn_xe_telemetry_blob_target_read_file('dl', NULL, NULL, NULL)
WHERE object_name = 'database_xml_deadlock_report'
Second query has data in XML format relating to the processes being executed. Good luck!
Monitoring of SQL Azure is more limited than SQL Server, but the tools are becoming more available for you to look underneath:
http://social.technet.microsoft.com/wiki/contents/articles/troubleshoot-and-optimize-queries-with-sql-azure.aspx
Now Azure SQL database supports two ways to get deadlock xml reports. You can create a db-scoped XE session with the database_xml_deadlock_report event to track them yourself, or you can modify the sys.fn_xe_telemetry_blob_target_read_file call from the earlier answer to use 'dl' instead of 'el'. Deadlocks are now routed to their own file instead of being mixed in with login events.
This MSDN article has the latest information.