By: Ben Snaidero
Overview
Extended Events is an event-driven framework built for monitoring Windows server systems. It allows you to correlate data generated from events within SQL Server and can also, in some cases, correlate data generated from the operating system and the applications running on it. In order for you to correlate the data gathered from within SQL Server with outside applications your event output must be directed to Event Tracing for Windows (ETW). The Extended Events architecture can be broken up into 5 main concepts. These are:
- Packages
- Targets
- Engine
- Sessions
- Tools
Extended Events Packages
An Extended Event package is simply a container for the objects used by Extended Events. It contains all the metadata for the objects that exist within Extended Events. When creating an Extended Events session you can use objects from one or more packages and there are many different packages available. Querying the sys.dm_xe_packages view you can get a listing of all of the available packages.
In many cases, when troubleshooting or monitoring SQL Server, most of the objects required will be in one of the following packages:
- package0 - Default package. Contains all standard types, maps, compare operators, actions and targets.
- sqlserver - Extended Events for Microsoft SQL Server.
- sqlos - Extended Events for SQL Server Operating System.
If you are looking to for more information for a specific feature within SQL Server some of these have their own package as well. A few examples of these are SecAudit, filestream and sqlclr. As mentioned earlier for a complete list of what's available you can query the sys.dm_xe_packages view.
Because an Extended Events session can contain objects from multiple packages a package does not need to contain all types of objects. For example, you can have a package that only contains events and actions and when you create a session you just use one of the targets defined in the default package. That said here is a list of the types of objects that a package can contain any or all of:
- Events
- Targets
- Actions
- Types
- Predicates
- Maps
Events
An event is pretty self-explanatory. It's basically a point of execution within your program, in this case SQL Server, that you are interested in tracking. When an event fires along with the time the event occurred (and the inherent fact that the event occurred) the state information is also logged. The state information is stored in a versioned schema and once a package is registered with Extended Events the set of events and their definition cannot be changed. What can be changed though is the consumer of the event does not have to consume all of the state information in the schema, it can just use what it needs. Also, once registered, these events can be used for tracing your application as well as for triggering other events which can be either synchronous or asynchronous. It is important to note that Extended Events also use an event categorization model. This model is similar to the one that is used for Event Tracing for Windows (ETW) mainly so that these two tools can be integrated. The two event properties used for categorization are channel and keyword. A channel identifies the audience of the event and can be one of Admin, Operational, Analytic or Debug. The keyword is application specific and can be used to group related events.
Targets
A target is the event consumer. Extended Events provide multiple targets that can be used to direct event output to a file, memory buffer or aggregate event data. The way that Extended Events is designed guarantees that a target will receive an event only once per session. Event targets can process events asynchronously (Event file, Event pairing, Histogram, Ring buffer) or synchronously (Event Tracing for Windows, Event Counter).
Actions
An action is a programmed response to an event. Actions must be tied to an event and each event can have its own unique set of actions. When an event fires each action is invoked synchronously. There are many different things that can be done within an action. This can include aggregating or appending to event data, storing state information or capturing a stack dump.
Types
The type object is what defines what is contained in the event data string. The following types are provided for package objects:
- event
- action
- target
- pred_source
- pred_compare
- type
Predicates
Predicates are sets of rules that are used to evaluate events when they fire. Predicates allow Extended Events to filter the captured event data on specific criteria. Predicates can also store local context information which allow them to only fire on a specified time interval or after a specified number of times the event fires. This allows you to reduce the amount of data collected when each event contains similar data.
Maps
A map table maps an internal numeric value to a more descriptive string which makes it easier for users to under the event data. A complete list of map values can be queried in the sys.dm_xe_map_values view.
Extended Events Targets
As mentioned earlier, targets are the event consumers and events are consumed by a target either synchronously or asynchronously. An important thing to note is that the database engine will disconnect from any synchronous extended events session that is processing data too slowly so that database performance is not affected by the session. It’s because of this that most targets, especially ones that can perform some extra processing/filtering, are asynchronous. Below is a short description of each of the different targets.
Asynchronous
- Event file - Writes event session output from complete memory buffers to disk.
- Event pairing - Used to determine when a specified paired event does not occur in a matched set. An example of an event pair would be lock acquired/lock released.
- Histogram - Used to count the number of times that a specified event occurs based on a specific event column or action.
- Ring buffer - Used to hold the event data in memory on a first-in first-out (FIFO) basis.
Synchronous
- Event Tracing for Windows (ETW) - Used to correlate SQL Server events with Windows operating system or application event data.
- Event counter - Counts the number of occurrences of each event in the session. Avoids the extra overhead of capturing the full event details.
Extended EventsEngine
The Extended Events engine does not do any processing of events or perform any actions when an event fires. The engine is actually a collection of services and objects that handle the management of the SQL Server Extended Events sessions. It enables you to define events, enables processing of events, manages services and objects in the system and also maintains a list of Extended Events sessions.
Extended Events Sessions
Session States
Each Extended Events session is created within the SQL Server process hosting the Extended Events engine. Throughout the lifecycle of a session it can be in any one of the following states.
- Created/exists, using CREATE EVENT SESSION, but not active
- Active, using ALTER EVENT SESSION … STATE=START
- Exists but not active, stopped using ALTER EVENT SESSION …. STATE=STOP
- Dropped, using DROP EVENT SESSION
Session Content
Even though events and targets can be used in multiple Extended Events sessions, each session is confined to its own boundary. That is, the configuration of one session cannot be affected by and also cannot have any effect on another sessions configuration. The following diagram taken from this link explains this concept.
Extended Events Tools
SQL Server provides the following tools that you can use to create and manage extended events sessions.
- TSQL – Using CREATE, ALTER and DROP EVENT SESSION you can manage an extended events session. You can also use DMVs, catalog views and system tables to query information about any already existing or default Extended Events sessions.
- SQL Server Management Studio (SSMS) - Under the Extended Events node of Object Explorer you can also start, stop or delete an Extended Events session. Under this item you can also import and export session templates.
- SQL Server PowerShell Provider – Under the SQLSERVER drive in the XEvent subfolder there is a folder for each event session. Under this folder you can view information about each session. You can also create, alter and drop sessions using the Provider.
