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
Live Data
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!
you almost had me converted – but there a few bits in there I should maybe correct
from a profiler trace you don’t have to save it to a table to query it and sort it – have a look at sys.fn_trace_gettable
also if you pause the trace then you are stopping it, but there is nothing stopping you creating a script for the trace and running it without using profiler. Then It’s running a bit like a background task
you have converted me to XE, but i’m not writing profiler off yet 🙂 , we still have legacy instances out there that need a bit of debugging and TLC
Oh, I know you can use the extended procedure to load trace. For that matter, check out sys.fn_xe_file_target_read_file, which does the same thing, only for Extended Events.
And yes, you can, should, in fact it’s the best way, use scripted Trace events, not the Profiler. I’m just pointing out, if you DO use Profiler, this is how it acts. It’s very different from the behavior of Extended Events.
And yes, anything running 2008R2 or less, absolutely must use Trace/Profiler.
still a good article, and lovely to see that it can be displayed in a pleasing manner… it’s one that has been killing me. just never had time to go there.
I wish SSMS would save the chosen columns and would restore it when the session is opened again.
It does. It absolutely remembers what columns you selected and they’ll be available the next time you open it. In fact, I’m going to go edit the text to mention that fact. Now, that’s for sessions on a given machine. HOWEVER, you can save the setup (this would be on an upcoming blog post), and either reapply it as you move between machines, or, share it with others. It’s very functional.
As SSMS is simply a stream reader, it pauses the reading of the stream, not the event session itself.
I have Stream Readers in Powershell running 24/7 to send emails when certain events fire
Much cooler tech indeed.