By Jonathan Kehayias, SQL Server MVP. You can view Jonathan’s blog at http://sqlblog.com/blogs/jonathan_kehayias/.

Introduction to Extended Events

As SQL Server matures with time, the diagnostics tools available to administrators to troubleshoot problems when they arise have also matured. SQL Server 2000 had very limited diagnostic tools when compared with SQL Server 2005. SQL Trace and SQL Server Profiler were often the tools of choice for administrators to identify problems with performance, along with DBCC and individual trace flags like 1205 for deadlocks, and 3605 to log results to the SQL Server error log. With SQL Server 2005, the diagnostic tools expanded to include the dynamic management views, which provide a deep view into the internal workings of SQL Server, additional trace events for SQL Trace and SQL Server Profiler including the deadlock graph and Showplan XML events, the ability to import performance counter logs in SQL Server Profiler to view trace events along with their impact on the system, and WMI events which use Service Broker endpoints for event notifications. SQL Server 2008 continues to build upon the diagnostic tools with a new feature called Extended Events.

Extended Events is an extended event handling system for SQL Server. It has a highly scalable and highly configurable architecture that allows administrators to collect as much or as little information as is necessary to troubleshoot or identify a problem. It is important to note that it is not currently a replacement for the existing diagnostic features of SQL Server, though it has many parallel functionalities to SQL Trace.

One of the key features of Extended Events is its ability to collect data that can be output to an Event Tracing for Windows (ETW) target, which allows the data to be correlated with data collected from the operating system through ETW. Previously in SQL Server 2005, it was possible to collect SQL Trace data with ETW; however, this was limited to SQL Trace events only. In SQL Server 2008, Extended Events data for wait events can be correlated with system kernel ETW (file) data in a single view to isolate the specific cause of the waits, as being a true I/O bottleneck, or perhaps a major file copy operation in the operating system that is causing the delays in SQL Server.

Note   As of the publication of this white paper, the current release of SQL Server 2008 is SQL Server 2008 Service Pack 1 (SP1). SQL Server 2008 SP1 contains fixes to bugs that affected Extended Events in the RTM version. It is recommended that you keep SQL Server 2008 up to date with the latest service pack and cumulative updates. To find a comprehensive list of bugs that affect Extended Events, potential workarounds, and release information for when they are fixed, see the following post on the author’s blog: http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/05/17/updates-to-extended-events-whitepaper.aspx.

Extended Events Architecture

Extended Events Engine

The Extended Events Engine is a collection of services and objects that allow for event session definition, processing of event data, and management of event sessions. The engine is completely separate from the event definition metadata; that is to say that the engine itself does not contain any event objects. These instead are provided by the processes that use the Extended Events Engine, which contain packages with the event metadata, which is loaded into the event engine for use.

This separation of the engine from the packages containing events ensures that all events are handled equally by the engine, and allows for the addition of new event objects in future releases of SQL Server. It also allows Microsoft Customer Service and Support (CSS) to provide custom event packages, which may not be appropriate for standard release, to customers for advanced troubleshooting of complex problems. The Extended Events Engine can only have one instance in a host process and has its own thread pool called the dispatcher pool which is used for handling events raised in active sessions. The engine also manages the enumeration of the sessions that are running on the server.

Figure 1: Extended Events Architecture

Packages

All Extended Events objects exist inside of binary modules in containers known as packages. Packages register with the Extended Events Engine when their associated module is loaded. Packages can contain any or all of the following Extended Events objects: events, targets, actions, types, predicates, and maps. The metadata for what packages are available to Extended Events can be read from the sys.dm_xe_packages DMV. SQL Server 2008 contained four packages at RTM, but only three of them can be used in user-defined event sessions. The SecAudit package is an internal-use-only package that is used by the new audit functionality in SQL Server 2008 to provide auditing.

The following query returns the available packages in the server:

SELECT p.*
FROM sys.dm_xe_packages p
WHERE (p.capabilities IS NULL OR p.capabilities <> 1);

Events

Events represent known points in code that may be of interest. Within the Extended Events architecture, events are categorized similar to the way events are categorized in Event Tracing for Windows (ETW), by channels and keywords. This was done to allow Extended Events to integrate with the ETW framework and the currently available tools for ETW. An event belongs to one of four channels, which is used to signify to whom the event may be of interest.

In addition to belonging to a channel, events are grouped by keywords which are essentially the part of the system that is claiming to have fired the event. Keywords are very much like the object groupings in SQL Server Profiler for SQL Trace. There are currently 24 specific keywords for grouping events. Events for the same keyword/channel combination may exist across different packages. For example, there are I/O and debug events in both the SQLServer and SQLOS packages.

The following query returns all available events:

SELECT p.name AS package, c.event, k.keyword, c.channel, c.description
FROM
(
SELECT event_package=o.package_guid, o.description,
       event=c.object_name, channel=v.map_value
FROM sys.dm_xe_objects o
       LEFT JOIN sys.dm_xe_object_columns c ON o.name = c.object_name
       INNER JOIN sys.dm_xe_map_values v ON c.type_name = v.name
              AND c.column_value = cast(v.map_key AS nvarchar)
WHERE object_type='event' AND (c.name = 'channel' OR c.name IS NULL)
) c left join
(
       SELECT event_package=c.object_package_guid, event=c.object_name,
              keyword=v.map_value
       FROM sys.dm_xe_object_columns c INNER JOIN sys.dm_xe_map_values v
       ON c.type_name = v.name AND c.column_value = v.map_key
              AND c.type_package_guid = v.object_package_guid
       INNER JOIN sys.dm_xe_objects o ON o.name = c.object_name
              AND o.package_guid=c.object_package_guid
       WHERE object_type='event' AND c.name = 'keyword'
) k
ON
k.event_package = c.event_package AND (k.event = c.event OR k.event IS NULL)
INNER JOIN sys.dm_xe_packages p ON p.guid=c.event_package
WHERE (p.capabilities IS NULL OR p.capabilities & 1 = 0)
ORDER BY channel, keyword, event

Each event has a set of columns that can be found in the sys.dm_xe_object_columns table. There are three types of columns that can exist for an event. The first type is a read-only column which is a set of descriptor columns that provide information about the event like the event ID, UUID, version, channel, and keyword.

SELECT p.name AS PackageName,
              o.name AS EventName,
              c.column_id,
              c.column_type,
              c.name ColumnName,
              c.column_value,
              c.description
FROM sys.dm_xe_objects o
       INNER JOIN sys.dm_xe_packages p
              ON o.package_guid = p.guid
       INNER JOIN sys.dm_xe_object_columns c
              ON o.name = c.object_name
WHERE o.object_type = 'event'
  AND c.column_type = 'readonly'
  AND (p.capabilities IS NULL OR p.capabilities <> 1)
ORDER BY PackageName, EventName, column_type, column_id;

The second type of column is the data column, which defines the default payload or column set that is collected when the event fires. These columns are automatically included with the event details when they are dispatched to the session targets.

SELECT p.name AS PackageName,
              o.name AS EventName,
              c.column_id,
              c.column_type,
              c.name ColumnName
FROM sys.dm_xe_objects o
       INNER JOIN sys.dm_xe_packages p
              ON o.package_guid = p.guid
       INNER JOIN sys.dm_xe_object_columns c
              ON o.name = c.object_name
WHERE o.object_type = 'event'
  AND c.column_type = 'data'
  AND (p.capabilities IS NULL OR p.capabilities <> 1)
ORDER BY PackageName, EventName, column_type, column_id;

In addition to the descriptor columns and the default payload, some events also have a third type of column called a customizable column which functions similar to a lightweight predicate on the event. These columns are collected similar to actions in the event session and have an added expense to the data collection. Because the cost to collect this data could affect performance, it is only collected when specified explicitly as a part of the event definition in the session. The following query returns the customizable parameters for events:

SELECT p.name AS PackageName,
              o.name AS EventName,
              c.column_id,
              c.column_type,
              c.name ColumnName
FROM sys.dm_xe_objects o
       INNER JOIN sys.dm_xe_packages p
              ON o.package_guid = p.guid
       INNER JOIN sys.dm_xe_object_columns c
              ON o.name = c.object_name
WHERE o.object_type = 'event'
  AND c.column_type = 'customizable'
  AND (p.capabilities IS NULL OR p.capabilities <> 1)
ORDER BY PackageName, EventName, column_type, column_id;

Targets

Targets are the data consumers or destination endpoints for event data in Extended Events. 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. Keeping with the flexibility of the Extended Events architecture, any target can consume any event.

Most of the targets are configurable through the use of customizable options which allow definition of items like file names and locations for the event file targets and maximum memory size for the ring buffer target. A complete overview of each of the targets can be found in the following SQL Server Books Online topic:

SQL Server Extended Events Targets
(http://msdn.microsoft.com/en-us/library/bb630339.aspx)

The following query returns all of the targets available in the server:

SELECT p.name AS PackageName,
       o.name AS TargetName,
       o.description AS TargetDescription
FROM sys.dm_xe_objects o
       INNER JOIN sys.dm_xe_packages p
              ON o.package_guid = p.guid
WHERE o.object_type = 'target'
  AND (p.capabilities IS NULL OR p.capabilities <> 1)
ORDER BY PackageName, TargetName;

Each target has a list of parameter columns that can be used to configure the target. Some of the columns are mandatory, while others are optional. The following query returns the parameters for the targets available in the server:

SELECT p.name AS PackageName,
              o.name AS TargetName,
              c.name AS ParameterName,
              c.type_name AS ParameterType,
              case c.capabilities_desc
                     when 'mandatory' then 'yes'
                     else 'no'
              end AS [Required]
FROM sys.dm_xe_objects o
       INNER JOIN sys.dm_xe_packages p
              ON o.package_guid = p.guid
       INNER JOIN sys.dm_xe_object_columns c
              ON o.name = c.object_name
WHERE o.object_type = 'target'
  AND (p.capabilities IS NULL OR p.capabilities <> 1)
ORDER BY PackageName, TargetName, [Required] desc;

Actions

Actions are bound to specific events and extend the event in a wide range of ways. They are invoked synchronously on the thread that fires an event, and therefore can have an impact to performance in some cases. Actions can be used to:

• Aggregate event data
• Append additional data to event data
• Capture a stack dump and inspect data
• Store state information in a local context using variable storage
• Stop server execution creating a debug breakpoint
  
  

Because actions execute synchronously, they should be used only where necessary to capture needed additional information. Certain actions like the package0.debug_break should not be used in production environments unless directed by the CSS team as a part of an open support incident. The following query returns the actions available on the server:

SELECT p.name AS PackageName,
       o.name AS ActionName,
       o.description AS ActionDescription
FROM sys.dm_xe_objects o
       INNER JOIN sys.dm_xe_packages p
              ON o.package_guid = p.guid
WHERE o.object_type = 'action'
  AND (p.capabilities IS NULL OR p.capabilities & 1 = 0)
ORDER BY PackageName, ActionName;

Types

Event data is provided as a collection of bytes that are strung together. To interpret this data, types provide the length and characteristics of the byte collection to the Extended Events Engine. Most columns have easily recognizable types, (int32, unicode_string, float32, boolean, and so on) that are common in most programming languages, but some have SQL Server–specific types (callstack, sos_context, database_context, and so on).

The following query returns the types available:

SELECT p.name AS PackageName,
       o.name AS TypeName,
       o.description AS TypeDescription
FROM sys.dm_xe_objects o
       INNER JOIN sys.dm_xe_packages p
              ON o.package_guid = p.guid
WHERE o.object_type = 'type'
  AND (p.capabilities IS NULL OR p.capabilities & 1 = 0)
ORDER BY PackageName, TypeName;

It is important to note that each column in an object’s payload has a type associated with it. The importance of this is clear when defining predicates. Types are not used as a part of a session definition.

Predicates

Predicates allow for dynamically filtering events at the point in time when the event is fired to determine whether the event being fired meets the criteria in any active event session for further processing. This allows an event session to be targeted to a specific problem rather than collecting full event tracing for later filtering. For certain targets like the ring buffer, this is important because events begin to be trimmed from the memory set when the memory becomes full.

Predicates are Boolean expressions that evaluate to either true or false and can filter on either global state data or local event data. The local event data is defined by an event’s payload or column definition in its columns schema. The global state data is defined by the available objects or predicate source columns which are in the sys.dm_xe_objects DMV with an object_type of pred_source.

SELECT p.name AS PackageName,
       o.name AS ColumnName,
       o.description AS ColumnDescription
FROM sys.dm_xe_objects o
       INNER JOIN sys.dm_xe_packages p
              ON o.package_guid = p.guid
WHERE o.object_type = 'pred_source'
  AND (p.capabilities IS NULL OR p.capabilities & 1 = 0)
ORDER BY PackageName, ColumnName;

Additionally, some predicates can store state information that allows the event to only fire every nth time that it is encountered, or for the event to fire for the first n times only. This allows for sampling-based event captures, and can also help isolate minimize event firing for rapidly occurring instances of an event to make processing the capture data easier.

Predicates are evaluated using the standard operators for mathematics. There are also a special set of operators called comparator objects that can be used for filtering. A list of the comparator objects can be found with the following query:

SELECT p.name AS PackageName,
       o.name AS ComparatorName,
       o.description AS ComparatorDescription
FROM sys.dm_xe_objects o
       INNER JOIN sys.dm_xe_packages p
              ON o.package_guid = p.guid
WHERE o.object_type = 'pred_compare'
  AND (p.capabilities IS NULL OR p.capabilities & 1 = 0)
ORDER BY PackageName, ComparatorName;

Comparators use syntax similar to calling a user defined function. The syntax is:

package_name.predicate_comparator_name (<predicate_source_column>, <value>)

The comparator and source column must have the same data type, and the data type of the source cannot be converted. The following script demonstrates the use of a comparator predicate:

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='BufferManagerWatcher')
    DROP EVENT SESSION [BufferManagerWatcher] ON SERVER;
CREATE EVENT SESSION [BufferManagerWatcher]
ON SERVER
ADD EVENT sqlserver.buffer_manager_page_life_expectancy(
     WHERE (([package0].[less_than_equal_uint64]([count],(3000)))))
ADD TARGET package0.ring_buffer(
     SET max_memory=4096)

The above event session fires anytime the page life expectancy falls below 3000 microseconds on the server.

Maps

Maps are tables that provide lookup data for internal values, so that an end user can know the meaning of the value. Maps function exactly like a lookup table would in a normalized database structure. The following query provides the available maps and the value definitions:

SELECT name, map_key, map_value
FROM sys.dm_xe_map_values
ORDER BY name, map_key

The maps contain everything from broker states to wait-types, and even include the channel and keyword lookups for categorizing events into their respective groupings.

Maps like types are descriptors in the context of Extended Events. They are not used in defining an Extended Events session directly. They do however provide the necessary lookup point for the value to assign to a predicate for filtering events or consuming target data.

Sessions

Sessions are a collection of events, their associated actions and predicates, and session targets. A session may have one or more events and targets. 

Figure 2: Session separation

Sessions also serve as an isolation point for event and target configurations. In the above example, the ring_buffer target is used by both sessions but with different memory configurations. Events defined in a session are also isolated from other instances of the same event defined in a different session. This flexibility allows for the creation of a session that collects additional information through actions only for a specific session, while all other sessions collect the basic event data only for the event.

One of the most flexible parts of the Extended Events architecture is that an event session can be defined using any event from any of the packages loaded into the Extended Events Engine in combination with any action from any package, any predicate source or predicate comparator from any package, and any target from any package. There are a few audit related objects in the Extended Events metadata that do not follow this rule, which are identifiable by the capabilities column of the sys.dm_xe_objects DMV having a value of one. These have been filtered from the results set returned by any of the queries in this document. This flexibility simplifies the definition of event sessions.

Out of the box, SQL Server 2008 includes a default event session that collects health information about the server named “session_health”. This session collects the following information:

• The sql_text and session_id for any sessions that encounter an error with severity >=20 or for error numbers 17803, 791, 802, 8645, 8651, 8657, or 8902.
• Any "non-yielding" errors that are raised.
• Any deadlocks that occur.
• The callstack, sql_text, and session_id for any sessions that have waited on a latch or other resource for > 15 seconds.
• The callstack, sql_text, and session_id for any sessions that have waited on a lock for > 30 seconds.
• The callstack, sql_text, and session_id for any sessions that have waited for an extended period of time for "external" waits or "pre-emptive waits".

 The DDL statement for this event session is:

CREATE EVENT SESSION session_health
ON SERVER
ADD EVENT sqlserver.error_reported (
       ACTION (package0.callstack, sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_stack)
       WHERE ([severity]>=(20) OR ([error]=(17803) OR [error]=(701) OR [error]=(802) OR [error]=(8645) OR [error]=(8651) OR [error]=(8657) OR [error]=(8902))) ),
ADD EVENT sqlos.scheduler_monitor_non_yielding_ring_buffer_recorded,
ADD EVENT sqlserver.xml_deadlock_report,
ADD EVENT sqlos.wait_info (
       ACTION (package0.callstack, sqlserver.session_id, sqlserver.sql_text)
       WHERE ([duration]>(15000) AND ([wait_type]>(31) AND ([wait_type]>(47) AND [wait_type]<(54) OR [wait_type]<(38) OR [wait_type]>(63) AND [wait_type]<(70) OR [wait_type]>(96) AND [wait_type]<(100) OR [wait_type]=(107) OR [wait_type]=(113) OR [wait_type]>(174) AND [wait_type]<(179) OR [wait_type]=(186) OR [wait_type]=(207) OR [wait_type]=(269) OR [wait_type]=(283) OR [wait_type]=(284)) OR [duration]>(30000) AND [wait_type]<(22))) ),
ADD EVENT sqlos.wait_info_external (
       ACTION (package0.callstack, sqlserver.session_id, sqlserver.sql_text)
       WHERE ([duration]>(5000) AND ([wait_type]>(365) AND [wait_type]<(372) OR [wait_type]>(372) AND [wait_type]<(377) OR [wait_type]>(377) AND [wait_type]<(383) OR [wait_type]>(420) AND [wait_type]<(424) OR [wait_type]>(426) AND [wait_type]<(432) OR [wait_type]>(432) AND [wait_type]<(435) OR [duration]>(45000) AND ([wait_type]>(382) AND [wait_type]<(386) OR [wait_type]>(423) AND [wait_type]<(427) OR [wait_type]>(434) AND [wait_type]<(437) OR [wait_type]>(442) AND [wait_type]<(451) OR [wait_type]>(451) AND [wait_type]<(473) OR [wait_type]>(484) AND [wait_type]<(499) OR [wait_type]=(365) OR [wait_type]=(372) OR [wait_type]=(377) OR [wait_type]=(387) OR [wait_type]=(432) OR [wait_type]=(502)))) )
ADD TARGET package0.ring_buffer (
       SET max_memory=4096)

These events were selected by the Microsoft CSS team because they can be turned on without compromising the performance of the instance of SQL Server. Because it uses the ring_buffer target, when the target gets to 4MB, the oldest events are flushed from the target and overwritten. To look at the information contained in the buffer, the following query can be executed:

SELECT CAST(xet.target_data AS xml)
FROM sys.dm_xe_session_targets xet
       INNER JOIN sys.dm_xe_sessions xe
              ON (xe.address = xet.event_session_address)
WHERE xe.name = 'system_health'

This outputs an XML document containing the event capture data contained in the ring buffer target currently, as well as counts of total events and dropped events. Further information on how to read the event data can be found later in this document.

In addition to the events and targets, each session has six configurable options that control how the event session’s buffers are configured, whether the event session starts automatically with the instance of SQL Server, and how the event session can affect performance. If no value is set during the session creation, a default set of values is assigned by the Extended Events Engine. The following table provides the six options, a description of each option, and the default value assigned by the Extended Events Engine if no values are specified.

For large or busy servers, the default set of options may not be appropriate for performance. Setting the MEMORY_PARTITION_MODE option to PER_CPU on a multi-core, multi-processor server, along with setting a value for the MAX_DISPATCH_LATENCY can reduce the impact of running an event session on server performance.

Extended Events DDL

As with any other object in SQL Server, Extended Events sessions are created and managed by DDL statements. The new DDL commands for managing Extended Events sessions are listed in the following table.

When an Extended Events session is created, it is not started by default, and no mechanism exists to create the configuration and start the session in a single command. The new session always has to be started with the ALTER EVENT SESSION command to set the state to start. An already-started event session can be altered to add new events and targets, or remove existing ones while the session is still running and collecting data. It is not necessary to stop the event session to change its definition.

System Objects

As of the RTM of SQL Server 2008, there is no graphical user interface for Extended Events. This makes a thorough understanding of the system objects for managing Extended Events important. There is however, an open-source community-based application for managing Extended Events that is available for download from CodePlex:

Extended Events Manager
(http://www.codeplex.com/ExtendedEventManager)

Dynamic Management Views

The Extended Events DMVs provide information about the metadata available for creating event sessions in the server, as well as information on the sessions that currently exist, and the state of the dispatcher pools used by the Extended Events Engine. The available DMVs are listed in the following table.

Catalog Views

The catalog views contain information on the defined event sessions in the instance of SQL Server. They can be used to determine which events are defined on the existing sessions and which targets consume the event session data.

The following example demonstrates how the catalog views can be used to retrieve the definition of the default “system_health” session that comes installed with SQL Server 2008.

SELECT name,
              event_retention_mode_desc AS event_retention_mode,
              max_dispatch_latency,
              max_memory,
              max_event_size,
              memory_partition_mode_desc AS memory_partition_mode,
              track_causality,
              startup_state
FROM sys.server_event_sessions
WHERE name = 'system_health'
SELECT package, e.name, predicate,
(
SELECT package + '.' + name + ', '
FROM sys.server_event_session_actions a
WHERE a.event_session_id = e.event_session_id
  AND a.event_id = e.event_id
ORDER BY package, name
FOR XML PATH('')
) AS Actions
FROM sys.server_event_session_events e
INNER JOIN sys.server_event_sessions es ON e.event_session_id = es.event_session_id
WHERE es.name = 'system_health';
SELECT package, t.name,
(
SELECT name + '=' + cast(value AS varchar) + ', '
FROM sys.server_event_session_fields f
WHERE f.event_session_id = t.event_session_id
  AND f.object_id = t.target_id
FOR XML PATH('')
) AS options
FROM sys.server_event_session_targets t
INNER JOIN sys.server_event_sessions es ON t.event_session_id = es.event_session_id
WHERE es.name = 'system_health';

The output is the session definition, followed by the individual events, and then the targets. An XML PATH sub-query is used to generate a comma separated list of actions for each event as well as a comma separated list of options for each target to simplify display of the session definition.

Consuming Target Data

After an event session has been created and is running, the data collects in the targets specified for future usage. The sys.dm_xe_session_targets DMV contains the target data for all targets except the file targets, which instead needs to be read using the sys.fn_xe_file_target_read_file() table valued function. To get the target data in a useable format, cast the target_data column from the sys.dm_xe_session_targets DMV to XML.

While the various target outputs do not conform to any XML schema, and due to the flexibility of the Extended Events architecture they never could, there is a standardized structure that is applied based on the firing event’s payload and action definition. For each fired event, the XML document has an “event” child node:

<event name="" package="" id="" version="" timestamp="">
</event>

The attributes of this node describe the event. The timestamp is the time that the event was logged by the ring buffer, and not the time the event actually fired. It is also stored in UTC date/time, and not local date/time.

Each event then has one or many data child nodes with the following schema for each column defined by its basic payload.

<data name="">
  <type name="" package="" />
  <value> </value>
  <text />
</data>

The name attribute of the data node is the name of the column. The type node holds the name and package for the data type of the data held in the value node under the data node. The text node contains the text associated with an event if text data exists.

After the base event payload column data nodes, a child node exists for each action that was defined in the event payload with the following schema:

<action name="" package="">
  <type name="" package="" />
  <value>  </value>
  <text />
</action>

The action child nodes, sub-nodes and attribute definition are just like the data nodes, only the action node also contains the package name as an attribute along with the actual action name. As an example, an event with two columns in its basic payload and one action defined on it would have an output schema such as the following:

<event name="" package="" id="" version="" timestamp="">
  <data name="">
    <type name="" package="" />
    <value> </value>
    <text />
  </data>
  <data name="">
    <type name="" package="" />
    <value> </value>
    <text />
  </data>
  <action name="" package="">
    <type name="" package="" />
    <value>  </value>
    <text />
  </action>
</event>

Ring Buffer Target

The following query retrieves the ring_buffer target data for the default system_health session:

SELECT name, target_name, CAST(target_data AS XML) target_data
FROM sys.dm_xe_sessions s
       INNER JOIN sys.dm_xe_session_targets t
              ON s.address = t.event_session_address
WHERE s.name = 'system_health'

To view the complete target data, click on the XML data in the target data column. The XML document opens in a new window. The ring buffer target XML always has a base parent node with the following schema:

<RingBufferTarget eventsPerSec="" processingTime="" totalEventsProcessed="" eventCount="" droppedCount="" memoryUsed="">
  <event />
</RingBufferTarget>

The event nodes are filled based on the shared event node XML definition information.

File Targets

The file targets do not store their event information in memory where it is queryable through the sys.dm_xe_session_targets DMV. Instead, a system table-valued function must be used to get the event data from the files themselves. The following query creates a session using a file target for the errorlog_written event which is triggered when the SQL Server error log is written to.

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='FileTargetDemo')
    DROP EVENT SESSION [FileTargetDemo] ON SERVER;
CREATE EVENT SESSION [FileTargetDemo]
ON SERVER
ADD EVENT sqlserver.errorlog_written
ADD TARGET package0.asynchronous_file_target(
     SET filename='c:\FileTargetDemo.etl', metadatafile='c:\FileTargetDemo.mta')
ALTER EVENT SESSION [FileTargetDemo]
ON SERVER
STATE=START

To trigger the event, run DBCC CHECKDB against a few databases, which logs information into the error log. Then run the following query to get the file names for the target from the active session DMVs.

SELECT name, target_name, CAST(target_data AS XML) target_data,
       (SELECT c.column_name + '=' + c.column_value + ', '
        FROM sys.dm_xe_session_object_columns c
        WHERE s.address = c.event_session_address
                     AND t.target_name = c.object_name
                     AND c.object_type = 'target'
        ORDER BY column_id
        for xml path('')) AS options
FROM sys.dm_xe_sessions s
       INNER JOIN sys.dm_xe_session_targets t
              ON s.address = t.event_session_address
WHERE s.name = 'FileTargetDemo'

With this information, the sys.fn_xe_file_target_read_file table-valued function can be used to query the files. Notice that the period separator between the file name and extension has been replaced with an asterisk (*) as a wildcard so that the table-valued function continues to read through any rollover files that may exist on disk.

SELECT *
FROM sys.fn_xe_file_target_read_file('c:\FileTargetDemo*etl', 'c:\FileTargetDemo*mta', null, null)

The output of the table valued function is a table of results. The event_data column can be cast as XML to get the event information in the same shared XML definition that the other targets use. The difference with the file target is that the information is read from the file and displayed in a tabular form.

Figure 3: sys.fn_xe_file_target_read_file Results Grid

The sys.fn_xe_file_target_read_file function accepts four input parameters; @path nvarchar(260), @mdpath nvarchar(260), @initial_file_name nvarchar(260), and @initial_offset bigint. The first one is the file path and mask for the log file. The second is the file path and mask for the corresponding metadata files. The first two parameters are required for the function, while the third and fourth can optionally be null. The third parameter and fourth parameter can be used to limit the amount of information that must be read from the target files. The initial_file_name and initial_offset parameters are paired parameters, that is, if one is specified, the other must also be specified. Included in the tabular output of the function are two columns, file_name and file_offset. These can be passed into the third and fourth parameters respectively to begin reading the file target from the point at which the last scan of the target data ended.

Note   To stop the event session, run the ALTER EVENT SESSION statement again for the session, but set the state to STOP.

Counter Target

The synchronous_event_counter target keeps a count of the number of times that the events specified in the session actually fire. The following query creates a sample event session that uses the synchronous event counter target.

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='EventCounterTargetDemo')
    DROP EVENT SESSION [EventCounterTargetDemo] ON SERVER;
CREATE EVENT SESSION [EventCounterTargetDemo]
ON SERVER
ADD EVENT sqlserver.sql_statement_starting,
ADD EVENT sqlserver.sql_statement_completed
ADD TARGET package0.synchronous_event_counter
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS)
ALTER EVENT SESSION [EventCounterTargetDemo]
ON SERVER
STATE=START

This session counts the sql_statement_starting and sql_statement_completed events that occur on the server.  To view the target data for this session, query the sys.dm_xe_session_targets DMV:

SELECT name, target_name, CAST(target_data AS XML) target_data
FROM sys.dm_xe_sessions s
       INNER JOIN sys.dm_xe_session_targets t
              ON s.address = t.event_session_address
WHERE s.name = 'EventCounterTargetDemo'

The target data is similar to the following XML shell:

<CounterTarget truncated="">
  <Packages>
    <Package name="">
      <Event name="" count="" />
    </Package>
  </Packages>
</CounterTarget>

Each of the packages loaded in the Extended Events Engine are listed, but only those packages with events being tracked by the event session have event child nodes listed. An event node exists for each event in the session definition and the count attribute has the number of times that the specific event has fired since the event session started.

Note   To stop the event session, run the ALTER EVENT SESSION statement again for the session, but set the state to STOP.

Pair Matching Target

The pair matching target can be used to find events having a starting or begin execution and that do not have a corresponding completed or end execution event. This is useful for events such as Lock Acquired and Lock Released, or Transaction Begin and Transaction End. This target can be used when orphaned transactions occur inside the SQL Server Database Engine to track the occurrence back to the statement that opened the transaction. For example, the following query creates an event session that tracks unpaired database_transaction_begin and database_transaction_end events. Before starting the event session, create an open transaction in a database by running a BEGIN TRANSACTION statement.

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='PairMatchingTargetDemo')
    DROP EVENT SESSION [PairMatchingTargetDemo] ON SERVER;
CREATE EVENT SESSION [PairMatchingTargetDemo]
ON SERVER
ADD EVENT sqlserver.database_transaction_begin(
     ACTION (sqlserver.session_id)),
ADD EVENT sqlserver.database_transaction_end(
     ACTION (sqlserver.session_id))
ADD TARGET package0.pair_matching(
     SET begin_event='sqlserver.database_transaction_begin', begin_matching_actions='sqlserver.session_id', end_event='sqlserver.database_transaction_end', end_matching_actions='sqlserver.session_id')
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);
ALTER EVENT SESSION [PairMatchingTargetDemo]
ON SERVER
STATE=START;

With the session started and running, issue a commit transaction for the previously opened transaction. This creates an unmatched database_transaction_end event because the session was not running during the database_transaction_begin event. Then, query the sys.dm_xe_session_targets DMV. The XML for the pair matching target has the following definition:

<PairingTarget truncated="" orphanCount="" matchedCount="" memoryPressureDroppedCount="">
  <event />
</PairingTarget>

There should be an event child node for each orphanCount that exists, that follows the generic XML event definition. The matchedCount is the number of event pairings that have occurred, and the truncated attribute tracks the number of truncated events since the session was started.

Note   To stop the event session, run the ALTER EVENT SESSION statement again for the session, but set the state to STOP.

Bucketing Target

The bucketing target can be used to group event occurrences using one of the event’s payload columns or actions to group by. The following event session groups wait_info by wait_type.

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='BucketingTargetDemo')
    DROP EVENT SESSION [BucketingTargetDemo] ON SERVER;
CREATE EVENT SESSION [BucketingTargetDemo]
ON SERVER
ADD EVENT sqlos.wait_info
ADD TARGET package0.asynchronous_bucketizer(
     SET filtering_event_name='sqlos.wait_info', source_type=0, source='wait_type')
WITH (MAX_DISPATCH_LATENCY = 2 SECONDS)
ALTER EVENT SESSION [BucketingTargetDemo]
ON SERVER
STATE=START

With the session started and running, query the sys.dm_xe_session_targets DMV. The XML for the bucketing target has the following definition:

<BucketizerTarget truncated="" buckets="">
  <Slot count="" trunc="">
    <value></value>
  </Slot>
</BucketizerTarget>

The root node provides the total number of buckets and truncated values. For each grouping there is a slot node that has the count of occurrences of that grouping, as well as any truncated bucket bytes and the value that the grouping is for.

Note   To stop the event session, run the ALTER EVENT SESSION statement again for the session, but set the state to STOP.

ETW File Target

The ETW file target is an external file target that is not consumable internally by SQL Server. It is generally used with external ETW tracing to get a look at what is occurring externally in conjunction with what is occurring internally.

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='ETWTargetDemo')
    DROP EVENT SESSION [ETWTargetDemo] ON SERVER;
CREATE EVENT SESSION [ETWTargetDemo]
ON SERVER
ADD EVENT sqlserver.sql_statement_starting (
       WHERE sqlserver.database_id = 2),
ADD EVENT sqlserver.sql_statement_completed (
       WHERE sqlserver.database_id = 2),
ADD EVENT sqlserver.file_written (
       WHERE sqlserver.database_id = 2),
ADD EVENT sqlserver.file_write_completed (
       WHERE sqlserver.database_id = 2)
ADD TARGET package0.etw_classic_sync_target (
       SET default_etw_session_logfile_path = N'C:\sqletwtarget.etl')

With the session created in an instance of SQL Server, the next step is to begin an ETW session in Windows to collect the external ETW data for disk and file operations.

logman start "NT Kernel Logger" -p "Windows Kernel Trace" (disk,file) -ets -o C:\systemetw.etl -bs 1024 -ct system

Full usage of logman can be found by using the /? switch at the command prompt. A full reference is also included in Appendix A of this document. With the external ETW logging enabled, start the SQL Server ETW session.

ALTER EVENT SESSION [ETWTargetDemo]
ON SERVER
STATE=START

Now generate a couple of sample workloads by loading information into some tempdb tables. Then stop the event session.

ALTER EVENT SESSION [ETWTargetDemo]
ON SERVER
STATE=STOP

Then stop the system ETW collection and Extended Events external ETW session from the command prompt.

logman update "NT Kernel Logger" -fd -ets
logman stop "NT Kernel Logger" -ets
logman update XE_DEFAULT_ETW_SESSION -fd -ets
logman stop XE_DEFAULT_ETW_SESSION –ets

Then combine the two .etl files using tracerpt to generate a single .csv file.

tracerpt c:\sqletwtarget.etl c:\systemetw.etl -y -o sqletw_results.csv

Full usage of tracerpt can be retrieved using the /? switch at the command prompt. A full reference is also included in Appendix B of this document. To view the results of this trace session, open the .csv file in Microsoft Excel.

Life of an Event

Because events exist for known points in code that may be of interest, when one of these known points is reached, a Boolean evaluation of the event determines if it is enabled on any active session in the Extended Events Engine. If the event is part of an active session on the server, any configurable columns on the event are checked to determine if it is necessary for the Extended Event Engine to retrieve the data for the configurable column. Retrieving the data for these columns is a potentially expensive operation that is only performed if necessary. After the configurable column data is collected, the remaining column data in the event payload is collected and the predicates defined on the event are evaluated to a Boolean pass/fail value.

Figure 4: Life of an event.

If the event execution passes the predicate check, then the actions are executed and any output data is added to the event data, which is then served to the synchronous targets immediately so that the impact on the performance of the executing connection is minimized. After the synchronous targets process the data, it is placed in the current session memory buffer where it waits for dispatch to the asynchronous targets based on the configuration options for the event session.

The data queued in a buffer processes differently depending on the options of the session. If the session is configured with an INFINITE setting for the MAX_DISPATCH_LATENCY option, the Extended Events Engine waits for the buffer to become full before assigning a dispatcher from the dispatcher pool to serve the data to the asynchronous targets. Otherwise, the data is processed from the buffer to the asynchronous targets when the dispatch latency setting has been passed. This is important to understand during event data consumption, because the data may not be immediately available for consumption in the event if the buffer it was added to has not been filled.

Conclusion

This paper has covered Extended Events, a new feature in SQL Server 2008 for troubleshooting. Extended Events offer a new methodology and framework for capturing diagnostic information from the SQL Server Database Engine. With a robust architecture, Extended Events sessions are highly flexible, highly configurable, and have a low impact to server performance. Event sessions are managed and configured using system and dynamic management views and DDL statements. 

One of the key features of the Extended Events architecture is that a session can have any event with any action and write event data to any target available in the packages loaded on the server. Each of the targets provides specialized storage and consumption of data as events are fired. The integration with Event Tracing for Windows provides a previously impossible deep look into the inner workings of SQL Server with the operating system to properly isolate the source of hard-to-troubleshoot problems.

While not a complete replacement for existing troubleshooting tools and techniques, Extended Events in SQL Server 2008 provides an exciting new framework for database administrators and IT professionals to troubleshoot complex problems inside the Database Engine.

About the author. Jonathan Kehayias is a SQL Server MVP and lives in Tampa, FL. He specializes in SQL Server Database Administration, Maintenance, and Performance Tuning and frequently blogs about Database Engine and Performance Tuning issues, techniques, and best practices, and can frequently be found on the MSDN Forums answering questions. Jonathan has been using SQL Server 2008 since its early beta releases and wrote the Extended Events Manager application to simplify understanding, managing, and using Extended Events. He has also published articles on the SQL Server Central community website.

For more information:

Introducing SQL Server Extended Events
(http://msdn.microsoft.com/en-us/library/bb630354(SQL.100).aspx)

MSDN Webcast: SQL Server 2008 Advanced Troubleshooting with Extended Events (http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-US&EventID=1032356291&CountryCode=US)

Debugging slow response times in SQL Server 2008 (http://blogs.technet.com/sqlos/archive/2008/07/18/debugging-slow-response-times-in-sql-server-2008.aspx)

SQL Server 2008: Advanced Troubleshooting with Extended Events (http://technet.microsoft.com/en-us/magazine/dd314391.aspx)

SQL Server 2008 Extended Events Manager Application
(http://www.codeplex.com/ExtendedEventManager/)

Did this paper help you? Please give us your feedback. Tell us on a scale of 1 (poor) to 5 (excellent), how would you rate this paper and why have you given it this rating? For example:

 - Are you rating it high due to having good examples, excellent screen shots, clear writing, or another reason?

 - Are you rating it low due to poor examples, fuzzy screen shots, or unclear writing?

This feedback will help us improve the quality of white papers we release.

Send feedback.

Appendix A: Logman Options

C:\>logman /?
Microsoft r Logman.exe (5.1.2600.5512)
c Microsoft Corporation. All rights reserved.
Logman manages the "Performance Logs and Alerts" service for creating and
managing Event Trace Session logs and Performance logs.
Usage:
logman VERB <collection_name> [options]
Verbs:
  create <counter|trace>        Create a new collection.
  start                         Start an existing collection and set the
                                begin time to manual.
  stop                          Stop an existing collection and set the end
                                time to manual.
  delete                        Delete an existing collection.
  query [collection_name|providers]  Query collection properties. If no
                                collection_name is given all collections are
                                listed. The keyword 'providers' will list all
                                of the registered Event Trace providers.
  update                        Update an existing collection properties.
Parameters:
  <collection_name>             Name of the collection.
Options:
  -?                            Displays context sensitive help.
  -s <computer>                 Perform the command on specified remote
                                system.
  -config <filename>            Settings file containing command options.
  -b <M/d/yyyy h:mm:ss[AM|PM]>  Begin the collection at specified time.
  -e <M/d/yyyy h:mm:ss[AM|PM]>  End the collection at specified time.
  -m <[start] [stop]>           Change to manual start or stop rather than a
                                scheduled begin or end time.
  -[-]r                         Repeat the collection daily at the specified
                                begin and end times.
  -o <path|dsn!log>             Path of the output log file or the DSN and
                                log set name in a SQL database.
  -f <bin|bincirc|csv|tsv|sql>  Specifies the log format for the collection.
  -[-]a                         Append to an existing log file.
  -[-]v [nnnnnn|mmddhhmm]       Attach file versioning information to the end
                                of the log name.
  -[-]rc <filename>             Run the command specified each time the log
                                is closed.
  -[-]max <value>               Maximum log file size in MB or number of
                                records for SQL logs.
  -[-]cnf [[[hh:]mm:]ss]        Create a new file when the specified time has
                                elapsed or when the max size is exceeded.
  -c <path [path ...]>          Performance counters to collect.
  -cf <filename>                File listing performance counters to collect,
                                one per line.
  -si <[[hh:]mm:]ss>            Sample interval for performance counter
                                collections.
  -ln <logger_name>             Logger name for Event Trace Sessions.
  -[-]rt                        Run the Event Trace Session in real-time
                                mode.
  -p <provider [flags [level]]> A single Event Trace provider to enable.
  -pf <filename>                File listing multiple Event Trace providers
                                to enable.
  -[-]ul                        Run the Event Trace Session in user mode.
  -bs <value>                   Event Trace Session buffer size in kb.
  -ft <[[hh:]mm:]ss>            Event Trace Session flush timer.
  -nb <min max>                 Number of Event Trace Session buffers.
  -fd                           Flushes all the active buffers of an existing
                                Event Trace Session to disk.
  -[-]u [user [password]]       User to Run As. Entering a * for the password
                                produces a prompt for the password. The
                                password is not displayed when you type it at
                                the password prompt.
  -rf <[[hh:]mm:]ss>            Run the collection for specified period of
                                time.
  -y                            Answer yes to all questions without
                                prompting.
  -ets                          Send commands to Event Trace Sessions
                                directly without saving or scheduling.
  -mode <trace_mode [trace_mode ...]>   Event Trace Session logger mode.
Note:
  Where [-] is listed, an extra - negates the option.
  For example --r turns off the -r option.
Examples:
  logman create counter perf_log -c "\Processor(_Total)\% Processor Time"
  logman create trace trace_log -nb 16 256 -bs 64 -o c:\logfile
  logman start perf_log
  logman update perf_log -si 10 -f csv -v mmddhhmm
  logman update trace_log -p "Windows Kernel Trace" (disk,net)
C:\>

Appendix B: TraceRpt Options

C:\>tracerpt /?
Microsoft r TraceRpt.Exe (5.1.2600.5512)
c Microsoft Corporation. All rights reserved.
Tracerpt processes binary Event Trace Session log files or real-time streams
FROM instrumented Event Trace providers and creates a report or a text (CSV)
file describing the events generated.
Usage:
tracerpt { <filename [filename ...]> | -rt <session_name [session_name ...]>
                                } [options]
Parameters:
  <filename [filename ...]>     Event Trace log file to process.
Options:
  -?                            Displays context sensitive help.
  -o [filename]                 Text (CSV) output file. Default is
                                dumpfile.csv.
  -summary [filename]           Summary report text file (CSV) file. Default
                                is summary.txt.
  -report [filename]            Text output report file. Default is
                                workload.txt.
  -rt <session_name [session_name ...]>   Real-time Event Trace Session data
                                source.
  -config <filename>            Settings file containing command options.
  -y                            Answer yes to all questions without
                                prompting.
Examples:
  tracerpt logfile1.etl logfile2.etl -o -report
  tracerpt logfile.etl -o logdmp.csv -summary logdmp.txt -report logrpt.txt
  tracerpt -rt EVENT_SESSION_1 EVENT_SESSION_2 -o logfile.csv
C:\>