Invalid XML in Extended Events xml_deadlock_report output - by Jonathan Kehayias

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


5
0
Sign in
to vote
ID 404168 Comments
Status Closed Workarounds
Type Bug Repros 3
Opened 1/22/2009 10:42:39 PM
Access Restriction Public

Description

The xml_deadlock_report provides an invalid xml document in the value output.
Sign in to post a comment.
Posted by Robert Heinig III on 7/31/2015 at 5:19 AM
Ooooold hat, but I have a problem letting the demo scripts go in light of SQL 2012/14/16, just in case someone else stumbles upon this.

That "select .. where .. 'xml_deadlock_report'" will, on a more modern 2012sp2 box, run very inefficiently by default. Also, the xml cast shown is now based on wrong assumptions.
Replace the inner where with "where s.name = N'system_health' and st.target_name = N'ring_buffer'", omit the first slash from the nodes XPath, and use ".query('./data[@name="xml_report"]/value/deadlock')" for the XML fragment extraction. Why?

- SQL now comes with an 'event_file' target by default, and including that in the xquery takes "forever" (even though that target has no <RingBufferTarget>). The target_name filter reduces execution time on my dev box from >10min to 1s.
- // is to / like a scan to a seek (an oversimplification, but // should be slower anyway), and it is known that the <RingBufferTarget> is at the root.
- .value() extracts and concatenates all CData under the addressed node, it does not yield XML unless there's XML wrapped in escaped form into CData - which was the case in 2008R2SP3 but isn't in 2012+. Casting that to XML surprisingly (or not, as SQL XML allows fragments, not only well-formed XML, and pure CData is a valid fragment) succeeds as long as the parsed executionStack/frame/text() doesn't contain the wrong markup characters. query() is the correct way to extract the desired xml fragments.
Posted by John Bell on 11/5/2010 at 7:33 AM
This problem is also in SQL Server 2008 R2 and fixed in CU1
Posted by debettap on 9/30/2010 at 11:43 AM
This issue was fixed in SQL Server 2008 SP1 CU6 (see http://support.microsoft.com/kb/978629 for details).

Thanks,

Peter DeBetta
Posted by Jerome [MSFT] on 1/27/2009 at 8:46 AM
Thanks for bringing this to our attention. This issue is a side effect of changes made in the deadlock monitor for SQL 2008. The event raised via XEvent reflects the change in the deadlock XML that multiple victims per cycle can be found, where as SQL 2005 and before could only find one victim per cycle.

Unfortunately there is a problem in the xml generation that a block of XML is omitted. We are aggressively perusing a fix for this issue.

Thanks,
Jerome Halmans
SQLOS
Posted by Jonathan Kehayias on 1/22/2009 at 10:47 PM
I uploaded scripts to produce a deadlock. First run the setup script, then run the Updater in one query window and the selector in another. Use the query included in the description below to retrieve the deadlock reports from XEvents.

I also included the output graph from sql trace and from XEvents as txt files for this problem.