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?

New Session- No WizardI 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:

It is still wizard enough

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:

plan guide successful

Not all events are created equal– some can slow down your SQL Server, just like in Profiler/SQLTrace. Be careful out there.

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:

Histogram Target

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.

Start Session

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:

doubleclick on the package

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.

voila-histogram-target-plan-guide-execution-count

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.

Previous Post
What is the PREEMPTIVE_DEBUG Wait in SQL Server?
Next Post
How to Ask Microsoft a SQL Server Licensing Question

1 Comment. Leave new

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}