SQL Server 2012 Extended Events Update – 1- Introducing the SSMS User Interface

For the most part I have been relatively quiet about the coming changes in SQL Server 2012 with regards to Extended Events.  Primarily this has been to allow the new features of the product to become fully baked to ensure that the information would continue to be applicable as the product lifecycle progressed, and there have been a number of major changes that have made this decision a really good one.  With SQL Server 2012 in it’s RC0 phase, and based on the responses I have seen to a number of feedback items for bugs on Microsoft SQL Server Connect, like the recent one for the template issue I blogged about on my blog post, Workaround for Bug in Activity Tracking Event Session Template in 2012 RC0, I’ve decided to go ahead and start a new series of posts that outline the new features of Extended Events in SQL Server 2012.  I can’t think of a better way to start off a series on the new features in SQL Server 2012 for Extended Events than the new SQL Server Management Studio User Interface for Extended Events in a couple of blog posts.

For this initial post, we’ll take a look at some of the features that replace existing SQL Profiler functionality that most DBA’s tend to use in their day to day operations.  To start off this topic, the first thing you need to know is that there is a new node for Extended Events under the Management folder in Object Explorer for SQL Server 2012.

If you happen to be connecting to a SQL Server 2008 server using SSMS from SQL Server 2012, this node will not exist.  This is due to the fact that UI for SSMS in SQL Server 2012 are not backwards compatible with SQL Server 2008, even though Extended Events exist in SQL Server 2008.  At some point when SQL Server 2012 actually releases to manufacturing (RTM), I will release an update to my SSMS Addin for Extended Events that back ports compatibility for SQL Server 2008 to Management Studio 2012, allowing full integration between SSMS 2012 and SQL Server 2008.

Within the scope of SQL Server 2012, the UI provides a lot of new functionality that should simplify the implementation and usage of Extended Events for most DBA’s.  One of the best enhancements is the ability to create an event session using the New Session Wizard to define an event session based with the least number of steps possible, possibly using an template for the event session, or manually defining a custom configuration that is applied to all of the events configured for the session.

By opening the New Session Wizard, immediately a Introductory page is presented that can be bypassed by selecting the option to Do not show this page again:

Since this page will typically slow down the creation of an event session for use, I would typically check this option before clicking on Next. The Set Session Properties page will allow you to specify a name for the session as well as whether or not the event session will startup automatically when SQL Server starts.  This can be very useful for troubleshooting an infrequent problem that does not predictably occur and you need to ensure that the session data is collected whenever the problem next occurs.  Some examples of where this might be applied will be shown in future posts in this series.

The New Session Wizard provides the ability to create the new event session based on a previously created template, or one of the templates provided by default with SQL Server 2012.  Keep in mind that in the RC0 build, and unfortunately the RTM release of SQL Server 2012, the Activity Tracking template has a bug in the XML definition that will cause this UI to error out.  This was documented by Mike Wachal, the PM for Extended Events at Microsoft on his blog post Activity Tracking event session template is broken, but a fix for the problem in the XML is available in my blog post Workaround for Bug in Activity Tracking Event Session Template in 2012 RC0.  After replacing the broken template with the one attached to my previous blog post we can select it in the UI.

The alternative to using an existing template is to create a blank event session by selecting the option to Do not use a template.  If a template is selected, once you click Next the events from the template will be displayed in the Select Events To Capture page, which also displays the events available in Extended Events and information about the data returned by the events.  The event library can be searched a number of ways to simplify finding the correct events for the session.  The most common way to search, once you start learning the events that are available, would be to start typing the event name in the textbox (green circle below) and the results will dynamically begin filtering out the events that don’t match the search text.  If you click on a specific event in the table, the event description and information about the columns returned by the event will be displayed (purple box below).

However, if you don’t know the specific events that you want, but you know the general category that the events apply to you can make use of other search options in the UI as well.  Events in Extended Events are broken down by two attributes, a Category (Keyword in the DMVs) and a Channel, that make them compatible with Event Tracing for Windows (ETW).  The Category is similar to the trace category that the existing Trace events have and can be used for logically grouping events similar to the way SQL Profiler groups events in previous versions of SQL Server.  The Channel aligns with the channels you would see with ETW.  By default one of the Channels is excluded in the UI, the Debug Channel.  Debug events are focused towards internal debugging tasks and are not of general purpose use by most DBAs.  These events tend to be counting in nature, or can fire incredibly frequently. If you want to see the Debug events in the UI, you can click the drop down and check the checkbox for the Debug Channel and they will be available.

Additionally you can filter out specific categories by clicking the Category dropdown and unchecking specific Category names from the selection.  To add an Event to the session, you can double click on the event, or you can select multiple events using Ctrl or Shift + click on the event names and then clicking the arrow that points to the right.  Alternately, you can also remove events using a double click or by highlighting the event and clicking the arrow that points to the left.

After adding events to the session, the next page allows you to specify the Global Fields, known as actions in Extended Events, that you want added to each of the events in the session.  If you look at the columns being returned by the events in Extended Events, there are significantly fewer data elements being returned at the individual event level.  Many of the trace columns map to the Global Fields (actions) in Extended Events and can be added as needed to the events.  The goal was to minimize the size of the firing events allowing additional information to be added as needed to maximize the performance of Extended Events firing.

After selecting the global fields to add to the Event Session and clicking Next, the Set Session Event Filters page is displayed where you can define filtering (known as predicates) on the events in the session.  Any filters that were configured as a part of the template will be displayed in the upper table, while new filters that will be applied to all of the events in the session can be added to the bottom table.  New filters can only be created on the columns that are available for all of the events in the event session, which typically there won’t be any if using multiple events, or on the global fields available to Extended Events.

This is a very restrictive functionality of the New Session Wizard that was put in place to provide a parity for session creation to what most users would expect from SQL Server Profiler.  I’ll show more about how this is not the best thing when we look at the other parts of the new UI in SSMS in other posts.  Once the filters have been created, the next step is to define the event storage.  The New Session Wizard restricts you to the event_file and ring_buffer targets, which are going to be the most commonly used targets by most DBAs since they retain the full event data and do not apply additional processing to the events.  For an event session that is going to be collecting data for a long period of time, or data that generates at a fast rate, the event_file target should be used, and similar to SQL Trace you can setup the maximum file size and whether or not file rollover should occur.  If the event session is going to be collecting data for a short period of time or where the event predicates will restrict the session to only collecting a small amount of data, the ring_buffer target will generally be a good choice.

Once the data storage has been configured the session can be created by clicking Finish, or you can click Next to get to the Summary page which will allow you to review all the configured options for the Event Session and Script the session definition for further changes to the DDL if necessary.

Once the Event Session is created, the last page provides you the opportunity to start the event session and to open the Live Data Viewer for the event session which is similar to the SQL Server Profiler view from SQL Trace.

In the next blog post I’ll show the New Session dialog which is not a wizard based implementation and why it provides a much more robust method of creating an event session in SQL Server 2012.