HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Shreded system_health ExEvent not returning all node information

Submitted by: @import:stackexchange-dba··
0
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

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

Context

StackExchange Database Administrators Q#104216, answer score: 3

Revisions (0)

No revisions yet.