THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Michael Zilberstein

Parsing Extended Events xml_deadlock_report

Jonathan Kehayias and Paul Randall posted more than a year ago great articles on how to monitor historical deadlocks using Extended Events system_health default trace. Both tried to fix on the fly bug in xml output that caused failures in xml validation. Today I've found out that their version isn't bulletproof either. So here is the fixed one:

SELECT CAST(xest.target_data as XML) xml_data, *
INTO #ring_buffer_data
FROM 
  
sys.dm_xe_session_targets xest
  
INNER JOIN sys.dm_xe_sessions xes on xes.[address] = xest.event_session_address
WHERE 
  
xest.target_name = 'ring_buffer' AND 
  
xes.name = 'system_health'
GO

;WITH CTE( event_name, event_time, deadlock_graph )
AS
(
  
SELECT
      
event_xml.value('(./@name)', 'varchar(1000)') as event_name,
      
event_xml.value('(./@timestamp)', 'datetime') as event_time,
      
event_xml.value('(./data[@name="xml_report"]/value)[1]', 'varchar(max)') as deadlock_graph
  
FROM #ring_buffer_data
      
CROSS APPLY xml_data.nodes('//event[@name="xml_deadlock_report"]') n (event_xml)
  
WHERE event_xml.value('@name', 'varchar(4000)') = 'xml_deadlock_report'
)
SELECT event_name, event_time
   
CAST(
      
CASE 
          
WHEN CHARINDEX('<victim-list/>', deadlock_graph) > 0 THEN
              
REPLACE (
                  
REPLACE(deadlock_graph, '<victim-list/>', '<deadlock><victim-list>'),
                  
'<process-list>', '</victim-list><process-list>'
          
ELSE
              
REPLACE (
                  
REPLACE(deadlock_graph, '<victim-list>', '<deadlock><victim-list>'),
                  
'<process-list>', '</victim-list><process-list>'
      
END 
   AS
XML) AS DeadlockGraph
FROM CTE
ORDER BY event_time DESC
GO

The difference as you can see is in the check whether '<victim-list>' node is empty (CHARINDEX('<victim-list/>', deadlock_graph) > 0). My system_health session caught some weird deadlocks. According to trace they're caused by intra-query parallelism when threads of the same session deadlock one another. Until now it is rather a rare type of deadlock but nothing strange - Bart Duncan and Craig Freedman blogged about it long ago. What is really strange is that this deadlock isn't caught by 1222 trace flag - error log remained empty. And - probably connected to previous item - victim list is empty. So instead of opening node '<victim-list>' we receive an empty one: '<victim-list/>'. I'm not sure such a deadlock deserves any attention if it occurs once in a while but I sure want to be able to parse xml_deadlock_report events without failures.

Published Monday, May 10, 2010 5:31 AM by Michael Zilberstein
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Jonathan Kehayias said:

Michael,

What is interesting is that you report that an intraquery parallelism deadlock isn't captured by TF 1222, which is contrary to my own experiences recently with that type of deadlock as well as experiences in the past.  I know for a fact that in SQL 2005 1222 will output for intraquery parallelism deadlocks, but I have yet to have a problem with them in SQL 2008, mainly because I have changed how I configure my servers for 'max degree of parallelism'.  If the TF 1222 didn't capture the deadlock I'd call that a bug and something that should be fixed.  The good thing is that the deadlock XML bug in XEvents has been fixed and will be released in Service Pack 2, per the comments on the connect bug which has been closed as fixed.

May 9, 2010 10:16 PM
 

Michael Zilberstein said:

Jonathan,

Agree with every word - I know that TF 1222 used to catch intra-query parallelism deadlocks, have seen it multiple times on my systems. I'm actually considering opening bug on connect - I wrote my post at ~4:00AM local time, so postponed it to get a little nap :-).

May 10, 2010 12:18 AM
 

Bill said:

I thought SQL 2008 CU1 package fixed that deadlock xml bug.

Does SQL 2008 SP1 include the fix for that?

May 17, 2010 6:42 PM
 

The Rambling DBA: Jonathan Kehayias said:

Today’s post was originally planned for this coming weekend, but seems I’ve caught whatever bug my kids

December 13, 2010 10:13 PM
 

Scotty Boy said:

Msg 9436, Level 16, State 1, Line 2

XML parsing: line 5, character 15, end tag does not match start tag

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)

looks to me like the XML file is truncated prematurely

October 20, 2011 5:11 PM
 

mark said:

I also get this

Msg 9436, Level 16, State 1, Line 2

XML parsing: line 6, character 15, end tag does not match start tag

any ideas?

April 24, 2012 6:25 AM
 

Eric Zierdt said:

I've just noticed that when I have multiple victimProcess nodes under the victim-list node, that the first one doesn't terminate (no /> at the end, just >)  This causes the XML parsing: line 6, character 15, end tag does not match start tag error that people are reporting.  Not sure yet if there is a way to catch this and correct it..i'm still working on it...I'm not seeing anyone else discussing this anywhere.

<deadlock-list>  <deadlock><victim-list>   <victimProcess id="processb8034c8">    <victimProcess id="process753c508"/>    </victim-list><process-list>

...

May 31, 2012 1:51 PM
 

Robert Berndt said:

I also get this

Msg 9436, Level 16, State 1, Line 2

XML parsing: line 6, character 15, end tag does not match start tag

Would love a solution!

June 28, 2012 7:25 AM
 

Igor Santos said:

I'm having the same issue that Eric Zierdt and Robert Berndt are experiencing.

Msg 9436, Level 16, State 1, Line 2

XML parsing: line 6, character 15, end tag does not match start tag

September 6, 2012 11:31 AM
 

M. Duverger said:

Hi guys,

I'm having the same issue that Eric Zierdt, Robert Berndt and Igor Santos are experiencing.

Msg 9436, Level 16, State 1, Line 2

XML parsing: line 6, character 15, end tag does not match start tag

Is there any solution yet?

September 27, 2012 9:12 AM
 

Lucas Kartawidjaja said:

I also got the same issue. As Eric Zierdt mentioned, the problem is with multiple victimProcess nodes.

I modified a script that Jonathan Kehayias' wrote on SQL Server Central. The script can be found on:

http://www.lucasnotes.com/2012/11/deadlock-graph-xml-from-extended-events.html.

Hopefully it can be helpful.

November 10, 2012 9:29 PM
 

Lorrin Ferdinand said:

Hi,

I would like to get a gauge on what the performance impact of running the xml_deadlock_report query to extract just that event is?  I started running one on a system that was multi-core with 64 GBs of RAM and after 2 mins 15 seconds - I killed it.  Should it take that long to run?

November 15, 2012 12:41 PM
 

Ali said:

Hi Lucas,

I also get error with your script.

Msg 9436, Level 16, State 1, Line 1

XML parsing: line 4, character 15, end tag does not match start tag

This Deadlock from DMV really not working for me. Not much usefull.

November 16, 2012 11:01 AM
 

Ali said:

Figured out another way to get information. First select into temp table without converting to XML.

select XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as DeadlockGraph

into tempdb.dbo.deadlock

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'

--Next CAST into xml

select CAST(deadlockgraph as XML) from tempdb.dbo.deadlock

November 16, 2012 1:45 PM
 

Dali said:

Thanks for nice deadlock report I wonder why there is no db name mentioned in the report?

March 8, 2013 12:25 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement