Extended Events: Embrace the XML

While XML is, without a doubt, a giant pain in the bottom, sometimes, the best way to deal with Extended Events is to simply embrace the XML.

Now, I know, just last week, I suggested ways to avoid the XML. I will freely admit, that is my default position. If I can avoid the XML, I will certainly do it. However, there are times where just embracing the XML works out nicely. Let’s talk about it a little.

Copy This Query

I have a theory. It goes like this:

There has only, ever, been a single XML query written from scratch. All other XML queries are just copied from that one and then edited to do what is necessary

OK. Maybe that’s not entirely true. In fact, I know it’s not true because I have, myself, written a couple of XML queries from scratch. And what a horror show that was. Also, there are books that can help.

However, we’re not talking about XQuery & XPath and all that nearly as much as we’re talking Extended Events. While we have ways to avoid the XML, sometimes it makes sense to go right after it. For example, if you want to look at the deadlock graph in an AWS RDS SQL Server database, you could do this:

WITH fxd
AS (SELECT CAST(fx.event_data AS XML) AS Event_Data
    FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) AS fx )
SELECT dl.deadlockgraph
FROM
(
    SELECT dl.query('.') AS deadlockgraph
    FROM fxd
        CROSS APPLY event_data.nodes('(/event/data/value/deadlock)') AS d(dl)
) AS dl;

Why do this instead of opening up the file in the Live Data window as I showed in my last post? Because I know exactly what I want and I don’t need to do a bunch of extra steps to get it. I can just go directly after the data I’m interested in, with a single query.

Alternatively, for another example, what if I wanted to set up monitoring or alerts to spot unauthorized people making changes to the system? That screams query. You don’t want to try to deal with that using DBATools. Instead, you could go right after the information like this:

SELECT COUNT(*) AS ObjectsAltered
FROM sys.dm_xe_session_targets AS xet
INNER JOIN sys.dm_xe_sessions AS xes
ON xes.address = xet.event_session_address
CROSS APPLY
(SELECT CAST(xet.target_data AS XML)) AS target_data_xml(xml)
CROSS APPLY target_data_xml.xml.nodes('//RingBufferTarget/event') AS xed(event_data)
WHERE xes.name = 'UnauthorizedObjectChanges'
AND xet.target_name = 'ring_buffer'
AND DATEDIFF(
MINUTE,
CONVERT(
DATETIME2,
SWITCHOFFSET(
CONVERT(
DATETIMEOFFSET,
xed.event_data.value('(@timestamp)[1]', 'datetime2')
),
DATENAME(TzOffset, SYSDATETIMEOFFSET())
)
),
GETDATE()
) < 30; –metadata changes within the last thirty minutes

Of course, you have to set up the session for this to work. However, as you can see, you can quickly pull the necessary information using a query.

And here’s the rub. Copy these queries. Modify them to do what you want. When you hit that place where a query makes sense, do a search on the particular events you’re capturing. See if someone else has already written a query for that event. Copy it. Don’t create a new XML query (there’s only really one anyway, right).

Conclusion

I sincerely believe that most of the time using the Live Data window or, DBATools, to avoid the XML is the right, and easiest, way to go. However, as with the examples above, you may hit a spot where querying that data directly is the appropriate response. In those instances, just embrace the XML. Yes, it’s a pain. There’s no denying it. Yet, leaning on the community as a way to alleviate that pain is a great approach. Then, you get the best of both worlds. Most of the time, no XML, sometimes, enough XML to get the job done. Working with Extended Events is more complicated in some ways than working with trace events, but it doesn’t have to be harder.