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.

 

 

   

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

3 Comments on “SQL Server Capture Deadlocks using Extended Events”

  1. — 2008

    — bug fixed with this https://connect.microsoft.com/SQLServer/feedback/details/404168/invalid-xml-in-extended-events-xml-deadlock-report-output

    select CAST(

    REPLACE(

    REPLACE(XEventData.XEvent.value(‘(data/value)[1]’, ‘varchar(max)’),

    ”, ”),

    ”,”)

    as xml) as DeadlockGraph

    FROM

    (select CAST(target_data as xml) as TargetData

    from sys.dm_xe_session_targets st

    join sys.dm_xe_sessions s on s.address = st.event_session_address

    where name = ‘system_health’) AS Data

    CROSS APPLY TargetData.nodes (‘//RingBufferTarget/event’) AS XEventData (XEvent)

    where XEventData.XEvent.value(‘@name’, ‘varchar(4000)’) = ‘xml_deadlock_report’

  2. Hi Amit,

    Thanks a lot for the article. your help is much appreciated. but I could not extract the SQL_Text. any idea

    My output looks like this :

    Microsoft Windows Operating System
    Unable to retrieve SQL text

Leave a Reply

Your email address will not be published.