December 9, 2014 at 6:44 am
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
December 9, 2014 at 7:02 am
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
December 9, 2014 at 8:25 am
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
December 9, 2014 at 10:17 pm
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
December 9, 2014 at 10:24 pm
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
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy