How Do You Pick Events in Extended Events?

A while back I wrote about using AI to explore why people are not using Extended Events. You can read all about it here, but a short summary of the biggest blockers would be:

• Familiarity (more comfort in Trace or DMVs)
• Lack of Knowledge (just don’t know how it works)
• XML (I agree)
• Event Overload (there are just too many)

For this blog post I want to focus on the last one, Event Overload. There really are a lot of events in Extended Events. I don’t just think that’s a good thing. I think it’s a GREAT thing. However, I get it. I hit the same problem, regularly. Which events do I use to do thing that I’m trying to do? Except for blogs like this one, there’s not always a lot of guidance on these things. Microsoft has (good) documentation on Extended Events in general, but not on every event, plus, not a ton of guidance. (some though).

How To Pick Events in Extended Events

There are three basic pieces of information that I use to pick events in Extended Events. First, do I already know the event I want. Yeah, OK, cheating, but that’s actually how I pick events most of the time. Next, and this is where many will get started, based on the event name. Seriously. That’s it. Finally, many, most, events have a description you can use to understand, probably, what it does.

Let’s skip the cheating part where I know which events I want. How can I search the event names? In the GUI, it’s shockingly straight forward. Here’s the Events page of the “New Session” window:

You can see it, almost in the center of the screen, but I’ll zoom in a bit:

You see it right there. Type into where it says “Search Events” and you’re searching based on the “in” that by default is the “Event names only”

Yes, really. That’s it.

That’s generally where I start. Couldn’t be easier. Want to search in description? Fine, hit the drop down. You can search in:

• Event names only
• Event names and descriptions
• Event fields only
• All

So, let’s say we’re not sure how to capture blocked process information. Ok. Simple enough. I’m going to type in block in the search field:

I’ve rearranged the screen some to make everything look pretty, but you can see, right at the top, blocked_process_report. Now, it’s possible that some of the other events might be what we’re looking for, but probably not. The way to be sure is to look at the descriptions. Clicking on the “blocked_process_report” we see this in the description:

Not only do I get the description, which probably tells me what I want to know, but I also get the fields for the event and their description. Between all this, I know which event to pick.

You can also query for this information. Here’s an example, running in the master database:

SELECT dxo.name,
       dxo.description
FROM sys.dm_xe_objects AS dxo
WHERE dxo.object_type = 'event'
      AND dxo.name LIKE '%block%';

I can add other system views to pull together the event fields and their descriptions as well.

Does This Always Work to Pick Events in Extended Events?

No.

It mostly works, but… well, let me show you:

SELECT dxo.name,
       dxo.description
FROM sys.dm_xe_objects AS dxo
WHERE DATALENGTH(dxo.description) = DATALENGTH(dxo.name)
      AND dxo.object_type = 'event';

If we run this query, we get results that look like this:

I don’t know about the rest of you, but my teachers in school would have lifted my scalp if I used the same words to describe something as the words that name something. Sure, I suppose that thread_attached pretty much describes what’s going on, but, does it really? No. The good news is, out of 2,506 events in SQL Server 2022, only 157 are like this. That’s 0.6%, so I wouldn’t sweat it too much.

Conclusion

With one of the top blockers to adopting Extended Events being caused by just too darned many events, I’m hoping this helps unblock things, just a little. Will this guarantee that you can pick events in Extended Events the right way every time? Probably not. Experimentation and research are your best buddies. Still, this out to make it quite a bit easier.