2017-01-05

SQL Server Event Handling: Extended Events

This is part of a series of posts that explores Event Handling for the SQL Server Database Engine:

Dave Mason - SQL Server Event Handling: Extended Events

It took me a while to make the transition from SQL Profiler to Extended Events. Eventually I got comfortable enough with it to use it 100% of the time. As I read more about the XEvents architecture (as opposed to just "using" XEvents), I gained a deeper appreciation of just how great the feature is. My only gripe is that there isn't a way to handle the related events from within SQL Server using T-SQL. DDL triggers can't be created for XEvents. And they can't be targeted to Service Broker for Event Notifications (not yet, anyway). For now, the one way I know of to handle an XEvent is by accessing the event_stream target via the .NET framework. I'll demonstrate with C#.

Using Visual Studio Express 2012 (this also works with Visual Studio Community 2015), I created a new console application project. All of the code is in the "Program" class, which is created by default (it's in the "Program.cs" file).

using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.XEvent.Linq;

namespace XEventHandler
{
    class Program
    {
        //Specify these two parameters.
        private static string sqlInstanceName = ".\\DBA";
        private static string xeSessionName = "system_health";

        static void Main(string[] args)
        {
            try
            {
                //Connection string builder for SQL 
                //(Windows Authentication is assumed).
                SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
                csb.DataSource = sqlInstanceName;
                csb.InitialCatalog = "master";
                csb.IntegratedSecurity = true;

                using (QueryableXEventData xEvents = 
                    new QueryableXEventData(
                        csb.ConnectionString,
                        xeSessionName,
                        EventStreamSourceOptions.EventStream,
                        EventStreamCacheOptions.DoNotCache))
                {
                    foreach (PublishedEvent evt in xEvents)
                    {
                        Console.ForegroundColor = ConsoleColor.Green;
                        Console.WriteLine(evt.Name);
                        Console.ForegroundColor = ConsoleColor.Yellow;

                        foreach (PublishedEventField fld in evt.Fields)
                        {
                            Console.WriteLine("\tField: {0} = {1}", 
                                fld.Name, fld.Value);
                        }

                        foreach (PublishedAction act in evt.Actions)
                        {
                            Console.WriteLine("\tAction: {0} = {1}", 
                                act.Name, act.Value);
                        }

                        Console.WriteLine(Environment.NewLine + 
                            Environment.NewLine);   //Whitespace

                        //TODO: 
                        //Handle the event here. 
                        //(Send email, log to database/file, etc.)
                        //This could be done entirely via C#.
                        //Another option is to invoke a stored proc and 
                        //handle the event from within SQL Server.

                        //This simple example plays a "beep" 
                        //when an event is received.
                        System.Media.SystemSounds.Beep.Play();
                    }
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine(Environment.NewLine);
                Console.ForegroundColor = ConsoleColor.Magenta;
                Console.WriteLine(ex.ToString());
                Console.WriteLine(Environment.NewLine);
                Console.WriteLine("Press any key to exit.");
                Console.ReadKey(false);
            }
        }
    }
}


A couple of quick notes before I run the app:

  • References: one or more project references will need to be set, based on the SQL Server version. Look for them in the "shared" folder (eg "C:\Program Files\Microsoft SQL Server\110\Shared\").
    • SQL 2012: "Microsoft.SqlServer.XEvent.Linq.dll"
    • SQL 2014: "Microsoft.SqlServer.XEvent.Linq.dll" and "Microsoft.SqlServer.XE.Core.dll"
    • SQL 2016: unknown. It's probably similar to SQL 2014. If you are able to verify, please leave a comment!
  • Project Properties: I had to change the Platform Target from "Any CPU" to "x64". YMMV.
  • SQL Connectivity: if you want to run this code yourself, you'll need to specify your SQL connection. Here, I want to connect to a named instance of SQL ("DBA") on the local host. In C# string syntax, a backslash character is an escape character--thus the double-backslash. I specify Windows Authentication (csb.IntegratedSecurity = true;), but you can use SQL Authentication, if desired. (Check out the SqlConnectionStringBuilder class for details.)
  • Session Name: I'll use the built-in "system_health" XEvent session for the demo, but you can specify any session you like.

  • On to the video. Remember, this is a console application--there's no GUI. YouTube, take it away...



    That wasn't terribly exciting, was it? Three events were captured and handled: connectivity_ring_buffer_recorded (twice) and wait_info (once). Each event that was captured was handled by playing the default "beep" system sound. I could have almost as easily sent an email or logged some information to a table.


    Code Analysis

    One of the first articles I read about the QueryableXEventData class was Introducing the Extended Events Reader. It includes some examples for creating an instance of the QueryableXEventData class (it has more than one constructor) and how to loop through the collection of events. Those code samples were the starting point for what I've created here. Until I ran the code, I kept looking at that first (outer) foreach loop and thinking it would return a finite set of events, iterate through them, writing the event data to the console, then exit the loop. But as presented, program control never leaves the loop. It behaves more like this:

    while (true)
    {
        //do stuff
    }
    


    Within the first (outer) foreach loop are two more foreach loops:

    foreach (PublishedEventField fld in evt.Fields)
    foreach (PublishedAction act in evt.Actions)
    


    When you look at the XEvent Session Properties, these correspond to the items in the "Event Fields" tab and the "Global Fields (Actions)" tab, respectively:

    Dave Mason - SQL Server Extended Events Session Properties


    Running The Code

    As always, it's wise to try things in a test environment first. Please don't blindly run this code in a production environment! Tools that monitor any system typically have *some* level of impact on the performance of the system itself. That being said, Microsoft has baked in a safeguard to protect us: if the event stream fills up with data faster than our code can consume it, error 25726 should be encountered. This would cause the database engine to disconnect from the event stream to avoid slowing the performance of the server.

    If/when you get to the point where you've got the code running, something to keep in mind is that the event_stream target is always an event "behind". Meaning at any given time, the most recent event is not captured by the QueryableXEventData class. (I demonstrated this issue in another post.)


    Another Option

    If Visual Studio and C# aren't in your wheelhouse, maybe this post didn't resonate with you. Perhaps PowerShell can save the day! The code can be converted almost line-for-line into a PowerShell script with a nearly identical run-time experience. I'll demonstrate that in my next post.


    SHARE