SQL Server Extended Events

Just a quick shout-out today to MSSQLTIPS for this article today on an introduction to what is, IMO, the most under-rated cool new SQL Server 2008 feature: Extended Events. X-Events is a SQL Server sub-system that is always on and registers information that is available to you as a DBA, about your system. It is a light-weight (in terms of system impact) version of SQL tracing and provides information that you can interogate about what is good and what is bad on your SQL Server. You can even integrate the events with the Windows Server Event Tracing subsytem.

SQL Server DBAs have been using server-side tracing, default trace and client-side tracing with SQL Profiler, for many years. So switching to a new mechanism for SQL Server monitoring and troubleshooting is new and unnatural. But make no mistake, Extended Events is THE way to monitor SQL Server events going forward. Denali will see a big increase and improvement in using X-Events.

Perhaps if SQL Server 2008/R2 had shipped with a nice GUI tool or integrated with SSMS, adoption of extended events would be greater. But you can download and install the free Codeplex add-in for Extended Events which you can get to from the link at the top of my post from the MS SQL Tips article. The free download for the x-events explorer very much simplifies setting-up and querying events. Even so, it is still a learning curve because with extended events in SQL Server, you have to learn about event targets and sessions and how to configure those.

In all honesty, I myself, do not use extended events very much yet. But, I’m no longer a full-time DBA or developer. Now I just try to help those who are in that role! But I do think this can be a very helpful way to get to the bottom of the good & bad of your SQL Server installations.

Utilize SQL Server Extended Events in SSMS

SQL Server 2008 introduced a new eventing architecture called Extended Events or Xevents. With Xevents, you can monitor and troubleshoot SQL Server and bring together both database and Windows OS events using event tracing for Windows (ETW) which means that it is a very powerful mechanism that can be used by many tools including Windows debugging and troubleshooting tools and integrating into monitoring tools for Windows.

But there is not an out of the box tool to set-up Extended Events and SQL Server Management Studio (SSMS) does not have any capabilities to provide support for Extended Events yet, even in SQL Server 2008 R2. Personally, I do not find Xevents intuitive or easy to configure and set-up.

But I came across Jonathan Kehayias’ Codeplex tool for managing extended events in SSMS. I’ve been using it today and I LOVE IT! Kudos to Jonathan for sharing this awesome tool with the community on Codeplex. I recommend that you download it and try it if you would like to begin using the power of extended events to monitor SQL events. I am currently making use of the SQLOS events.

BTW, just to add: based on my experience with add-ins and GUI-based solutions on Codeplex, I found Jonathan’s Extended Events Manager to be excellent compared to many other tools that I’ve tried.