Extended Events in SQL 2012

The SQL Profiling Journey

Capturing SQL traces has always been really handy for DBAs or database developers to troubleshoot database blockings and deadlocks, finding those problematic long running queries, tracking DDL operations or maybe logging those missing column statistics. The sad part of the story is that all these methods of collecting diagnostic data from SQL Server have an associated “observer overhead” impacting the performance of a workload under heavy load.

With a quick peep into SQL Trace internals we find that:

1. Trace runs right in the SQL Server process to gather and filter traced events.
   
   
2. A trace (created and managed using documented, Stored Procedures) can either write to a file directly (server-side trace) or return data directly to the Profiler.
   
   
3. When an event occurs inside SQL Server, SQL Trace passes the event to all the running traces that include the event. Each trace then filters the event as specified by the trace definition and queues the selected event data before sending the output.

And as expected, writing the trace data directly to a file is very efficient since it's done directly from SQL Server with minimal overhead. The active trace file can be viewed using the fn_trace_gettable() table-valued function on the same instance as the trace. However, the trace needs to be stopped or a file rollover must occur before the trace data can be imported.

Profiler, on the other hand, retrieves and processes trace data in near real time.

Where the drawback exists

The drawback exists because:

1. The interactive graphical display requires quite a bit more overhead.
   
   
2. A high-volume Profiler trace degrades the overall server performance to the point of causing query timeouts and a slow response time.

And then Extended Events came up in SQL 2008, But

Microsoft came up with Extended Events (some call them XEvents) for collecting event-driven data about the SQL Server instance and its databases with the SQL 2008 release. It requires a smaller footprint than trace events and would let developers write customized tools to work with event information.

But it lacked the GUI to directly interface with the events and that was really not a piece of good news. SQL DBAs had quite a tough time writing those complex T-SQL statements (all Extended Events objects exist inside of binary modules in packages, sys. dm_xe_packages and so on containing various Events and Targets in XML format, not much to the DBA's delight! And as expected this found very little adoption in the database industry.

And finally, The “lightweight” Extended Events of SQL 2012

Finally SQL 2012 came up with a game changer with the SSMS including a native graphical interface for Extended Events, thus enabling only a series of simple and intuitive procedures to create, deploy and monitor reasonably complex Extended Events sessions without having to use any T-SQL commands or query XML data.

It is “lightweight” for sure, considering the nearly zero overhead it has on SQL Server performance.

1. First of all, it is embedded deep within the SQL Server engine, which means it requires far less code to function.
   
   
2. Secondly, it takes a reactive approach by only collecting and sending data to their target when a pre-configured event being tracked has occurred. Hence, if that event doesn't happen, then the SQL Server engine will just not capture any event data at all! You can purposely configure an Extended Events session to stop SQL Server in case of a specific event occurrence
   

And just to add to the list, some more of the new possibilities using XEvents 2012  are the following:

1. Long-running physical I/O operations.
2. Statements that cause specific wait starts to occur.
3. SQL Server memory pressure.
4. AlwaysOn Availability Groups events.

Procedure to use Extended Events

It is managed in SQL Server 2012 through the Extended Events node in the Object Explorer window, under the Management folder in SSMS.



If you expand the Extended Events node, you'll find a Sessions folder.

To collect event data in Extended Events, you must create and configure a session that specifies exactly what data to collect.

Comparison statistics

Let's take a quick comparison of statistics using an example of a test run using stress mode configurations to drive the maximum amount of load against the test AdventureWorks 2012 SQL Server instance, for different profiling techniques:

1. Initial baseline tests: Only the default data collections in SQL Server 2012 were enabled: the default trace and the system_health event session.
   
   
2. The remaining tests collected the following events:
   
   
   1. Security Audit\Audit Login
   2. Security Audit\Audit Logout
   3. Sessions\ExistingConnection
   4. Stored Procedures\RPC:Starting
   5. Stored Procedures\SP:Completed
   6. Stored Procedures\SP:Starting
   7. Stored Procedures\SP:StmtStarting
   8. TSQL\SQL:BatchStarting
      
      
3. A server-side trace script was generated from the template using the export functionality in SQL Server Profiler.
   
   
4. The Profiler tests were run using SQL Profiler locally on the SQL Server and remotely from a separate client.
   
   
5. The Extended Events tests included evaluating the event_file, ring_buffer and new streaming provider in SQL Server 2012 separately to determine the overhead that each target might impose on the performance of the server.

The point in these tests was to show the significant overhead that Profiler incurs, regardless of where a profile was being run. Hence running SQL Server Profiler should be especially avoided on busy production servers.

The number of events in SQL 2012 XEvents (618 in SQL Server 2012 RTM, with 9 more in SQL Server 2012 SP1) was not changed much with SQL 2014 with the addition of some 145 new events and 4 new packages mostly related to Hekaton internals and runtime.)

But is everything actually over with Profiler and Traces?

As per Microsoft's diagnostic tools road map for SQL Server, SQL Trace and Profiler might be slated for retirement soon, but all is not lost. Especially for the people that might have built up a good library of SQL Profiler based monitoring sessions in all these years, Microsoft provides some migration mappings DMVs to migrate between the Profiler and Extended Events.

Also boB “The Tool Man” Taylor has released an actual conversion tool called SQL PIE (Profiler Into Events) available in the Codeplex site.
So, go ahead and continue enjoying your profiling journey with XEvents 2012.