My application has been logging dead lock exceptions return from sql azure to my .net application:
Transaction (Process ID 152) was deadlocked on lock resources with another process....
I would like to investigate this further to see if I can prevent those deadlocks from happening.
How do I get the details about those past and future deadlocks? I remember that the sql server profiler is able to extract some deadlock events with some xml that shows all the queries involved in the dead lock. That is what I’m after.
However, since this runs on Sql Azure, there is no profiler.
You can use extended events on Azure to capture deadlock details.
Following article explains in details.
Based on the comment I am adding this section. I just tested this and it works.
You need to run the following query in Master database (Not user database where your deadlock happened) in order to get the XML of the deadlock.
WITH deadlockcte 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)', '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)', 'nvarchar(100)') AS db_name FROM deadlockcte
Which will have the details about the deadlock processes and you can get a deadlock graph by:
- Copy the deadlock_xml column results from the previous query and load into a text file. If more than one row is returned, you will want to do each row result separate.
- Save the file as a ‘.xdl’ extension, (e.g. deadlock.xdl) which can be viewed in tools such as SQL Server Management Studio as a deadlock report/graphic.
Based on the last comment I am adding my test code which does show text in inputbuffer.
/* Deadlock simulation code was copied from: http://stackoverflow.com/questions/22825147/how-to-simulate-deadlock-on-sql-server */ --Simulating deadlock IF OBJECT_ID('Employees') IS NOT NULL DROP TABLE Employees CREATE TABLE Employees ( EmpId INT IDENTITY, EmpName VARCHAR(16), Phone VARCHAR(16) ) GO IF OBJECT_ID('Suppliers') IS NOT NULL DROP TABLE Suppliers CREATE TABLE Suppliers( SupplierId INT IDENTITY, SupplierName VARCHAR(64), Fax VARCHAR(16) ) GO INSERT INTO Employees (EmpName, Phone) VALUES ('Martha', '800-555-1212'), ('Jimmy', '619-555-8080') GO INSERT INTO Suppliers (SupplierName, Fax) VALUES ('Acme', '877-555-6060'), ('Rockwell', '800-257-1234') GO /* --Window 1 BEGIN TRAN; UPDATE Employees SET EmpName = 'Mary' WHERE empid = 1 --Window=2 BEGIN TRAN; UPDATE Suppliers SET Fax = N'555-1212' WHERE supplierid = 1 Window 1 UPDATE Suppliers SET Fax = N'555-1212' WHERE supplierid = 1 window 2 UPDATE Employees SET phone = N'555-9999' WHERE empid = 1 --commit */ DROP TABLE Employees GO DROP TABLE Suppliers GO