Extended Events (XE or XEvents) is a diagnostic feature that was introduced with SQL Server 2008. This is one of the significant additions to SQL Server. However I feel this is the least used feature so far. This post is a brief introduction to extended events and its significance for diagnosing SQL Server and performance monitoring.
What happened to the good old SQL Profiler and Server side trace?
For a quite few years diagnosing problems spin around SQL Profiler tool or Server side trace feature (and trace flags and performance counters). But ask any SQL Server DBA this question.
“Do you use these tools in production?” The answer is a big “NO”.
SQL Profiler and server side trace are resource and memory intensive. Whatever filter you apply, they process huge volume of trace data and then apply the filter. Definitely this is not an ideal method for tracing in production environment.
More over SQL Profiler and server side tracing functionalities are deprecated in SQL Server 2012.
What are Extended Events?
SQL Server Extended Events are a highly scalable, event driven data collection infrastructure for SQL Server and databases. The term “Events” can be correlated to “action to be taken when an event fires”. The XEvents has far less overhead than the legacy server side trace or SQL Server profiler mechanisms.
Here is an excerpt from the book “Professional SQL Server 2012 Internals and Troubleshooting” by Christian Bolton et al.
“Microsoft has measured just how lightweight the events architecture that Extended Events use actually is, and determined that 20,000 events per second fi ring 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!”
Why this is called Extended Events?
Windows operating systems (from Windows Server 2000) has a high-performance troubleshhoting feature called Event Tracing for Windows (ETW). You can read on ETW from here and here. Microsoft has Extended this Event tracing feature to use in SQL Server. So it has got the name Extended Events.
Is it possible migrate old SQL trace to new Extended Events?
If you still want to migrate your legacy SQL trace files here is a method. Mike Wachal from Microsoft has developed an SQL CLR procedure to do the migration. You can find the information here.
This post is just an introduction to XEvents to make the feet wet. I’m planning to write more posts on XEvents in the coming days. If you want to jump to the water here are some links.