Hi Friends,

I was neck-busy in an assignment so could not write for sometime.

Sometime back I wrote an article on monitoring page-splits using extended events. You can browse that article here

Today I want to talk about SQL Server capture deadlocks using Extended Events, but before I you read this blog post further, I suggest you first go through the previous article. The previous article explains some basics of Extended Events which I will not be doing here. Once you run through the previous article, feel free to come to this one.

Extended Events framework had a Dead Lock event:

And the event has some default columns that can be trapped:

Next, we want to create an event session based on the above event. Also specify the action and target.

Finally, start the event.

Now, let us simulate a deadlock.

In a new query window, say connection 1, execute the following code:

From another query window, say connection 2, execute the following code:

Back to connection 1, read the record that has been locked by connection 2; the query will wait

And from connection 2, read the record locked by connection 1; deadlock will occur now and one of the two transactions will be chosen as a victim:

At this time you can roll back both the transactions.

Stop the event now.

You can now see that 2 files have been created in the folder you specified in Target section. Let us extract the event data. Replace the filenames appropriately. Extended Events framework provides a function to read the event data file which I am using here to get the data into a temporary table.

Next, you can preview the data.

You can click on the XML link in the results pane and you will observe XML payload something like this…

You can use XQuery to read the XML event information. Or figure the event data manually. Observe the last element (action name=”sql_text”) which has the query that was chosen as the victim.