SQL Server Extended Events were first introduced in SQL Server 2008, and provided a new mechanism for capturing information about events inside the Database Engine that was both highly performant and highly configurable. Designed from the ground up with performance as a primary focus, Extended Events may seem a bit odd at first look, especially when you compare it to SQL Trace. However, as you begin to work with Extended Events, you will most likely change how you think about tracing problems, and will find the power in the design of Extended Events. This series of blog posts should help you get a jumpstart to using Extended Events if you haven’t already. This post will review Extended Events from a high level, providing the basic background upon which the rest of the series will build on.
The Extended Events Engine
The Extended Events engine is the central service that manages the objects and resources for Extended Events. The engine manages a pool of workers known as dispatchers that are responsible for processing event information from the memory buffers to the targets based on the session options. The engine also maintains the information for Event Sessions that have been created in the instance, and the metadata about the packages that have been registered by modules inside the engine. The engine itself contains no object metadata. The engine essentially provides the platform for which packages can be registered and event sessions can be created, and event information can be consumed.
Packages are registered by modules in the Extended Events Engine and contain the information about what Extended Events objects the module contains. Packages are the top level containers of metadata for the other objects that exist inside of Extended Events. Packages can contain a combination of events, actions, targets, maps, predicates and types. In SQL Server 2008, there are only four packages; sqlserver, sqlos, package0, and a private internal use only package SecAudit, which is used by Server Audits. All of these packages are loaded by the sqlservr.exe module. In Denali CTP1, there are three additional packages, one more for sqlserver but loaded from different module, a new sqlclr package that is loaded from sqlservr.exe, and a new ucs (unified communications stack) package that is loaded from sqlservr.exe. In addition to these changes the package0 and sqlos modules are no longer loaded by sqlservr.exe and instead are loaded by sqldk.dll.
Events provide information about the execution of the module that loaded the package containing the event. An event corresponds to a specific point in code where something of interest occurs inside the Database Engine. In SQL Server 2008, there are 253 events that can be used to capture information and as previously mentioned, while there are a lot of events, not all of the common events from SQL Trace were implemented in Extended Events. In SQL Server Denali CTP1, the number of events has expanded to 446, and now includes a corresponding event for all of the events from SQL Trace. Each event returns information specific to the point in code corresponding to the event, which I refer to as is basic payload of information. The amount of information returned in the base payload is set by the events definition in the package metadata, but can be added to through the use of Actions.
Actions are bound to individual events in the definition of the Event Session in the engine, and perform a specified task when the event fires. Actions generally do two different types of tasks, they either collect additional information that is added to the firing events base payload, generally global state information like session_id, sql_text, etc. and they can perform tasks inside the engine like inserting a debug_break, or performing a memory dump for analysis. In SQL Server 2008 there are 35 actions that can be used. In SQL Server Denali CTP 1 there are 43 actions that can be used and include new ones that map to Trace Columns like context_info, database_name, and event_sequence that were unavailable in SQL Server 2008.
Targets are the event destinations for Extended Events. Most of the targets are memory resident, meaning that they exist in memory, and only while an event session is active in a started state on the instance. These can be useful for short term troubleshooting, when a long term persisted version of the events is not necessary. A file target also exists which functions similar to the Trace File in SQL Trace and collects the event information in files on the file system that can then be transferred for analysis by another person at a later point in time. Two types of targets exist in Extended Events, synchronous targets which have the events buffered to them by the executing thread, and asynchronous targets that have the events dispatched to them by the dispatchers in Engine when the buffers fill up, or the maximum duration for dispatch defined by the session is exceeded for an event. A more detailed coverage of the specific targets will be done a little bit later in this series.
Maps provide lookup information for the information that is available inside Extended Events. Maps provide a key/value pairing for specific types of information that can be used in defining event sessions, and correlating the information captured by event sessions. An example of a map would be the wait_types, which maps the engine key to logical name for all wait_types that can be fired inside of SQL Server.
Predicates provide the filtering mechanism inside of Extended Events and are defined on individual events and control the circumstances under which the event actually fires. Predicates in Extended Events offer short-circuiting, where the first false evaluation in the predicate string terminates the evaluation and prevents the event from firing, making evaluation of properly defined predicates highly performant. Predicates can be defined on any of the data returned by the events base payload, as well as on global state information that is available inside of the engine. Predicates can be defined two different ways, using common filtering clause criteria such as <,>, >=, <=, =, <>, LIKE, NOT LIKE, etc. but can also be defined using textual comparators that are included in package metadata. Inside the Engine when a session is started, any predicate using common filtering clause gets converted to its corresponding textual comparator.
Types define the data type for the information elements inside of Extended Events. There are 29 different types in SQL Server 2008, but only 28 inside of SQL Server Denali CTP1; the database_context type was removed. In addition to the package defined types, some event data elements will define the data type as a map name, which provides information about the type of information that will be returned from the data element.
Event Sessions are a collection of events, their corresponding actions and predicates, and the targets that will be the destinations for the events being collected. Defined Event Sessions exist in the Extended Events Engine until they are explicitly dropped allowing the session to be started and stopped as needed without having to recreate the session, even beyond service restarts. Event Sessions have a number of configuration options that control how the session functions, including the ability to start automatically when the service starts up, the maximum duration of time between an event firing and being dispatched to the targets, the amount of memory available to the event session for buffering, how that memory is partitioned, and when/if/how event loss can occur when the buffer space fills up faster than the dispatchers can deliver the events. and have a number. An event session requires at least one event at creation, but does not require a target, allowing side acting actions like a memory dump to occur without having to actually dispatch the event for consumption.
With the basics out of the way, in the next post we’ll look at the Metadata DMV’s and how to get the information about the objects available in Extended Events. For more information about Extended Events in the mean time take a look at the Using SQL Server 2008 Extended Events whitepaper I wrote on MSDN.