View Target Data

The View Target Data Menu Item will open up the Target Data View and display the information contained within the currently selected target.

Drop Event Session

The Query Target Data with TSQL Menu Item will make use of Adam Machanic’s Code Generator to generate a TSQL command that can be used to query the target data from the server in a SSMS Query Window.

Metadata Viewer

The Metadata Viewer allows browsing of the Extended Events Metadata contained inside of the Metadata DMV’s in SQL Server.

1. Tabbed Document Support

   The Metadata Viewer provides standard Tabbed document functionality inside of SSMS.

2. MetaData Tree View

   Contains a list of the existing Event Sessions defined on the Server. The session definition can be seen by expanding the sub nodes under the session.

3. Additional Information Box

   Will contain any additional information contained in the Event Session Metadata for the selected node.

Event Session Editor

The core functionality provided by the Extended Events Manager is the ability to create and edit Event Sessions without having to explicitly write TSQL.  The Event Session Editor allow full configuration of an Event Session including the ability to Add/Remove/Edit Events and Targets, and specify the Session Level Options.

Main Window

The main window of the Event Session Editor exposes a majority of the information about the Event Session.  There are nine areas of interest in the Session Editor.

1. Script Action

   

   The Script Action button on the toolbar provides the same scripting functionality available in many of the windows natively available in SSMS including the ability to script changes to a New SSMS Window, a File, or the Clipboard.  An addition option is to script the changes to a Message Box popup, which was a debugging feature that was never intended to make it into a released build, since it has not intrinsic value over the other options in practical usage, but since it is there I figured I would mention it here.

2. Session Name

   The name of the Extended Event Session.

3. Add Event

   Opens the Event Editor UI for a new Event to be added to the Event Session.

   

4. Start Event Session Automatically

   Specifies that the Event Session should be altered to a started state when the Ok button is clicked.

5. Event Grid View

   Displays current Events defined for the Event Session.  Direct editing of Actions and Predicate definitions can be performed by typing in the appropriate cell for the Event to be changed.  Alternatively, the entire Event definition can be edited by clicking the Edit button which will open the Event Editor and bind the Event definition to the Editor.

6. Targets Grid View

   Displays current Targets defined for the Event Session.  Direct editing of Target Option definitions can be performed by typing in the appropriate cell for the Target to be changed.  Alternatively, the entire Target definition can be edited by clicking the Edit button which will open the Target Editor and bind the Targets definition to the Editor.

7. Add Target

   Opens the Target Editor UI for a new Target to be added to the Event Session.

   

8. Ok/Cancel Buttons

   The Ok button will parse the Session to a script for execution against the SQL Server to change or create the Event Session.  Exceptions are handled and offer to display the specific errors, including line numbers with the Session Script for troubleshooting.  Should there be an unresolved issue it might be best to script the operation to SSMS and perform troubleshooting directly with the DDL command.  In this case, please provide the DDL script in error as a bug on the Codeplex site for the project.  The Cancel Button will close the Session Editor discarding all changes.

9. Session Option Editor

   

   The Session Option Editor displays all of the options for the Event Session.

Target Data Viewer

The Target Data Viewer shreds the Event Data for all of the Targets currently available in Extended Events and pivots the data so that it can be displayed in a Profiler like table in SSMS.  The target information is displayed at the top, the Event data held inside the target is displayed in the Gridview, and information about the selected row in the Gridview is displayed in the text window below it.  The target data can be incrementally refreshed from the server by clicking the Refresh button.

Extended Event Manager Options

The Extended Event Manager has two configurable options that control whether or not the Addin checks for updates at startup, and whether or not the Addin provides a warning before attempting to DROP an active Event Session from the current server.

In addition to the Addin specific options, the Addin also offers the ability to set personalized defaults for the Event Session Options that are used every time a new Event Session is created using the Addin.  This can simplify the customization of Event Sessions if you prefer to set Event Session Options that differ from the defaults provided by Extended Events.

Using the Library with Powershell

The Extended Events Manager has its own SMO like library DLL that is the foundation of all of the functionality available inside of the Addin and the stand alone application named XEvents.Library.dll.  This assembly can be used with Powershell to extend management of Extended Events to Powershell, by loading the library using Reflection:

[reflection.assembly]::LoadFrom("C:\Program Files\SQL Server Extended Events\Extended Events SSMS Addin\XEvents.Library.dll")

Once the assembly is loaded, a connection to a SQL Server can be made

$Server = new-object ("XEvents.Library.SqlServer") "ServerName”

From here, the common PowerShell commands apply. To see all the members of the Server:

$Server | get-member

To view the Sessions currently in the Extended Events Engine:

$Server.EventSessions | % {$_.Name}

Background on the Project

When I first read about this new feature in SQL Server 2008 I tried looking into it, but I really got nowhere because there wasn’t a lot of information about it.  At the time only one blog popped up with anything to do with Extended Events in search engines.  Bob Beauchemin of SQL Skills had blogged about Extended Events as early as the July CTP of SQL Server 2008, back in August 2007.  Nearly a year later I started really working on learning Extended Events and like most people I had trouble wrapping my head around all the DMV’s Catalog Views, and learning what was actually available through Extended Events.  I wrote a really nasty XML generator that queried the DMV’s and provided back a XML representation of the Metadata that root nodes for each Package and then child nodes for each of the types of Objects contained in each Package, all the way down to the object column level.  This made the Metadata a bit more digestible, but after a few days of expanding and contracting nodes I set out to create a basic UI that made working with Extended Events easier.

When I completed the first build of the UI and had tested things to the point that I considered it to be “stable” I posted the project and source code up on CodePlex and sent an email off to Bob asking for some feedback on the project.  Bob provided a ton of feedback early on in the projects development and blogged about it shortly after the initial build was released in August of 2008.  I spent some time reworking pieces of the project here and there, and as I published newer builds of the project, I sent it out to a couple of other people that I had begun talking/blogging about Extended Events, and one of those people, my good friend Kevin Kline (Blog|Twitter) recommended that I enter the application in the SQL Server 2008 Heroes contest that was being put on by Microsoft in conjunction with that year’s PASS Summit.  When it was picked as a finalist and won, I was surprised because at the time it was just something I was playing around with.

After PASS 2008 I got an email from Adam Machanic (Blog|Twitter) asking if I would be interested in some help with the project to move it from being a basic project into a completed tool.  Many of the changes that ensued over the next year with the code base, including a complete rewrite of the underlying Library DLL came about because of email exchanges with Adam and the suggestions he provided.  However, the one thing I really disliked about the Extended Event Manager was that it was at this point still a stand alone application, and I really hated having to open another application to play with Extended Events.  Over this period of time I had been trading emails with Jerome Halmans at Microsoft, constantly asking questions about Extended Events and sending him updates whenever I published a new build of the UI.  At some point he mentioned that it would be great if it could be integrated into SSMS directly, so I set out to figure out how to integrate the UI functionality in SQL Server Management Studio as an Addin, which was not then, and is still not now, something that is actually supported by Microsoft.

If you do any kind of searching online for SSMS Addin today, you will find all kinds of references for how to build one in SQL Server 2005, and SQL Server 2008, but one person that you are most certainly going to find is Mladen Prajdic (Blog|Twitter), the author of the SSMS Tools Pack.  Right around the point that I was ready give up on ever figuring out how to build a Addin for SSMS, I got a comment from Mladen on twitter about the Extended Events Manager, that opened up conversations between us about the problems I was facing in my attempts to move it from being a standalone application into a SSMS Addin.  Mladen answered a number of questions for me, and even looked at a couple of code files that I was having problems with through email exchanges, and the result was the first release of the SSMS Addin for Extended Events in July 2009.  There have been a few revisions to the Addin since its initial release, mainly performance enhancements to the Target data processing methodologies, and some fixes to compatibility issues with SQL Server 2008 R2 due to changes in the SSMS codebase.

I owe a lot of people for their input, feedback, ideas, and assistance with this project.  I may have been the person slinging the code, but the project is as much the combined work of all the people mentioned above, without which I probably never would have continued as far as I did with the project.

Future of the Project

If you look at the project page on Codeplex, one of the first things you will probably notice is that I haven’t done any real work on it in over a year, and that is essentially the case.  The last build of the project, was completely bug free and perfect, was release right before I changed jobs, and then went back to school, so I haven’t had a lot of time to dedicate to the project, and I haven’t really had a lot of motivation to since there hasn’t been a lot of feedback on what bugs or issues people have encountered when using the Addin.  I know that there are some bugs in the current release, every now and then I encounter one and ask myself when am I going to finally fix that.

Currently I have been reworking the Library code base to work with SQL Server Denali CTP1 and provide backwards compatibility with SQL Server 2008 and 2008R2.  This has proven to be somewhat of a challenge for a number of reasons, mainly I haven’t written much .NET code over the last year and I have had to put some effort into learning what some blocks of code actually do again.  In addition to that I have had to make some significant changes to support changes that occurred in Denali, and doing that while ensuring backwards compatibility is maintained is slow, but from the XEvents.Library DLL standpoint, I have made all the necessary changes to support all three releases of SQL Server. Lets just hope that Microsoft doesn’t change to much in the remaining CTP’s, RC’s and ultimately the RTM of Denali! (Mike I know you are reading this, hint hint!) 

Since the Visual Studio environment that Management Studio runs on top of was changed out, that changes a number of things associated with the Addin portion of the Extended Events Manager, and I have yet to figure out how to actually register an Addin in Denali CTP1 yet, but I do know that its possible and already been done so I am sure that I will get there at some point.  For now I am reworking the UI components using the stand alone application approach that I have used in the past for building and testing the controls that are actually registered in the Addin in SSMS.  Hopefully sometime in January 2011 I will be able to release at least the stand alone application in a Denali build and then be in a position to focus on figuring out how to hack an Addin back into SSMS.