Shredding XML in XEvents

One of the biggest pains with Extended Events is the thing we love to hate – XML. XML is so foreign to many DBAs. It’s not relational and often brings nightmares of parsing and performance issues.

Despite that, Extended Events takes advantage of XML much like we have seen in so many other areas of SQL Server. You might be familiar with execution plans, SSRS, SSIS, or maybe even the ring buffer. If you look hard enough, you will find XML within the database engine. Whether you love or hate it, you still have to deal with it. Today, I want to dive into a means of dealing with XML, as far as Extended Events is concerned.

Mad Scientist Lab

Let’s head on over to the lab to dive into XML head first. I will be using a session as an example of which I have previously written – here.

ALTER EVENT SESSION TreeHuggerCPU 
ON SERVER 
STATE = START;
GO
SELECT CONVERT(XML, t2.event_data) AS event_data
FROM ( SELECT target_data = CONVERT(XML, target_data)
FROM sys.dm_xe_session_targets t
INNER JOIN sys.dm_xe_sessions s
ON t.event_session_address = s.address
WHERE t.target_name = 'event_file'
AND s.name = 'TreeHuggerCPU'
) cte1
CROSS APPLY cte1.target_data.nodes('//EventFileTarget/File') FileEvent ( FileTarget )
CROSS APPLY sys.fn_xe_file_target_read_file(FileEvent.FileTarget.value('@name',
'varchar(1000)'),
NULL, NULL, NULL) t2;
ALTER EVENT SESSION TreeHuggerCPU 
ON SERVER 
STATE = STOP;
GO

If the session is already deployed – great. If not, you may need to create it to execute (successfully) these scripts. Note that I am starting the session and then fetching some data and then stopping the session. To see some data, you may want to wait a few cycles before stopping the event session.

The sole purpose is just so I can take a look at the session data in XML format. Now that I have some data, I would see something that might look like the following:

<event name="perfobject_processor" package="sqlserver" timestamp="2015-08-26T02:47:28.993Z">
  <data name="percent_c1_time">
    <value>4312500965</value>
  </data>
  <data name="percent_c2_time">
    <value>260553380830</value>
  </data>
  <data name="percent_c3_time">
    <value>0</value>
  </data>
  <data name="percent_dpc_time">
    <value>529726562</value>
  </data>
  <data name="percent_idle_time">
    <value>274891621093</value>
  </data>
  <data name="percent_interrupt_time">
    <value>369296875</value>
  </data>

For today’s lab, I just want to focus on the “data” node while saving the attributes of the event node, and the action node(s) for another discussion.

The “data” node happens to be the data that is directly tied to an Extended Event event. When looking at the event metadata, this would be called the event columns. Knowing that all of these columns follow a fairly standard format can make it a bit easier to figure out how to query this data. One of the daunting things with XML is figuring out how to query the XML data to make it more relational – a format we may be more accustomed to seeing (as DBAs).

Due to the daunting task of figuring out how to query the XML and because it is a pretty decent format for consumption, I decided to simplify the entire process. Why not write some code that will write the XML parsing code for me? And that is what we have here.

/* auto-gen the xml for the columns for your XE sessions */DECLARE @EventName VARCHAR(64) = NULL --'query_post_execution_showplan'
,@ReadFlag VARCHAR(64) = 'readonly' --NULL if all columntypes are desired
,@SessionNameVARCHAR(128) = NULL --'system_health' --NULL if all Sessions are desired
SELECT oc.OBJECT_NAME AS EventName
,oc.name AS column_name, oc.type_name
,',event_data.value(''(event/data[@name="' + oc.name + '"]/value)[1]'',''' + 
CASE 
WHEN ISNULL(xmv.name,'') = ''
AND oc.type_name = 'guid'
THEN 'uniqueidentifier'
WHEN ISNULL(xmv.name,'') = ''
AND oc.type_name = 'boolean'
THEN 'bit'
WHEN ISNULL(xmv.name,'') = ''
AND oc.type_name <> 'unicode_string'
AND oc.type_name <> 'ansi_string'
AND oc.type_name <> 'ptr'
AND oc.type_name NOT LIKE '%int%'
THEN oc.type_name
WHEN ISNULL(xmv.name,'') = ''
AND oc.type_name LIKE '%int%'
THEN 'int'
ELSE 'varchar(max)' END + ''') AS ' + oc.name + '' AS ColumnXML
,oc.column_type AS column_type
,oc.column_value AS column_value
,oc.description AS column_description
,ca.map_value AS SearchKeyword
FROM sys.dm_xe_object_columns oc
-- do we have any custom data types
OUTER APPLY (SELECT DISTINCT mv.name FROM sys.dm_xe_map_values mv
WHERE mv.name = oc.type_name
AND mv.object_package_guid = oc.object_package_guid) xmv
--just get the unique events that are tied to a session on the server (stopped or started state)
CROSS APPLY (SELECT DISTINCT sese.name,ses.name AS SessionName
FROM sys.server_event_session_events sese
INNER JOIN sys.server_event_sessions ses
ON sese.event_session_id = ses.event_session_id) sesea
--keyword search phrase tied to the event
CROSS APPLY (SELECT TOP 1 mv.map_value
FROM sys.dm_xe_object_columns occ
INNER JOIN sys.dm_xe_map_values mv
ON occ.type_name = mv.name
AND occ.column_value = mv.map_key
WHERE occ.name = 'KEYWORD'
AND occ.object_name = oc.object_name) ca
WHERE oc.column_type <> @ReadFlag
AND sesea.name = oc.object_name
AND oc.object_name = ISNULL(@EventName,oc.object_name)
AND sesea.SessionName = ISNULL(@SessionName,sesea.SessionName)
ORDER BY sesea.SessionName,oc.object_name
;
GO

This script will take an event session name, an Extended Event event name, or a combination of both (imagine having multiple events tied to a session) to produce the XML strings automagically. This script does only produce some pretty generic column aliases, so that part is left to the user of the script to edit after generating the XML parse statements.

With this script, I can quickly retrieve all of the XML parse statements for all of the data nodes within the session or event that I specify. This can significantly reduce the amount of time taken to produce a usable script to consume the event session data.

This is just the script to parse the event data. If there are actions tied to the session, this will not produce the statements for those actions. The script for that will be provided in a future article. Stay tuned!