The primary focus we’ve had for Extended Events in SQL Server codenamed “Denali” is to offer a better user experience by providing options for working with the system beyond DDL. One of those options is the availability of a managed code object model. In this post I’ll describe the object model and it’s usage as well as provide sample code that shows how various objects are used.
In the graphic below you can see that there are two “sides” of the object model:
Metadata: Describes the set of events, actions, targets, etc. that are available on the system. All access to the metadata objects has to go through the Package object.
Runtime: Describes the event sessions that are configured and various aspects of their runtime state. All access to the runtime objects has to go through the Session object.
The top level object is named XEStore; you get to the XEStore by connecting to the program that uses Extended Events. At the current time the object model supports the SQL Server database engine, <teaser> but in the future there will likely be others.</teaser> <bummer>(Sorry, I can’t discuss when or what at this point.)</bummer> The object model is based on the SFC Framework which isn’t particularly important for most of the XE objects, but the XEStore is a type of SfcConnection so I thought it worth mentioning. Since we’re specifically after the XEStore in SQL Server, I’m using a SqlStoreConnection, which inherits SfcConnection, and I need to pass in a normal SqlConnection to get to my server.
private static XEStore ConnectToXEStore()
SqlConnectionStringBuilder conBuild = new SqlConnectionStringBuilder();
conBuild.DataSource = "(local)";
conBuild.InitialCatalog = "master";
conBuild.IntegratedSecurity = true;
// Create the actual SqlStoreConnection...
server = new Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection (new SqlConnection(conBuild.ConnectionString.ToString()));
// ...and then create the XEStore to return to the calling function.
return new XEStore(server);
In this case I’m using the SqlConnectonStringBuilder for clarity, but you can see how I could have just passed a connection string directly into the SqlStoreConnection constructor. The connection to the XEStore is the part that changes if I connect to a different program that uses Extended Events. Everything else will be identical.
In the interest of keeping this post short and helping you avoid a boat-load of Copy/Paste activities, I’ve just put all the sample code into a .cs file with comments throughout to help explain the various objects and tie them back to the Extended Event DMVs and Catalog that you’re already familiar with. I tested this code on the current, public CTP, so things should be working, but let me know if you run into any problems.