One reason a lot of people don’t like Extended Events is because the output is in XML. Let’s face it, XML is a pain in the bottom. However, there are a bunch of ways around dealing with the XML data. The first, and easiest, is to ignore it completely and use the Live Data window built into SQL Server Management Studio.
I’ve written about the Live Data window before, and I’ve been using it throughout this series of posts on Extended Events. There’s a lot more to this tool than is immediately apparent. Today, we’re going to explore the basics around this tool
There are two easy ways to get the Live Data window open. The first, for any Extended Event session that’s running, you can right click on that session and select “Watch Live Data” from the context menu. That will open the following:
The other way to get at this is to simply open a *.XEL file. You can even open a file that’s live, being actively used. Either way, you get the events on the top and the details on the bottom. That’s it. Easy.
Now, this is one of those places where everyone starts to hate on Extended Events because, frankly, this looks like crap. You have to click on the event in the top to see the details in the bottom. It’s stupid.
So, don’t do that. Instead, as the link above describes, either right click on values in the details and select “Show Column in Table” from the context menu, or, even easier, open the “Choose Columns” window from the tool bar or the “Extended Events” menu choice in SSMS:
Now we can easily customize what we’re looking at so that the Extended Events information is displayed in a pleasing manner:
This is where things get really fun.
Pausing Live Data
If you have opened a file, this is necessary. On the other hand, if you have a live file, or an active session that you’re watching the data from, in order to do some other fun manipulation, we first have to pause the Life Data window.
Now, understand, this is a huge distinction. We’re going to use the tool bar, or the menu, to “Stop Data Feed”. This does not in any way affect the Extended Event session. It keeps running. All we’re doing is disconnecting from the live feed of the data. The tool bar looks like this:
You’re looking for that little red square. Hover over it and you can see what it will do. Clicking on it, stops the data feed. Just remember, this doesn’t stop the Extended Event session. This is a feature, not a bug.
With the data feed stopped, note the change in the tool bar:
We’ve enabled “Grouping” and “Aggregation”. It’s not just that. The Live Data window can now do all sorts of fun things. I’ll cover how the grouping and aggregation works in another blog post. Today, let’s keep things simple.
What if I wanted to quickly see which query had the longest duration?
Easy. I just click on the Duration column in the upper window. It immediately sorts the data ascending. If you want it descending, easy, click again:
Just like that, I’ve got the longest running query for the time frame that the information I have covers.
There’s a ton more that this window can do for you. I’ll cover more in additional blog posts.
If you decide to restart the live data feed, you can, but know that the window will clear to start capturing more data.
EDIT: I should also mention that SSMS remembers the columns that you selected so that the next time you open that session, it looks the same as the last time you had it open.
You Can’t Do That In Profiler
The Profiler gui will let you watch queries, and you can open a file with the gui. However, the gui can’t sort the data. If you want to sort the data, you must export it into a table and then you can run queries against it. The tool simply doesn’t support the functionality we get with Live Data in Extended Events.
Further, if you’re using Profiler to watch live data, understand, that uses a different caching mechanism on your server that can cause problems. This isn’t so of Extended Events. If you pause the live data in Profiler, you’re actually stopping the Trace event completely. As noted above, this is different in Extended Events.
Yes, Extended Events does capture into XML. That doesn’t mean we ever have to touch that XML. Further, because of all the functionality within the Live Data window, we can do some fun stuff with the data, quickly and easily, in ways that you simply can’t in Profiler.
Are there caveats around the Live Data Explorer? Yes. If you have really big files with millions of events, it doesn’t cope well. You can get around this by filtering data (another post for another day). Also, the SSMS gui can behave oddly at times (by oddly, I mean that literally, odd numbered screen magnification behaves weirdly).
However, for a simple way to quickly consume data and do things like identifying the longest running query, this tool is great!