patternMinor
Shreded system_health ExEvent not returning all node information
Viewed 0 times
allnodereturningsystem_healthnotexeventshrededinformation
Problem
SQL Server is not returning all node information when I shred the deadlock XML from the 2012 system_health Extended event.
When I run the following query
at our Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 instance all node information is returned along with descriptions i.e.
However when I run it against our Microsoft SQL Server 2012 - 11.0.5548.0 instance all node information and descriptions are stripped out and only the text is returned i.e
What might cause this? The full XML hierarchy is outputted at our 2012 instance if I remove the
When I run the following query
SELECT CAST(event_data.value('(event/data/value)[1]','varchar(max)') AS XML) AS DeadlockGraph
FROM (
SELECT XEvent.query('.') AS event_data
FROM ( -- Cast the target_data to XML
SELECT CAST(target_data AS XML) AS TargetData
FROM sys.dm_xe_session_targets st
INNER JOIN
sys.dm_xe_sessions s
ON s.address = st.event_session_address
WHERE name = 'system_health'
AND target_name = 'ring_buffer'
) AS Data -- Split out the Event Nodes
CROSS APPLY
TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData ( XEvent )
) AS tab ( event_data )at our Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 instance all node information is returned along with descriptions i.e.
Proc [Database Id = 26 Object Id = 1641213047]
...
...
...
However when I run it against our Microsoft SQL Server 2012 - 11.0.5548.0 instance all node information and descriptions are stripped out and only the text is returned i.e
Proc [Database Id = 26 Object Id = 1641213047]What might cause this? The full XML hierarchy is outputted at our 2012 instance if I remove the
SELECT CAST(event_data.value('(event/data/value)[1]','varchar(max)') part from the query.Solution
According to http://www.red-gate.com/community/books/accidental-dba [Troubleshooting SQL Server: A Guide for the Accidental DBA] this looks to be by design.
Changes to Extended Events in SQL Server Denali
At the time of the
final edits of this chapter, SQL Server Denali CTP3 was released, with
changes associated to how the Extended Events targets store XML data
inside of the value element of the Event XML output. Listing 7.5
shows the use of the .value() method from XML in SQL Server, but in
Denali CTP3, a .query() method has to be used to retrieve the
deadlock graph from the Event XML output.
Additional information : Extended Events Changes in SQL Server 2012 – Event XML for XML data elements
Changes to Extended Events in SQL Server Denali
At the time of the
final edits of this chapter, SQL Server Denali CTP3 was released, with
changes associated to how the Extended Events targets store XML data
inside of the value element of the Event XML output. Listing 7.5
shows the use of the .value() method from XML in SQL Server, but in
Denali CTP3, a .query() method has to be used to retrieve the
deadlock graph from the Event XML output.
Additional information : Extended Events Changes in SQL Server 2012 – Event XML for XML data elements
Context
StackExchange Database Administrators Q#104216, answer score: 3
Revisions (0)
No revisions yet.