What’s New for Extended Events in SQL Server Codenamed “Denali” CTP3

CTP3 is finally here and the crowd goes wild! Since I’m sure everyone has been anticipating the CTP3 updates to Extended Events (because, let’s face it, Extended Events is the most important part of the product, right?) let’s get to it.

Introduction

I’m going to cover the new features at a high level in this post and then drill into a couple specific features in more detail in posts dedicated to that topic. Before I get into the “meat” of this post I wanted to spend a few sentence writing about the design philosophy we’ve followed as we’ve created Extended Events. Every feature gets to a point where a trade-off has to be made and the design philosophy guides us in making those trade-offs.

Foremost is our prime directive (not that I’m a Star Trek™ fan):

The most important workload on the server is the customer workload; diagnostic systems should not prevent the customer workload from running.

This doesn’t mean that a diagnostic workload won’t impact server performance, it will. It means that in cases where we have to make a choice between the activity of the customer workload and the diagnostic workload, the customer workload wins, period. This has been the prime directive of the Extended Events team since the beginning and it didn’t change in “Denali.”

The goal for “Denali” has been twofold:

1. Provide a diagnostic tracing facility, built on Extended Events, that will not just replace the existing SQL Trace/SQL Profiler based system, but exceed it.
2. Drastically improve the usability of Extended Events.

A significant part of meeting our first goal was accomplished in CTP1 by providing data parity with SQL Trace event classes. I’ve already discussed migration from SQL Trace to Extended Events in the blog, so I won’t go into details about that work here. With that preamble, let’s take a look and how we accomplished the second goal.

Extended Events User Interface

Get more details: Introducing the Extended Events User Interface

This won’t be a surprise to many of you, but we’ve created a user interface for Extended Events and you’ll find it in CTP3. Having a user interface was the most common user request, by far, and we listened. SQL Profiler is too important to not have equivalent functionality. Making it much easier to configure and read event sessions is the primary way we delivered on our usability goal. The Extended Events user interface is built directly into SSMS and there are four primary pieces:

• Session list – You will find a list of sessions within the Object Explorer under the node Management | Extended Events | Sessions.
• New Session Wizard – A wizard (duh) that provides a simplified experience for creating an event session. Only the most common options and functionality is exposed with everything else being configured automatically. Expand the context menu of the Sessions node and click New Session Wizard.
• New Session dialog – The complete set of Extended Events configuration in handy dialog format. Expand the context menu of the Sessions node and click New Session… . The same dialog is used for creating new sessions and for modifying existing sessions (click Properties on the context menu).
• Extended Events display – A collection of SSMS pages (tabs like the Query window) that display Extended Events trace data.

The user interface you designed…really

We didn’t just listen to the feedback that we needed a user interface, we also listened to the feedback we got throughout the design and development process. This UI represents more than just the work of the Microsoft development team, but also the feedback of many SQL Server MVPs, usability study participants and anyone who would talk to us at the various conferences where we presented the UI. We took something useful away from every customer interaction and made significant changes based on that feedback. Thanks to everyone who took the time to provide your feedback. (And keep it coming – as you explore CTP3 send us your feedback using Microsoft Connect.)

You might want to sit down for this next section

It’s an unpopular word, but I have to say it … Deprecation.

Yeah, I said it; we are announcing deprecation of SQL Trace in Denali. Before everyone goes off the deep end, remember, this is only the announce phase which means that nothing actually changes in Denali, we’re just letting you know that we’re phasing it out in favor of the new system over the next few releases so now is a good time to start investigating how to migrate. (See the link in the Introduction for more details.)

Programming Extended Events

Get more details: Introducing the Extended Events Reader

Another important aspect of creating a usable diagnostic system is a programmatic interface. (Not everyone loves DDL as much as we do apparently.) We’ve covered this in two pieces:

• A “management” API that lets you create and modify event sessions as well as explore the Extended Events metadata on a server. This API was release in CTP1 and I’ve already provided examples in my post Introducing the Extended Events Object Model.
• A “reader” API that provides functionality for reading event files (XEL) and event streams coming from a running event session.

The UI is built on top of these APIs so you have all the capabilities you need to write your own customized tools for working with Extended Events.

The “other” stuff

There are always a host of little things that we add, change or fix when we’re improving a feature. I don’t want to deprive anyone of the “Joy of Discovery” that comes with finding these little bits and pieces, so stop reading now if you want to hunt them down on your own. For the rest of you, here is the list of bits and pieces that we hope will make the Extended Events world a little better for you.

Some (target) names have been changed to protect the innocent

We’ve changed the names of some of the targets to be friendlier:

To make this change easier we’ve done some work under the covers:

• ADD TARGET with the old target names will still work. We do name fixup inside SQL Server when we execute the DDL.
• DROP TARGET with the old target names will not work. We fixed up the names so there are no records in the catalog with the old names.
• The target_name field of dm_xe_session_targets will be fixed up to have the new target names for any running sessions during Upgrade.
• The name field of server_event_session_targets will be fixed up to have the new target names for any configured session during Upgrade.

Note on histogram target – There is not strong evidence to support that people are using both the synchronous and asynchronous bucketizer together in a single session so we used the renaming event to also eliminate this duplication. We have a single histogram target now that is asynchronous.

There can be only one – file that is

We’ve eliminated the metadata file all together. That’s right, no more XEM file, it just didn’t make sense and it’s kind of a pain to have to worry about dragging the extra file around if you’re sending logs to other folks. Both the server reader function, fn_xe_file_target_read_file and the new Reader API are fully backwards compatible with the SQL Server 2008 and SQL Server 2008 R2 “dual file” format.

Note: We do not support the XEL/XEM files created in SQL Server Codenamed “Denali” CTP1.

The single file model has some downstream implications for event_file target and the fn_xe_file_target_read_file function.

event_file – You no longer need to set the metadatafile attribute but it is still available (but optional) so as not to break your DDL scripts. If you do provide a value for metadatafile it will be ignored for valid paths and it will throw an error for invalid paths.

fn_xe_file_target_read_file – The mdpath attribute is optional and ignored (but will throw if you provide an invalid path) when the filename path represents “Denali” XEL version files. You should just pass ‘NULL’ for mdpath as you would for all other optional attributes.

New options available for some targets

We’ve added a few new options to make some of the targets a bit more flexible.

pair_matching – In order to help prevent the pair_matching target from running amok in cases where you have an excessive number of orphaned records we’ve added the max_orphans option to the target with a default of 10000. As you might surmise, this option controls the number of orphaned records that will be maintained in the pair_matching target.

ring_buffer – We’ve introduce the max_events_limit for the ring_buffer to provide a deterministic way to control the number of events in the target. The default value is 1000 and we’ve changed the default for max_memory to be unlimited, meaning that the ring_buffer will use as much memory as required to store 1000 events by default. You can set one or both of these. When both are set we’ll honor the one that is hit first.

Wrapping up

Now that you have the overview go ahead and take Extended Events out for a spin. Dig into the detailed posts for more information about the UI and the programmatic APIs. If you find any issues or have suggestions for future advancements please post an item into Microsoft Connect so that others can vote on it. If you have questions, post them to the SQL Server Database Engine forum.

Happy Eventing!

- Mike