Capturing historic deadlock using Extended Event

Question

Post reply

Capturing historic deadlock using Extended Event

SQL006

SSCertifiable

Points: 5071
More actions
December 9, 2014 at 6:44 am

#299227

Hi
I am learning Extended event to capture deadlock which already happened, for this in my SQL SERVER 2012
i am simulating a deadlock . With the help of Jonathan Kehayias blog [/url] where he given a query to find the deadlock details using extended event here is the code
-- Retrieve from Extended Events in 2012
SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM ( SELECT XEvent.query('.') AS XEvent
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 s.name = 'system_health'
AND st.target_name = 'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes
('RingBufferTarget/event[@name="xml_deadlock_report"]')
AS XEventData ( XEvent )
) AS src;
Here is the Code for creating deadlock
CREATE TABLE dbo.DeadLockTest (col1 INT)
INSERT dbo.DeadLockTest SELECT 1
CREATE TABLE dbo.DeadLockTest2 (col1 INT)
INSERT dbo.DeadLockTest2 SELECT 1
--Open up a new query window and paste this code and execute it:
BEGIN TRAN
UPDATE dbo.DeadLockTest SET col1 = 1
--Open up another new query window and paste and execute this code:
BEGIN TRAN
UPDATE dbo.DeadLockTest2 SET col1 = 1
UPDATE dbo.DeadLockTest SET col1 = 1
--Go back to your first query window (with the first BEGIN TRAN statement) and execute this code:
UPDATE dbo.DeadLockTest2 SET col1 = 1
This code creates a deadlock but when i run the above Extended events query to get the details of deadlock, it doesnot display any results.
If i am missing something , please help
Thank you

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323507 More actions · Answer 1

i've got something i'm cosnuming for SQL2008R2 for deadlocks, and comparing it to yours, my item is slightly different:

you have

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph

mine has this...no "deadlock" in the XEvent

SELECT XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')

could you try that and see if it makes a difference?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 2

That is rather odd, because I can find the deadlock event searching through the file target for the system health session.

Lowell, I did try your change and it made no difference. Were you referring to using the SQL Server 2008 R2 version of Jonathan's query:

select CAST(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[@name="xml_deadlock_report"]') AS XEventData (XEvent);

The above query still returns nothing on my SQL Server 2012 instance.

Alternative, is to just query the XEL files directly:

SELECT CAST([event_data] AS xml) AS event_data

FROM [sys].[fn_xe_file_target_read_file]('system_health*xel',NULL,NULL,NULL)

WHERE [object_name] = 'xml_deadlock_report';

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

SQL006 SSCertifiable Points: 5071 More actions · Answer 3

Lowell (12/9/2014)
i've got something i'm cosnuming for SQL2008R2 for deadlocks, and comparing it to yours, my item is slightly different:
you have
SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
mine has this...no "deadlock" in the XEvent
SELECT XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')
could you try that and see if it makes a difference?

i tried the above statement but it returning error

Msg 4121, Level 16, State 1, Line 3

Cannot find either column "XEventData" or the user-defined function or aggregate "XEventData.XEvent.value", or the name is ambiguous.

i removed the XEventData. then it also not showing any result.

I am using SQL Server 2012 Devloper Edition.

Thanks

SQL006 SSCertifiable Points: 5071 More actions · Answer 4

Shawn Melton (12/9/2014)
That is rather odd, because I can find the deadlock event searching through the file target for the system health session.
Lowell, I did try your change and it made no difference. Were you referring to using the SQL Server 2008 R2 version of Jonathan's query:
select CAST(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[@name="xml_deadlock_report"]') AS XEventData (XEvent);
The above query still returns nothing on my SQL Server 2012 instance.
Alternative, is to just query the XEL files directly:
SELECT CAST([event_data] AS xml) AS event_data
FROM [sys].[fn_xe_file_target_read_file]('system_health*xel',NULL,NULL,NULL)
WHERE [object_name] = 'xml_deadlock_report';

thanks Shawn when i query the XEL files directly it displaying the deadlock details in xml.

But still the Jonathan's original query not displaying any result. Do i need to set any configuration in server level.

Thanks