Udostępnij za pośrednictwem

Introducing the Extended Events Reader

  • Artykuł

Check out What's new for Extended Events in SQL Server Codenamed "Denali" CTP3 for an overview of all of the new functionality released in CTP3. This post covers the details of the “reader” API, which we call the XeReader, that you can use to programmatically access both XEL files and the near-live event stream from a running session. You can find information about the Extended Events object model in my earlier post: Introducing the Extended Events Object Model.

Event stream defined

An event stream is pretty much what it sounds like, a stream of events that comes out of a running session. You will not find an option to configure the event stream in the UI or Extended Events DDL, it is accessed using the API described in this post. All the session configuration is handled under the covers. There are a few things to understand about the event stream before I dive into some examples:

  • The event stream is asynchronous, not live, but to make the experience seem “more live” the event stream modifies the latency behavior of the source event session. When the event stream is hooked to an event session, the latency period specified by MAX_DISPATCH_LATENCY is changed to 3 seconds. When the event stream is disconnected, the latency is changed back to it’s original value. You may notice some odd behavior if you connect more than one XeReader to the same event session; the latency modification assumes only one connection so the first disconnect from the event session will revert the latency back to the original value (eg. no ref-counting).
  • The event stream aggressively follows the prime directive – if the application utilizing the event stream API gets far enough behind in processing events that it risks blocking the server, the application will be disconnected. This is to prevent a “client tracing” application from bringing down a server; a problem often documented with SQL Profiler. If the event rate from your event session is so high that it causes a disconnection, then the event file is a more appropriate target for you.

Exploring the API

From the point of view of the XeReader, there is no difference between an XEL file and an event stream aside from the mechanism used to obtain the data. The main object in the XeReader is the QueriableXEventData object, which returns an enumerable list of events.

Using the XeReader API

In order to code against the API you’ll need to add a reference to the following assembly:

Microsoft.SqlServer.XEvent.Linq.dll

You’ll also need to add the following namespaces to your classes.

using Microsoft.SqlServer.XEvent.Linq;

Note: By the time we release “Denali” you will also need to add a second namespace, Microsoft.SqlServer.Xevent, to your code. Just letting you know so it’s not a surprise.

Reading an event file (XEL)

The XeReader supports reading the legacy XEL/XEM from SQL Server 2008 & SQL Server 2008 R2 as well as the XEL file produced in SQL Server “Denali” CTP2 and above. There is no support for reading the files created in SQL Server “Denali” CTP1. There are three versions of the constructor that accept files:

  • A single string with a valid path.
  • A single string array containing a set of paths to XEL files.
  • Two string arrays containing sets of paths to XEL and XEM files respectively.

The paths can be fully qualified files or any valid representation of a path using wildcards. Here are a couple examples:

Read all XEL files from a specific location

 QueryableXEventData events = new QueryableXEventData(@"C:\Temp\myFile*.xel");

Read a specified multiple of XEL files

 string[] fileList = new string[2];
fileList[0] = @"C:\Temp\demo_trace_0_129418467298110000.xel";
fileList[1] = @"C:\Temp\demo_trace_0_129391767647570000.xel";

QueryableXEventData events = new QueryableXEventData(fileList);

Provide both XEL and XEM files

 string[] xelFiles = new string[1];
xelFiles[0] = @"C:\Temp\demo_trace_0_129418467298110000.xel";
string[] xemFiles = new string[1];
xemFiles[1] = @"C:\Temp\demo_trace_0_129418467298110000.xem";

QueryableXEventData events = new QueryableXEventData(xelFiles, xemFiles);

You get the idea.

Reading an event stream

You can read a stream from a running session by providing a connection string and event session name to the constructor along with a couple extra options.

 QueryableXEventData stream = new QueryableXEventData(
    @"Data Source = (local); Initial Catalog = master; Integrated Security = SSPI", 
    "alert_me", 
    EventStreamSourceOptions.EventStream, 
    EventStreamCacheOptions.DoNotCache);
  • EventStreamSourceOptions – EventStream is the only option that exists at this point. We’ll be adding one more option before we release but I’ll leave that for a later blog post when it’s relevant.
  • EventStreamCacheOptions – You can either choose to not cache any data (DoNotCache) or to cache the data (CacheToDisk). This option determines whether you can enumerate across the history of events that have come from the stream. The DoNotCache option will pull events from the server as fast as possible and keep them in memory as long as possible. It is possible for events to be ejected from memory before they are processed by the enumerator. The CacheToDisk option will store events to the disk until the disk is full.
Working with the Events

The constructors all return an enumerable collection of type PublishedEvent, so the code to work with the events is identical regardless of source. I’ll cover the basics here to get you started.

You can loop through the collection of events just like you would expect for an enumerable object so you can imagine using a foreach to process each event. Additionally, each PublishedEvent object, contains collections for Fields and Actions that return the PublishedEventField and PublishedAction objects respectively. Taken together it’s a straight forward operation to list out all the events along with their fields and actions using code similar to this:

 foreach (PublishedEvent evt in events)
{
      Console.WriteLine(evt.Name);

      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);
        } 
}

You can perform conditional expressions as you would expect, say if you wanted to perform an action based on a specific value being returned from a field:

 if (evt.Fields["field_name"].Value == "foo")

or

 if (fld.Value == "foo")

It is worth pointing out some “weirdness” associated with map fields if you want to evaluate using the friendly text that you would find in dm_xe_map_values rather than the integer map_key. You have to explicitly cast the field to a MapValue:

 if (((MapValue)evt.Fields["field_name"].Value).Value == "foo" )

Again, you get the idea. This is really the core of the XeReader and the part I expect you’ll be working with the most. To make your life a little easier, I’ve attached a files that contains all this code in a runnable format, just add a reference to the assembly and you should be ready to start experimenting.

Download the Extended Event Reader sample code

Happy Eventing

- Mike

Comments

  • Anonymous
    August 08, 2011
    I love this so much, I had no idea what the Cache/DoNotCache option did.

  • Anonymous
    August 08, 2011
    I love this so much, I had no idea what the Cache/DoNotCache option did.

  • Anonymous
    February 11, 2013
    This is great just the information I was looking for!

  • Anonymous
    February 18, 2015
    Is the Microsoft.SqlServer.XEvent.Linq.dll redistributable?