SQL Server-Capturing Deadlocks using Extended Events

SQL Server-Capturing 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:

: http://sqlservergeeks.com/articles/sql-server-bi/76/sql-server-monitoring-page-splits-using-extended-events

Today I want to talk about capturing 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.

If you liked this post, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks

Have a SQL Server question? Join the fastest growing SQL Server facebook group at: http://www.facebook.com/groups/458103987564477/

Regards, 
Amit Bansal

http://www.twitter.com/A_Bansal

http://www.twitter.com/SQLServerGeeks

http://www.amitbansal.net/

Visit my FaceBook page at http://www.facebook.com/AmitRSBansal

Contribute on SQLServerGeeks.com: visit http://www.sqlservergeeks.com/default-category/write-for-us