Deadlocks in SQL Azure V12

Hi All

 

Today I want to explain how to find and troubleshoot Deadlocks in SQL Azure V12. 

 

At V2 we had this query to run and find our deadlocks:

 

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

 

This query return XML, and we could have change it to XDL and see the deadlock Chart.

 

You can see this in the 2 links by Thomas Larock (@SQLRockstar):

 

 

http://thomaslarock.com/2013/05/how-to-troubleshoot-deadlocks-in-windows-azure-sql-database-wasd/

&

https://www.youtube.com/watch?v=RlKCe05WDOw

 

Now in V12 this feature is not supported - so how do you get the deadlock data?

 

MSFT Gave us this query (Running on Master DB):

 

 

SELECT top 100 *,

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, CAST(event_data as XML)

FROM sys.fn_xe_telemetry_blob_target_read_file('el', null, null, null)

where object_name = 'database_xml_deadlock_report'

order by CAST(event_data as XML).value('(/event/@timestamp)[1]', 'datetime2') desc

 

 

We get XML and can use it or its graphical view (save as xdl).

 

This is written in some delay.

 

This is new  and Un documented fn, that reads the event log from BLOB (as I understand), so this is actually the first look of EE in SQL Azure.

 

Enjoy and Thanks to Geri Resef, helped me with this.

 

Pini