Extended Events and Performance Tuning Knowledge

I’m working on updating my book, Query Performance Tuning Distilled, so that it reflects the new things available in SQL Server vNext:Denali. I’m going through the first chapters that are all about gathering information about your systems. Performance tuning is all about building up knowledge of how the system is working in order to understand what you need to change in order to improve it. I’m surprised by how much hasn’t changed. But some of the changes are fundamental and huge. Let’s talk huge. Extended Events is huge.

Extended Events came out in SQL Server 2008, but very few people, myself included, paid much attention. Those who did found the implementation awkward and confusing. Only a few people persevered enough to discover just how powerful and amazing these things are. Which is why most anyone who wants to learn about extended events should plan on starting at one place, Jonathan Kehayias’ blog. Yeah, the Books Online help get you started, but Jonathan really makes it all take off.

But why is this huge? I’m not going into a low-level discussion here, because frankly, I’m incapable, for that go back to Jonathan. What I will say is that you have a mechanism that captures events at their source instead of after the fact. That makes it faster and causes less impact on your system. Further, you can put filters in place at the event level that make it so that you only capture the information you want, when you want it. In short, it’s about control, reduced impact and increased knowledge.

So why write about it now? Because Denali has finally provided a GUI that simplifies the process of setting up extended events. There’s a wizard for building extended events sessions, and a session management GUI that you can use to manage them on your own. Basically, you have a way to get past the confusing and odd TSQL implementation and start using these things.

Check it out:

It really does provide everything you need to get going with extended events… or does it? There’s still the issue of knowing how these things work and what they do. The GUI above enables you, but it doesn’t provide much guidance. For example, there’s a very enticing little action (stuff collected from events are called actions or fields) called sql_text. When setting up my first, simple, session (the one you see above), I saw that action and thought, ah-ha, there’s my collection point for getting the sql batch and the procedure call. But then, on testing, it didn’t work. After a plaintive call for help on Twitter (hash tag #sqlhelp for those who don’t know), Jonathan rode to my rescue and informed me that despite the enticing name, that’s not what I wanted.

Instead, each event has event fields that are simply a part of the event. Global Fields (actions, whatever, told you extended events were confusing), have an added overhead, so you have to think twice about using them. Plus, sql_text didn’t return any data for sql batch calls. No, instead, for the rpc_completed, I needed to read the field, statement. Excellent! All set. Well, no. There’s still another wrinkle. For sql_batch_completed there is no statement field. Instead, I need to collect sql_batch_text. Ah, good…. but wait, now I have two fields that get returned and I have to figure out which of these I use instead of just going to a single source like I used to with trace…grrr… Why does Microsoft hate me? And you?

Anyway, I’ll be weaving extended event sessions throughout the book. You can use these things for looking at waits, deadlocks, ooh, all kinds of stuff. I did say powerful and huge right. Keep an eye out for a few more blog posts.

Also, if you want to talk to me about gathering knowledge for your performance tuning efforts, then you should make your way to SQL In the City: Los Angeles. I’m going to be there with a slew of other MVPs on October 28th. It’s free. Click here now to register. My session is called Performance Tuning With Knowledge, and it’s all about gathering the information you need to make your performance tuning decisions.