What’s new for Extended Events in SQL Server code-named “Denali” CTP1

  • Article

I’ve been generally lax about posting new stuff to the blog for the last several months, and I’m particularly behind in posting an update about what you’ll see in Denali CTP1 that is new for Extended Events. My only excuse is that we’ve been hard at work on the advancements that will be in the next CTP after this and that’s been a full time job. But enough about the future, let’s get onto what you can see today.

(For those of you who haven’t seen yet, we announced SQL Server code-named “Denali” as the 2010 PASS Summit. You can get information about it, and download it, at https://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx.)

Extended Events in SQL Server 2008 offered the ability to collect some very detailed information about what is going on inside the database engine but it was lacking some of the based information that you were used to seeing in SQL Trace. One of the goals for the team in Denali was to make up that difference, and we have in CTP1.

From SQL Trace to Tracing with Extended Events

We’ve ported the complete set of diagnostic events that are available in SQL Trace over to Extended Events and rounded out our set of Actions to ensure that you can produce the same data from an event session that you can using SQL Trace. We stayed pretty close to doing a one for one port, even keeping the same event names in most cases, but we did make a few tweeks in terms of name changes and combining a few events in places where it made sense.

[What’s with the underline? SQL Trace really has two primary usage scenarios, one is diagnostics, the other is security auditing. We’re making a move to separate those two features; security auditing is covered by SQL Server Audit (introduced in 2008) and diagnostic tracing will be handled by Extended Events. This means the bulk of the audit related Event Classes have not been migrated.]

We didn’t want to leave folks stranded trying to figure out the event equivalencies, so we created a couple mapping tables to guide you along. The tables are located in master and are: dbo.trace_xe_event_map and dbo.trace_xe_action_map with the rather obvious purposes. There are a couple BOL topics to help you utilize these tables:

How To: View the Extended Events Equivalents to SQL Trace Event Classes
How to: Convert an Existing SQL Trace Script to an Extended Events Session

These should get you started examining how to create equivalent event sessions for SQL Trace sessions you’re already using. In a future post I’ll discuss this in more detail and describe how event fields and action are related to SQL Trace columns.

image Exposing ourselves to Object Explorer

Observant users of CTP1 will have likely noticed a new node in the SSMS Object Explorer. (Actually you’ve probably noticed several, but I’m only interested in Extended Events, so you’ve only noticed one, OK?) Yes, that’s right citizens, Extended Events has the first indications of a user interface!

We’re going to live under the Management node in our very own Extended Events folder. You get a list of event sessions that exist on the server and you can see if they’re running or not. For CTP1 you have a limited set of commands that are accessed from the context menu:

  • Sessions | Import Session – Allows you to import an XML based definition of an event session.
  • <Event session name> |
  • Start / Stop Session – Does what it sounds like.
  • Import Session – Allows you to import an XML based definition of an event session.
  • Export Session – Allows you to export the selected session to an XML based defintion.
  • Script Session – The standard T-SQL script options that produce Extended Events DDL.

The other menu options are self-explanatory or standard SSMS menu commands.

Beyond the DDL

We love our DDL but we’ve heard that some people like to use languages other than T-SQL. (Horrors!) We’ll we’ve got something for you weirdoes too. We’ve introduced a managed API for Extended Events that gives you access to the “inventory” of the system (eg. lists of events, actions, etc.) as well as the ability to create, modify and delete sessions using your favorite managed language. You can dig into this API by exploring the Microsoft.sqlserver.management.xevent namespace on MSDN. I’ll be posting some code samples to this blog in the future.

We’ve also exposed this API through PowerShell, so if you’re one of the new elite scripting, we’ve got you covered. You can find some details of the PS provider in the BOL topic Using the PowerShell Provider for Extended Events.

image 

So that’s the run down of Extended Events in CTP1. I’ll follow-up with some posts that go into specifics for these areas and there is more to come in the next CTP so stay tuned.

- Mike