Sure, you can right click on a running session for Extended Events and open the Live Data window, but are there other ways of observing what Extended Events is capturing?
Yes.
Extended Events Output
Let me just get out of the way immediately, there’s probably a way to do this programmatically with DBATools. I need to dig in there, and I’ll post what I find. But what can we do right now? SQL Server Management Studio (SSMS).
Here are all the sessions currently on my test/demo machine:
Some of these are built in, system_health for example. Most of the rest are mine. So, how can we see what they do? Well, notice that every one has a little plus sign next to it:
If we click on the plus sign, we see some more information:
Those are the output targets. One is going to a file. The other is going to the ring buffers. If I double click on the file this opens up:
Yeah, that’s the Live Data Window. We are once again observing Extended Events. Oh, and I need to update Prompt.
But, what happens when I double click the ring buffers one?
What the heck is that? Let’s click on it:
Oh gag. It’s XML. Yeah, it’s the output from Extended Events, to the Ring Buffer. What about the other outputs? What does event counter look like?
That particular session is capturing rpc_completed and sql_batch_completed. Those counts will be visible in PerfMon under the sqlserver package. Also, I skipped passed it up above, but if we right click, we can set a refresh interval on this view into the data:
And… pair matching? Can we observe Extended Events pair matching?
Oh yes. And it refreshes as well.
What about histograms?
Yep. This one is a count of a single stored procedure.
And sync targets?
I don’t know. I’ve never used that in anger. Maybe I should go learn and then put up a blog post about it.
Conclusion
Pretty simple conclusion, there are a bunch of ways to look at the data coming out of Extended Events.
Hey, wanna learn some more about Extended Events? How about some of the other tools that are built in to SQL Server that can help us tune queries? I’m going to be in Gothenburg for DataSaturday Gothenburg where I’ll be putting on a full day precon on September 6, 2024, on that exact topic. Please follow this link and join me.
I’m still waiting to have an easy way to actually query the extended results file and have the system generate the available attribute xquery statements (cfr Object explorers capability to generate a select statement )