THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

An XEvent a Day (12 of 31) – Using the Extended Events SSMS Addin

The lack of SSMS support for Extended Events, coupled with the fact that a number of the existing Events in SQL Trace were not implemented in SQL Server 2008, has no doubt been a key factor in its slow adoption rate.  Since the release of SQL Server Denali CTP1, I have already seen a number of blog posts that talk about the introduction of Extended Events in SQL Server, because there is now a stub for it inside of SSMS.  Don’t get excited yet, the functionality in CTP1 is very limited at this point, but the demonstration of what’s to come at the PASS keynote last month showed that more effort has been put into this than is currently available in CTP1, and we should see further improvements in coming releases.  To make working with Extended Events easier, two years ago I worked on a management UI that eventually turned into a SSMS Addin for Extended Events.  In this blog post I’ll go over the features of the SSMS Addin and how it can be used to work with Extended Events.

Features

The Extended Events Manager exists in two forms, a SSMS Addin, and a standalone Winforms application.  The project started out with the Winforms application, and has continued to maintain that as a development environment for functionality changes and new additions to the project, since debugging changes inside of the SSMS Addin proved more difficult than inside of the Winforms application.  This blog post will focus on the functionality of the Addin which matches the functionality of the stand alone applications with only a few difference that relate to the stand alone application not being a part of SSMS.  Currently the Addin only supports SQL Server 2008 and 2008R2, but changes are underway to allow it to support Denali as well.  T

Automatic Update Notifications

The Extended Events SSMS Addin will check for an Internet connection when SSMS starts up, and validate whether or not the latest build is being used.  If a new release is available, then a message box similar to the one below will be displayed.

image

By clicking the “Yes” button on the dialog box, Internet Explorer will be launched and will open the CodePlex project site for the application where you can then download the newest release for use.  The Automatic Update notifications can be turned off by unchecking the box on the popup, or turning off the feature in the Option Manager.

Using the Extended Events Manager

Using the Extended Events Manager is very simple, and a majority of the functionality was implemented to match the way functionality is exposed inside of SSMS already.  The Addin is available from the View menu and opens up the Event Session Explorer Window which can be docked like the Object Explorer inside of SSMS.  The Event Session Explorer will show the Extended Events Session information for the currently selected Server in the Object Explorer if the server is a SQL Server 2008/2008R2 instance, and automatically changes its Server connection when the selected server in the Object Explorer changes. 

Event Session Explorer Window

The Event Session Explorer displays a list of all the Event Sessions that exist inside of the current SQL Server Session Definition Catalogs.  Each Event Session node will show a list of Events and Targets configured in the Event Session. 

image

A majority of the functionality provided by the Extended Events Manager is available through context menu’s in the Event Session Explorer.  There are three different context menu’s available, the Server Level Menu, the Session Level Menu, and the Target Level Menu.

Server Level Menu

image

  • New Event Session

    The New Event Session Menu Item will open up the Event Session Editor with a blank Session form.

  • Refresh

    The Refresh Menu Item does exactly what its name implies and refreshes the Session Metadata held by the Tree View from the SQL Server.

  • View XEvent MetaData

    The View XEvent MetaData Menu Item loads the available Extended Events MetaData from the Extended Events Engine on the Server and displays it in the XEvent Metadata Viewer. The data is categorized by Package with SubNodes for each of the object types supported by the Extended Events Engine on the currently selected Server.

  • Addin Options

    The Addin Options Menu Item opens up the Option Manager for the Addin allowing configuration of the Options available.

Session Level Menu

image

  •  

    Edit Event Session
    The Edit Event Session Menu Item will open up the Event Session Editor with the currently selected Event Sessions metadata bound to the form.

     

  •  

    Drop Event Session
    The Drop Event Session Menu Item will immediately execute a DROP EVENT SESSION command against the database server for the currently selected Event Session.

     

  • Script Session As
    The Script Session As Menu Item provides scripting functionality similar to that provided by Management Studio for the currently selected Event Session. Currently a script can be created to a file or to the system Clipboard. A future release will also provide the ability to script to a new SQL Server Management Studio window.

     

  •  

    Start/Stop Event Session
    The Start Event Session and Stop Event Session Menu Items are active based on the current status of the Event Session on the Server. If the Session is started, then the start option is disabled and the stop option is enabled. Both issue the appropriate ALTER EVENT SESSION command immediately against the SQL Server for the selected Event Session.

     

Target Level Menu

image

  •  

    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.

    image

    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

    image

    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

      image

      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.

      image
    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.

      image

    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

      image

      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.

    image 

    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.

    image

    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.

    image 

    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.

    Published Sunday, December 12, 2010 2:40 PM by Jonathan Kehayias

    Comments

    No Comments
    Anonymous comments are disabled

    This Blog

    Syndication

    Powered by Community Server (Commercial Edition), by Telligent Systems
      Privacy Statement