Tip for Learning Extended Events – Use “New Session” (Not “New Session Wizard”)

Extended Events have become one of my favorite tools for learning about tuning SQL Server. Whenever I wonder how something works, I can fire up a test instance and play around with XEvents. It’s always interesting, and at least 5% of the time I end up learning what I intended.

Not everything is sunshine and rainbows. Finding the right events and making sure you understand the output can be really frustrating. And you want a SQL Server 2012 or 2014 test instance to do this. The events are much richer and there’s an interface to help you along.

But like a lot of things, the interface isn’t all that intuitive.

New Session, or New Session Wizard?

I used to click on “New Session Wizard”, simply because I had no idea what the difference was between the two options, and it was first in the list.

The “Wizard” option gives you some templates that you can choose to customize. If you’re just starting out, that could be interesting to use on your test instance.

But it always left me frustrated that I couldn’t use some of the more advanced options in Extended Events. I felt like I was just scratching the surface, and trying to code sessions from scratch using Books Online was slow and painful.

“New Session” Just Gives You a Better Wizard

When you click new session, it turns out that you still get a wizard. It starts out asking you for a session name and some basic properties:

Then you can choose your events. I decided I wanted to test some plan guides, and see if I could use Extended Events to count how many times a plan guide was used. (This is something that the DMVs and perf counters don’t make accessible.) I added the plan_guide_successful event:

Extended Events Targets: I Can Pick a Histogram!

The “Data Storage” tab is where this gets good. I was always frustrated when I was using the Wizard that I had limited choices about how to set up a target. If you use this version of the Wizard (the Un-Wizard Wizard?), then you get to pick your target type and it’ll help you script that out.

In this case, I want to use the histogram target. I don’t care when the plan guides were executed– I just want a count of which ones are being used. This can help keep my trace lightweight, plus if that’s the only information I want then I don’t have to do any complicated grouping on the output.

The GUI is nice and friendly and lets me say I want my “Buckets” to be on the plan_guide_name field:

After I create my session, it shows up in my Extended Events session list in the SSMS Object Explorer. I can right click on it and script it out, start it, stop it, etc.

I Ran My Plan Guides Test and … It Worked!

I fired up my session, created some plan guides, and ran some queries against my restored copy of the StackOverflow database.  After building up a bit of data, I just double-clicked on package0.histogram to open the results so far:

And it worked! I created a ‘template’ plan guide that I executed via a HammerDB workload— so that got hit a ton. Then I created two other plan guides that I just ran twice and once, respectively.

Extended Events Aren’t Always This Easy

Putting together this little example went smoothly. Surprisingly smoothly. So smoothly that I really want to quit while I’m ahead.

Lies we tell ourselves: “This should be simple to do with Extended Events.”

— Kendra Little (@Kendra_Little) December 14, 2014