Why and When You Should Use SQL Server Extended Events

While Extended Events have been around since the release of SQL Server 2008, they have not been as popular as one might expect for such a powerful troubleshooting feature. You might still be wondering then if now is a good time to begin using them. This section addresses that question by reviewing three of the primary reasons why people are now using Extended Events, along with some examples demonstrating when you might choose to use them.

SQL Server roadmap

The first reason to consider using Extended Events now is driven by Microsoft’s diagnostic tools road map for SQL Server. According to Microsoft, the tools most people currently use for troubleshooting, SQL Trace and Profiler, are slated for retirement in a future version of SQL Server. Their replacement, as you can probably guess, is Extended Events; and even SQL Server 2012 includes new functionality for Extended Events, whereas SQL Trace is starting to be left behind. Fortunately, it’s very likely that whatever you do today with Profiler you can also do with Extended Events.¶ If after using SQL Server for many years you’ve built up a library of Profiler based monitoring sessions, all is not lost, as there are several migration options and aids available for you. Microsoft provides some migration mappings in some SQL Server DMVs for people who want to migrate between the Profiler and Extended Events, while boB “The Tool Man” Taylor has recently released an actual conversion tool called SQL PIE (Profiler Into Events) that will be downloadable soon from Codeplex.

Graphical tools

The second reason to use Extended Events now is that SQL Server 2012 delivers out-of-the-box what Extended Events in SQL Server 2008 lacked, a graphical user interface (GUI). Unlike other new SQL Server features that were embedded in SQL Server Management Studio, the adoption of Extended Events was hindered by this lack of a GUI. Instead, learning a new T-SQL command set stood in the way of using them, along with the need to know how to query XML data. Although a GUI tool was written by a community member for SQL Server 2008, you had to be interested enough in Extended Events to find and then use it. Even now, you can probably begin to see why Microsoft had to deliver improvements in SQL Server 2012 if they were ever going to realistically be able to retire the SQL Trace components.¶ Fortunately, Microsoft did respond, and SQL Server 2012 includes a native graphical interface for Extended Events built into SQL Server Management Studio. Now it’s possible to create, deploy, and monitor reasonably complex Extended Events sessions without having to use any T-SQL commands or query XML data. Of course, there are always benefits if you choose to do that, but they’re no longer compulsory as they were in SQL Server 2008.

Low impact

Finally, if the preceding two reasons aren’t enough to persuade you to begin using Extended Events, then consider the nearly zero overhead that using them has on SQL Server. Extended Events is often called “lightweight” for good reasons. First, it is embedded deep within the SQL Server engine, which means it requires far less code to function compared to older tools that connect to SQL Server like a regular user does before they send event data. Second, although other tools such as Profiler may try to limit the amount of data they request, they tend to use inherently inefficient mechanisms within SQL Server that often results in capturing more data than is needed.¶ Instead, Extended Events take a reactive approach, only collecting and sending data to their target when a previously configured situation being monitored for occurs. If that event doesn’t happen, then the SQL Server engine will not capture or store any event data. Microsoft has measured just how lightweight the events architecture that Extended Events use actually is, and determined that 20,000 events per second firing on a server with a 2 GHz Pentium CPU and 1GB of memory consumed less than 2% of the CPU’s resource — and that’s on a very old specification server!¶ Of course, it’s always possible to configure anything badly and suffer undesirable consequences, including with Extended Events, so low overhead should not be an excuse to avoid planning and testing their deployment into your production environments. In fact, it’s even possible to purposely configure an Extended Events session to stop SQL Server when a specific event occurs; it’s that powerful! However, to do that, you would have to deliberately configure that very specific action to occur, so don’t worry that you could accidentally stop SQL Server.

When you might use extended events

Having discussed some of the reasons why you might want to use Extended Events, this section considers what you might use it for. If you’re already familiar with tools like Profiler and SQL Trace, then you will already be familiar with these examples based on your current monitoring:

• Troubleshooting blocking and deadlocking
• Finding long-running queries
• Tracking DDL operations
• Logging missing column statistics

After you start to explore some of the more advanced capabilities of Extended Events, you’ll see it’s just as easy to track events such as the following:

• Long-running physical I/O operations
• Statements that cause specific wait stats to occur
• SQL Server memory pressure
• AlwaysOn Availability Groups events