Extended Events: Avoid the XML

One story I hear over and over goes like this: I tried setting up Extended Events, but then I saw the output was XML so I stopped.

Look, I get it. I don’t like XML either. It’s a pain to work with. It’s actively difficult to write queries against it. If there weren’t a ton of ways to avoid the XML, yeah, I would never advocate for Extended Events. However, here we are, I have ten pages of blog posts that at least mention Extended Events. Why? Because I avoid the XML (most of the time). Lots of other people do as well. You can too. Let’s see how.

Live Data Window

I have a video that goes into this in detail right here. But the core concept is simple. If you right-click on any Extended Events session inside SSMS, if it’s an actively running session, you can see a menu choice: Watch Live Data. Clicking on that opens up the Live Data window. You can see this in action in the video above.

Now, we do hit a problem here, almost as bad as the XML. Here’s the output when you first open it:

The top part of the screen are all the events. In this case, I’m capturing queries through sql_batch_completed and rpc_completed. Selecting any one event on the top of the screen will show you the details of that event in the bottom of the screen.

Admission time. I used Extended Events just like this for well over a year, and I was happy. It’s embarrassing. Why? Because that isn’t how you use this. It’s a generic viewer for any kind of event. However, it doesn’t have to stay that way. Instead, I can customize the view for my session. What’s more, that customization is saved, automatically, and will be reused.

So, how does that work? Two ways.

First, you can right-click in the details on any one of those fields. Doing so opens up this menu:

Click on Show Column in Table, and that field is added to the screen above the details.

Another way is to right-click in that top view, right on the top of the grid (maybe on the timestamp column, not that it matters, just at the top of the grid). You’ll get a menu that looks like this:

Click on the “Choose Columns…”. This opens a new window:

I’m not going into the details of how to use this. Honestly, I think it’s pretty self-explanatory. On the left you have all the data being captured by the events defined in your session. Move them to the right to create your own customized view. For example, here’s the view I use most often when looking at query performance data:

Now, suddenly, this is useful. I can see the query, either batch or statement, the duration, reads, CPU, writes, and all laid out in a nice neat format. What’s more, once you’ve done this, you can still click on any given line and see the details. However, now you get all sorts of additional functionality. You can search these columns. You can sort, group by, and aggregate. You can filter this data by time, or on any column and value. In short, you have a tool for exploring the data, completely avoiding the XML in its entirety.

Now, millions of rows, make the tool crawl. So it’s only good for slightly smaller chunks of data. However, you can watch data live, as I walked you through in this demo. Alternatively, you can open files directly. You can create a customized view of your data and then reuse that customization with no extra work at all. You can even save it and pass it to coworkers or put it on multiple machines.

You can avoid the XML.

DBATools

If you don’t already know about DBATools, well you are in for a treat. Follow this link and experience happy goodness. This is a community-driven set of PowerShell tools that will absolutely change the way you administer and work with SQL Server. And it won’t cost you a penny.

And, best of all, DBATools has had Extended Events functionality for years. And not just one or two commands. A whole slew of functionality to help you work with and support Extended Events:

While all these tools are going to help you out, we’re going to focus on one and only one: Read-DbaXEFile. I’m not going into extensive code demos here. One, it’s too easy to use. Two, DBATools goes out of their way with examples and code samples, so I’m just going to link to their documentation right here.

Now, using DBATools, you don’t have to try to figure out XPath (it’s no fun, trust me). Instead, you can query the file directly into human-readable form. What’s more, since you’re already in PowerShell, just pipe it out to any other format or location that you can imagine. Also, Powershell really isn’t bothered by millions of data points. That makes this a great resource if you have large results from Extended Events sessions.

You can avoid the XML.

Conclusion

Just using these two methods, and nothing else, you can avoid the XML. So, if your first experience was looking at XML and you’ve punted on the whole concept of Extended Events, time to come around again. Extended Events are not hard to use. They’re really simple. In fact, once you have these two tools in your pocket, Extended Events become extremely powerful and extremely easy.

I don’t normally ask this, but do me a favor, share this one with your coworkers, especially your more senior coworkers. Far too many of them are avoiding Extended Events. Some of them had a bad experience in 2008. Some of them saw, or heard about, the XML, so they punted. Others just heard people saying bad things, so they never even bothered to look. They need to get reintroduced to the topic because Extended Events are here to stay. All new functionality within SQL Server have Extended Events, no Trace Events at all. Azure SQL Database only lets you use Extended Events. There’s more functionality than in Trace, and finally, these things put less of a load on the engine. Extended Events are good.

Don’t just take my word for. Listen to all these people.

So, Extended Events really are the future. It’s time that everyone found out, the future is actually really easy and doesn’t involve XML.