SQL Server Capture Deadlocks using Extended Events

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:

select * from sys.dm_xe_objects
where name = 'lock_deadlock'

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

select * from sys.dm_xe_object_columns
where object_name = 'lock_deadlock';

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

CREATE EVENT SESSION DeadLocks
ON SERVER
ADD EVENT
  sqlserver.lock_deadlock
  (
    ACTION
     (
       sqlserver.database_id,
       sqlserver.client_app_name,
       sqlserver.sql_text
     )
   )
ADD TARGET package0.asynchronous_file_target
( SET filename = 'C:\Amit\capture_DeadLocks.xel',
metadatafile = 'C:\Amit\capture_DeadLocks.mta',
max_file_size = 10,
max_rollover_files = 10);
 
GO

Finally, start the event.

-- start the event
ALTER EVENT SESSION DeadLocks
ON SERVER
STATE = START
GO

Now, let us simulate a deadlock.

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

-- connection 1
 
use AdventureWorks
GO
 
BEGIN TRAN
update Person.Contact
set FirstName = 'Amit'
where ContactID = 1

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

-- connection 2
 
use AdventureWorks
GO
 
BEGIN TRAN
update Person.Contact
set LastName = 'BAnsal'
where ContactID = 2

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

-- connection 1
 
select * from Person.Contact
where ContactID = 2

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:

-- connection 2
 
select * from Person.Contact
where ContactID = 1

At this time you can roll back both the transactions.

Stop the event now.

--stop the event
ALTER EVENT SESSION DeadLocks
ON SERVER
STATE = STOP

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.

-- extract the data
-- replace the filenames with your filenames
 
DECLARE @xel_filename varchar(256) = 'C:\Amit\track_page_splits_0_129689559924490000.xel'
DECLARE @mta_filename varchar(256) = 'C:\Amit\track_page_splits_0_129689559924490000.mta'
 
SELECT CONVERT(xml, event_data) as Event_Data
INTO #File_Data
FROM sys.fn_xe_file_target_read_file(@xel_filename, @mta_filename, NULL, NULL)

Next, you can preview the data.

-- preview the data
select * from #File_Data

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

<event name="lock_deadlock" package="sqlserver" id="70" version="1" timestamp="2011-12-21T15:48:11.461Z">
  <data name="resource_type">
    <value>7</value>
    <text>KEY</text>
  </data>
  <data name="mode">
    <value>3</value>
    <text>S</text>
  </data>
  <data name="owner_type">
    <value>1</value>
    <text>Transaction</text>
  </data>
  <data name="transaction_id">
    <value>18905</value>
    <text />
  </data>
  <data name="database_id">
    <value>9</value>
    <text />
  </data>
  <data name="lockspace_workspace_id">
    <value>0x000000008006c280</value>
    <text />
  </data>
  <data name="lockspace_sub_id">
    <value>1</value>
    <text />
  </data>
  <data name="lockspace_nest_id">
    <value>1</value>
    <text />
  </data>
  <data name="resource_0">
    <value>88</value>
    <text />
  </data>
  <data name="resource_1">
    <value>131328</value>
    <text />
  </data>
  <data name="resource_2">
    <value>1957881960</value>
    <text />
  </data>
  <data name="deadlock_id">
    <value>356</value>
    <text />
  </data>
  <action name="database_id" package="sqlserver">
    <value>9</value>
    <text />
  </action>
  <action name="client_app_name" package="sqlserver">
    <value>Microsoft SQL Server Management Studio - Query</value>
    <text />
  </action>
  <action name="sql_text" package="sqlserver">
    <value>select * from Person.Contact
where ContactID = 2</value>
    <text />
  </action>
</event>

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.