extended events for deadlock

Question

extended events for deadlock

SQL_Surfer

SSCrazy Eights

Points: 8320
More actions
October 6, 2014 at 2:16 pm

#291302

I've setup a deadlock monitor using extended events like this.
CREATE EVENT SESSION [deadlock] ON SERVER
ADD EVENT sqlserver.lock_deadlock(
ACTION(package0.process_id,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle)
WHERE ([sqlserver].[database_name]=N'Db1' OR [sqlserver].[database_name]=N'Db2' OR [sqlserver].[database_name]=N'DB3')),
ADD EVENT sqlserver.lock_deadlock_chain(
ACTION(package0.process_id,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle)
WHERE ([sqlserver].[database_name]=N'Db1' OR [sqlserver].[database_name]=N'Db2' OR [sqlserver].[database_name]=N'DB3')),
ADD EVENT sqlserver.xml_deadlock_report(
ACTION(package0.process_id,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle)
WHERE ([sqlserver].[database_name]=N'Db1' OR [sqlserver].[database_name]=N'Db2' OR [sqlserver].[database_name]=N'DB3'))
ADD TARGET package0.event_file(SET filename=N'E:\SQL Install\MSSQL11.MSSQLSERVER\MSSQL\Log\deadlock_monitor.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
Deadlock happened couple of days ago. I'm trying to determine the cause of deadlocks. What script should I use to pull that information to see what objects/processes caused deadlock?
October 7, 2014 at 4:23 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Robert Klimes SSCoach Points: 17865 More actions · Answer 1

You could view it in the GUI. Go to management->extended events->session->Deadlock. right-click on package0.event_file and choose "View Target Data"

here is a basic query. I am sure someone will add something much better.

SELECT *, CAST(event_data AS XML) AS 'event_data_XML'

FROM sys.fn_xe_file_target_read_file('E:\SQL Install\MSSQL11.MSSQLSERVER\MSSQL\Log\deadlock_monitor.xel', NULL, NULL, NULL) where object_name = 'xml_deadlock_report'

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

SQL_Surfer SSCrazy Eights Points: 8320 More actions · Answer 2

Thanks. How do I see the query that was run to cause the deadlock?

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 3

Robert klimes (10/6/2014)
You could view it in the GUI. Go to management->extended events->session->Deadlock. right-click on package0.event_file and choose "View Target Data"
here is a basic query. I am sure someone will add something much better.
SELECT *, CAST(event_data AS XML) AS 'event_data_XML'
FROM sys.fn_xe_file_target_read_file('E:\SQL Install\MSSQL11.MSSQLSERVER\MSSQL\Log\deadlock_monitor.xel', NULL, NULL, NULL) where object_name = 'xml_deadlock_report'

If you open the xml column from the above query, it will open in another window. There is lots of info about the transactions that took part in the deadlock.

Igor Micev,My blog: www.igormicev.com

Robert Klimes SSCoach Points: 17865 More actions · Answer 4

At the top of the xml there are tags <victim-list>. here is an example

<victim-list>

<victimProcess id="process207c1d468" />

</victim-list>

the victim process id(s) are the processes that got killed due to the deadlock. Find the Process id in the <process-list> section and in that process id the input buffer will show query that was killed.

<executionStack>

<frame procname="adhoc" line="1" stmtstart="58" stmtend="152" sqlhandle="0x02000000c1450b241d05fed07cbd918b838c810dd57b293c0000000000000000000000000000000000000000">

unknown </frame>

<frame procname="adhoc" line="1" stmtend="94" sqlhandle="0x020000004f5d8600c681164acba4aa957a0615c5021a07ab0000000000000000000000000000000000000000">

unknown </frame>

</executionStack>

<inputbuf>

update deadlock1

set val = 'Two'

where id = 20 </inputbuf>

</process>

the other process in the process list will be the one that succeeded.

<executionStack>

<frame procname="adhoc" line="1" stmtstart="58" stmtend="152" sqlhandle="0x02000000cc61311e914cc8e7d6441c7b2a0f1b42e42f89b50000000000000000000000000000000000000000">

unknown </frame>

<frame procname="adhoc" line="1" stmtend="94" sqlhandle="0x02000000228bbc0f7c4eb2b6fda3f323542a1d043e28550a0000000000000000000000000000000000000000">

unknown </frame>

</executionStack>

<inputbuf>

update deadlock2

set val = 'Two'

where id = 20 </inputbuf>

</process>

further down in the <resource-list>, it will show which objects were involved in the deadlock

<resource-list>

<ridlock fileid="1" pageid="325" dbid="7" objectname="test.dbo.deadlock1" id="lock2066b1e00" mode="X" associatedObjectId="72057594040745984">

<owner-list>

<owner id="process207c1d848" mode="X" />

</owner-list>

<waiter-list>

<waiter id="process207c1d468" mode="U" requestType="wait" />

</waiter-list>

</ridlock>

<ridlock fileid="1" pageid="327" dbid="7" objectname="test.dbo.deadlock2" id="lock2066b1300" mode="X" associatedObjectId="72057594040811520">

<owner-list>

<owner id="process207c1d468" mode="X" />

</owner-list>

<waiter-list>

<waiter id="process207c1d848" mode="U" requestType="wait" />

</waiter-list>

</ridlock>

</resource-list>

here is the entire deadlock report

<event name="xml_deadlock_report" package="sqlserver" timestamp="2014-10-07T13:53:40.772Z">

<data name="xml_report">

<value>

<deadlock>

<victim-list>

<victimProcess id="process207c1d468" />

</victim-list>

<process-list>

<executionStack>

<frame procname="adhoc" line="1" stmtstart="58" stmtend="152" sqlhandle="0x02000000c1450b241d05fed07cbd918b838c810dd57b293c0000000000000000000000000000000000000000">

unknown </frame>

<frame procname="adhoc" line="1" stmtend="94" sqlhandle="0x020000004f5d8600c681164acba4aa957a0615c5021a07ab0000000000000000000000000000000000000000">

unknown </frame>

</executionStack>

<inputbuf>

update deadlock1

set val = 'Two'

where id = 20 </inputbuf>

</process>

<executionStack>

<frame procname="adhoc" line="1" stmtstart="58" stmtend="152" sqlhandle="0x02000000cc61311e914cc8e7d6441c7b2a0f1b42e42f89b50000000000000000000000000000000000000000">

unknown </frame>

<frame procname="adhoc" line="1" stmtend="94" sqlhandle="0x02000000228bbc0f7c4eb2b6fda3f323542a1d043e28550a0000000000000000000000000000000000000000">

unknown </frame>

</executionStack>

<inputbuf>

update deadlock2

set val = 'Two'

where id = 20 </inputbuf>

</process>

</process-list>

<resource-list>

<ridlock fileid="1" pageid="325" dbid="7" objectname="test.dbo.deadlock1" id="lock2066b1e00" mode="X" associatedObjectId="72057594040745984">

<owner-list>

<owner id="process207c1d848" mode="X" />

</owner-list>

<waiter-list>

<waiter id="process207c1d468" mode="U" requestType="wait" />

</waiter-list>

</ridlock>

<ridlock fileid="1" pageid="327" dbid="7" objectname="test.dbo.deadlock2" id="lock2066b1300" mode="X" associatedObjectId="72057594040811520">

<owner-list>

<owner id="process207c1d468" mode="X" />

</owner-list>

<waiter-list>

<waiter id="process207c1d848" mode="U" requestType="wait" />

</waiter-list>

</ridlock>

</resource-list>

</deadlock>

</value>

</data>

<action name="plan_handle" package="sqlserver">

<value>0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000</value>

</action>

<action name="database_name" package="sqlserver">

<value>master</value>

</action>

<action name="database_id" package="sqlserver">

<value>1</value>

</action>

<action name="client_hostname" package="sqlserver">

<value />

</action>

<action name="client_app_name" package="sqlserver">

<value />

</action>

<action name="process_id" package="package0">

<value>6968</value>

</action>

</event>

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]

SQL_Surfer SSCrazy Eights Points: 8320 More actions · Answer 5

My XML looks like this and can't decipher. I do get the the object id to tell me which table. But can't tell for sure what query was running at the time and which process got killed and which one succeeded during the dead lock.

<text>OBJECT</text>

</data>

</data>

<text>Transaction</text>

</data>

</data>

</data>

</data>

</data>

</data>

</data>

</data>

</data>

</data>

</data>

</data>

</data>

</data>

</data>

</action>

</action>

</action>

</action>

<value>Microsoft SQL Server Management Studio</value>

</action>

</action>

</event>

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 6

You can add the following event to the session

ADD EVENT sqlserver.blocked_process_report(

ACTION(package0.process_id,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.plan_handle)

WHERE ([sqlserver].[database_name]=N'Db1' OR [sqlserver].[database_name]=N'Db2' OR [sqlserver].[database_name]=N'DB3')),

and then see if you have more details.

Igor Micev,My blog: www.igormicev.com

Robert Klimes SSCoach Points: 17865 More actions · Answer 7

the xml you posted is from the lock_deadlock event. you need the xml from the xml_deadlock_report event.

Bob
-----------------------------------------------------------------------------
How to post to get the best help[/url]