The other day I wrote about that torrid love/hate relationship DBAs tend to have with working with XML. In that same post, I promised I would have a follow up post about XML in Extended Events.
Well, today I present to you another opportunity for you to renew your love/hate relationship with XML.
In the previous post (which you can read here), I discussed just one facet of shredding the XML related to Extended Events. Today, we have “actions” to discuss. I am not going to get into what an “action” is today. I will save that for a near future post.
For the shredding of the actions, I will use the TreeHuggerCPU Event Session I used in the last article (for the sake of consistency). The session doesn’t necessarily need to be running. I will just pull the actions related to the session from the metadata accessible via the system catalog views.
Should the session be running (and in my case it is for demonstration purposes), I could open the session data and view it as XML and see something like the following:
I have highlighted two different types of nodes available in the XML data. In the previous article, I discussed the “data” nodes and I have highlighted that in red here. Today, we are talking actions, and those are highlighted in green this time around. It is the “action” nodes that we will be shredding via the following script.
DECLARE @EventName VARCHAR(64) = NULL --'sp_statement_completed'
, @EventSession VARCHAR(128) = NULL --'Deadlock' --NULL
;
/* auto generate the xml associated to an event session action deployed to the server */SELECT p.name AS package_name
,o.name AS action_name
,',event_data.value(''(event/action[@name="' + esa.name + '"]/value)[1]'', ''' +
CASE
WHEN o.type_name = 'guid'
THEN 'uniqueidentifier'
WHEN o.type_name = 'boolean'
THEN 'bit'
WHEN o.type_name = 'binary_data'
THEN 'varbinary(max)'
WHEN o.type_name = 'callstack'
THEN 'varbinary(max)'
WHEN o.type_name = 'filetime'
THEN 'varbinary(max)'
WHEN o.type_name = 'cpu_cycle'
THEN 'varbinary(max)'
WHEN ISNULL(o.type_name,'') = ''
THEN NULL
WHEN o.type_name <> 'unicode_string'
AND o.type_name <> 'ansi_string'
AND o.type_name <> 'ptr'
AND o.type_name NOT LIKE '%int%'
THEN o.type_name
WHEN o.type_name LIKE '%int%'
THEN 'int'
ELSE 'varchar(max)' END + ''') AS ' + esa.name +'' AS ActionXML
,ses.name AS EventSessionName
, ese.name AS EventName
,o.description
FROM sys.dm_xe_packages AS p
INNER JOIN sys.dm_xe_objects AS o
ON p.guid = o.package_guid
INNER JOIN sys.server_event_session_actions esa
ON o.name = esa.name
INNER JOIN sys.server_event_sessions ses
ON esa.event_session_id = ses.event_session_id
INNER JOIN sys.server_event_session_events ese
ON esa.event_session_id = ese.event_session_id
AND ese.event_id = esa.event_id
WHERE o.object_type = 'action'
AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
AND (p.capabilities IS NULL OR p.capabilities & 1 = 0)
AND ese.name = ISNULL(@EventName,ese.name)
AND ses.name = ISNULL(@EventSession,ses.name)
ORDER BY esa.name, ses.name, ese.name
;With this script, I can either search for all actions tie to an XE Session, for the actions tied to one event within a Single XE Session, for all actions tied to a specific event across multiple sessions, or for all actions that are tied to any event tied to any event session deployed to the server.
Combine this with the previous script and suddenly all of that XML just got several times easier.
