Monthly Archives: May 2014

Introduction to Extended Events

I have done a few presentations on the intro to Extended Events and I wanted to get the content out here on my blog for you. So, here it is:

Extended Events (XEvents) were first introduced with the release of SQL Server 2008. They were powerful but not adopted due to the fact that they were difficult to understand, setup, configure and use. They were very complex to figure out compared to traditional tools such as Profiler, SQL Trace & Activity Monitor. This was because there was no user interface and all operations were command-line or query driven. The query toolset works well for DMV’s, but DBA’s were unwilling to try to navigate the minefield that they thought XEvents were.
Just what is / are XEvents ?
Well, Books On-Line describes them as: “a lightweight, highly scalable, highly configurable performance monitoring system that uses very little system resources and allows admins to collect as much or as little information as is necessary to troubleshoot or identify a performance problem.” WOW, that’s a lot of buzz words!!
Let’s break it down.

Now, an event is ANY recordable activity of significance that occurs in the normal execution path of a piece of code (disk read, memory page out, etc). The Event Tracing for Windows (ETW) framework is a process within the Windows Operating System of logging kernel or application events to a log file.

Event Tracing for Windows Concepts
Here is a diagram of the internals of the Event Tracing for Windows framework:

Event Tracing for Windows framework

Legend:
The events themselves are sometimes sourced from providers which are applications that contain event tracing information.
Event tracing sessions are merely enable the capture of events that occur in the system. The events and associated information is stored in a buffer until that information is flushed.
Consumers receive the events from a log file or in real-time. Consumers can accept multiple sessions simultaneously and the system will deliver events in chronological order.
Controllers start and stop the event trace session and enable providers.

ETW References:
MSDN: Event Tracing
MSDN Magazine: Improve Debugging And Performance Tuning With ETW

The Extended Events (XEvents) feature within SQL Server is an architecture that uses and builds on this ETW framework.

XEvents Concepts
Here is a diagram of the internals of XEvents:

Exended Events within SQL Server

Legend:
The XEvents Engine is a collection of services that:
     Enables the definition of events
     Enables processing event data
     Manages XEvents objects in the system
     Maintains a list of XEvents sessions and manages access to that list
An XEvents Package is a container for XEvent objects. The packages are package0, sqlserver, sqlos, SecAudit, sqlclr and ucs (unified communications stack), etc. Contained in an XEvent package is a combination of the following:
     Events: An event is a point of interest where something happens in the execution path of SQL Server. There are 446 events in SQL 2012 and 763 events in SQL 2014 and each one corresponds to an event from SQL Trace. When an event fires, that event returns state information relating to the corresponding point of code. This basic information can be added to by the use of Actions
     Actions: These are bound to the event during the definition of the Event session and perform a specific task when the event fires. This task can be: collecting additional information from memory, a stack dump and data inspection, aggregating event data, run-time statistics collection or user-input collection. In SQL 2012, there are 43 actions that can be used.
     Types: Straight forward, data type of the information contained in the event. There are 28 types in SQL 2012 including ansi_string, unicode_string, boolean, guid, filetime & callstack.
     Maps: This is the lookup table that will let the admin know what the information inside the event actually means. For example, the “lock_mode” map system table will map the key to the value for various types of locks that can occur.
     Predicates: These are the set of rules that control the circumstances under which the event actually fires and are used to evaluate events as they are processed. They also provide us with the ability to filter the events that we are capturing.
     Targets: These are the event consumers that process events, either synchronously or asynchronously. Synchronous targets have the event buffered to them by the executing thread, and asynchronous targets have the events dispatched to them by the dispatchers in the Database Engine when the buffers fill up.
XEvents Targets are the data consumers or endpoints of the event data. Targets can write to a file, store event data in a memory buffer, or aggregate event data. Targets can process event data both synchronously, on the thread that fired the event, or asynchronously on a background system thread. For performance reasons, asynchronous targets should be used whenever possible. When an event fires, the synchronous targets are served the data immediately to minimize the impact to the executing session. After all of the synchronous targets have processed the event data, the data is queued in the session buffers where it waits to be dispatched to the asynchronous targets.

XEvent Targets:
     Event File (asynchronous)- Creates two types of files, log and metadata for writing complete buffers to disk. The metadata file describes the output log file and allows the events and associated actions to be parsed correctly. The file names and locations must be provided to use this target. Use to write event session output from complete memory buffers to disk.
     Event Ring Buffer (asynchronous) – Temporarily holds event data in memory, in either a first-in first-out mode where events are discarded in the order received or, on a per-event mode where a set number of each event type is kept and then discarded.
     Event Pairing (asynchronous) – Many kinds of events occur in pairs, such as lock acquisitions and lock releases. Use to determine when a specified paired event does not occur in a matched set. Matches two events based on one or more columns present in the events. Matched pairs are then discarded leaving only unmatched events for analysis. To use this target, you must provide the events to be matched and the columns to be used for the matching.
     Event Tracing for Windows (ETW) (synchronous) – Use to correlate SQL Server events with Windows operating system or application event data. Only one ETW session to the SQL Server provider can be open at a time. For this reason, the ETW target obtains a reference to this one session for all ETW targets regardless of their XEvents session scope.
     Event Counter (synchronous) – Counts all specified events that occur during an XEvents session. Use to obtain information about workload characteristics without adding the overhead of full event collection.
     Event Bucketing (asynchronous) – Creates buckets based on the user-specified columns, and only collects and retains data for the specified source. If the event data is too large for retention, it is truncated. Use to count the number of times that a specified event occurs, based on a specified event column or action. This is also referred to as the Histogram Target.

An XEvents Session is just a collection of events, the actions that occurs when an event fires and the targets that are the destination of the event information. XEvents Session options also define the start and stop parameters, timeline of events and actions, memory buffer size, and how event loss can occur if the buffer fills up.

XEvent Session State

An XEvents Session has implied boundaries that contain the configuration and session objects (events, actions or targets). These boundaries do not prevent the same object from being used in multiple sessions. They just define the context of that specific session. Policies are attached to sessions to define buffering and dispatch.
XEvent Session Boundaries

XEvent References:
MSDN: Extended Events
MSDN: Extended Events Tools

Here is a link to the PowerPoint slide deck that I use in my presentations on this subject:
PowerPoint Slide Deck on Extended Events

Please Note:
When you install the RTM or SP1 versions of SQL Sever 2012, there is a bug in the “Activity Tracking” template within the Extended Events management console UI and wizard. My good friend Jonathan Kehayias ( blog | twitter ) discovered the bug and has posted a fix on his blog. You can get it here.

Aaaaaand, that’s it.
I will be posting a follow up soon with a demo on how to run XEvents on your server to perform some basic event capturing.