Export (0) Print
Expand All

SQL Server Extended Events Targets

SQL Server Extended Events targets are event consumers. Targets can write to a file, aggregate event data, or start a task that is related to the event. Targets can process data synchronously or asynchronously. 

The Extended Events design ensures that targets are guaranteed to receive events once and only once per session.

Extended Events provide the following targets that you can use for an Extended Events session:

  • Event bucketing

    Use to count the number of times that a specified event occurs, based on a specified event column or action.

  • Event pairing

    Many kinds of events occur in pairs, such as lock acquires and lock releases. Use to determine when a specified paired event does not occur in a matched set.

  • Event Tracing for Windows (ETW)

    Use to correlate SQL Server events with Windows operating system or application event data.

  • Event file

    Use to write event session output from complete memory buffers to disk.

  • Synchronous event counter

    Counts all specified events that occur during an Extended Events session. Use to obtain information about workload characteristics without adding the overhead of full event collection.

  • Ring buffer

    Use to hold the event data in memory on a first-in first-out (FIFO) basis, or on a per-event FIFO basis.

The targets can be divided into file targets and in-memory targets.

  • The event file target and ETW target are considered file targets.

  • The event bucketing, event pairing, synchronous event counter and ring buffer targets are considered in-memory targets, where the data is stored in the session's buffer (the target_data column of the sys.dm_xe_session_targets dynamic management view).

Be aware that when you stop a session that uses an in-memory target, all the information that is stored in the session's buffer will be lost. If you want to access the event data after you stop the session, you should either configure the session to use a file target (recommended) or save the data before you stop the session.

You can use the following guidelines to help you decide which target to use.

Target

Recommended Usage

Event file target or ETW target

Use a file target when one or more of the following conditions are true:

  • You want to perform long term data collection.

  • You want to collect data for very high volume events.

  • You want to keep event data for historical purposes.

Ring buffer target

Use the ring buffer target when the following conditions are true:

  • You want to perform cyclical tracing where you continually collect data, but only care about the most recent events (approximately 2 megabytes (MB) of event data). Or, you want to perform short term data collection, and view the most recent events.

  • You want to view all event data that is captured in the session's buffer. In other words, you want to view the raw data, and not aggregated information.

  • You do not have to keep the event data for historical purposes.

Event bucketing target, event pairing target, or synchronous event counter target

Use one of these in-memory targets when the following conditions are true:

  • You want to troubleshoot a specific issue by using a target that can perform some form of analysis and aggregation of the event data. For example, you may want to know whether any transactions are left open (event pairing target) or you may want to see what the top queries by CPU usage are over a given time period (event bucketing target).

  • You want to perform short term data collection.

  • You do not have to keep the event data for historical purposes.

The event bucketing target groups occurrences of a specific event type based on event data. A "bucket" is a grouping of events that are counted based on a specified event column or action. You can use the event bucketing target to troubleshoot performance issues. By identifying which events are occurring most frequently, you can find "hotspots" that indicate a potential cause of a performance problem.

The following table describes the options that can be used to configure the event bucketing target.

Option

Allowed values

Description

slots

Any integer value. This value is optional.

A user-specified value indicating the maximum number of buckets to retain. When this value is reached, new events that do not belong to existing buckets are ignored.

filtering_event_name

Any event present in the Extended Events session. This value is optional.

A user-specified value that is used to identify a class of events. Only instances of the specified event are bucketed. All other events are ignored.

If you specify this value, you must use the format: package_name.event_name, for example 'sqlserver.checkpoint_end'. You can identify the package name by using the following query:

SELECT p.name, se.event_name
FROM sys.dm_xe_session_events se
JOIN sys.dm_xe_packages p
ON se_event_package_guid = p.guid
ORDER BY p.name, se.event_name

If you do not specify the filtering_event_name value, source_type must be set to 1 (the default).

source_type

The type of object that the bucket is based on. This value is optional and if not specified has a default value of 1.

Can have one of the following values:

  • 0 for an event

  • 1 for an action

source

Event column or action name.

The event column or action name that is used as the data source.

When you specify an event column for source, you must specify a column from the event that is used for the filtering_event_name value. You can identify the potential columns by using the following query:

SELECT name FROM sys.dm_xe_object_columns
WHERE object_name = '<eventname>'
AND column_type != 'readonly'

When you specify an event column for source, you do not have to include the package name in the source value.

When you specify an action name for source, you must use one of the actions that is configured for collection in the event session for which this target is being used. To find potential values for the action name, you can query the action_name column of the sys.dm_xe_sesssion_event_actions view.

If you are using an action name as the data source, you must specify the source value by using the format: package_name.action_name.

The following example illustrates at a high level how the event bucketing target collects data. In this example, you want to use the event bucketing target to count how many waits of each wait type occurred. To do this, you would specify the following options when you define the event bucketing target:

  • filtering_event_name = 'wait_info'

  • source = 'wait_type'

  • source_type = 0 (because wait_type is an event column)

In the example scenario, the following data is recorded for the wait_type source.

Filtering event name

Source column value

wait_info

file_io

wait_info

file_io

wait_info

network

wait_info

network

wait_info

sleep

The wait type values would be categorized into three buckets, with the following values and slot counts:

Value

Slot count

file_io

2

network

2

sleep

1

For more information about event bucketing target syntax, see How to: Monitor System Activity Using Extended Events.

The bucketing target only retains event data for the specified source. In some cases the event data may be too large to retain completely, in which case the data is truncated. When event data is truncated, the number of bytes is recorded and displayed as XML output.

The bucketing target serializes data to a calling program or procedure in XML format.

NoteNote

Bucketing output does not conform to any schema.

The following example illustrates bucketing target output format.

<Slots truncated = "0" buckets=[count]>
    <Slot count=[count] trunc=[truncated bytes]>
        <value>
        </value>
    </Slot>
</Slots>

The event pairing target matches two events using one or more columns of data that are present in each event. Many events come in pairs, for example, lock acquires and lock releases. After an event sequence is paired, both events are discarded. Discarding matched sets allows for easy detection of lock acquisitions that have not been released.

By using event-level filters, the pairing target can be used to only capture events that do not match pre-set criteria.

When you use the event pairing target you are allowed to choose two events that will be matched, along with a sequence of columns to perform the matching on. All the columns in this sequence must be of the same type.

The following table describes the available options for configuring event pairing.

Option

Allowed values

Description

begin_event

Any event name that is present in the current session.

The event name specifying the beginning event in a paired sequence.

end_event

Any event name that is present in the current session.

The event name specifying the ending event in a paired sequence.

begin_matching_columns

A comma-delimited, ordered list of column names.

The columns to perform matching on.

end_matching_columns

A comma-delimited, ordered list of column names.

The columns to perform matching on.

begin_matching_actions

A comma-delimited, ordered list of actions.

The actions to perform matching on.

end_matching_actions

A comma-delimited, ordered list of actions.

The actions to perform matching on.

respond_to_memory_pressure

One of the following values:

  • 0 = Do not respond.

  • 1 = Stop adding new orphans to the list when there is memory pressure.

The target response to memory events. If set to 1 and the server is low on memory, unpaired information that is being maintained is removed.

All the data associated with an event is captured and stored for future pairing. In addition, data added by actions is also collected. The collected event data is stored in memory, and as such has a finite limit. This limit is based on system capacity and activity. Rather than taking the maximum amount of memory to be used as a parameter, the amount of memory used will be based on available system resources. When these are not available, unpaired events that have been retained will be dropped. If an event has not been paired and is dropped, the matching event will appear as an unpaired event.

The pairing target serializes unpaired events to an XML format. This format does not conform to any schema. The format only contains two element types. The <unpaired> element is the root, followed by one <event> element for each unpaired event that is currently being tracked. The <event> element contains one attribute that contains the name of the unpaired event.

The following example illustrates pairing target output format.

<unpaired truncated = "0" matchedCount = "[matched count]" memoryPressureDroppedCount = " [lost count]">
    <event name  = "[event name]" package = "[package]" id= "[event ID value]" version = "[event version]">
    <data name = "[column name]"> 
    <type name = "[column type]" package = "[type package]" /> 
    <value>[column value]</value>
    <text value>[text value]</text>>
        </data>
    </event>
</unpaired>

Before you use Event Tracing for Windows (ETW) as a target, we recommend that you possess a working knowledge of ETW. ETW tracing is either used in conjunction with Extended Events or as an Extended Events event consumer. The following external links provide a starting point for obtaining background information about ETW:

Extended Events supports the Windows XP and Windows Server 2003 version of ETW. The ETW target is a singleton target, although the target can be added to many sessions. If an event is raised on many sessions, the event will only be propagated to the ETW target one time per event occurrence. The Extended Events engine is limited to a single instance per process.

Important noteImportant

In order for the ETW target to work, the SQL Server Service startup account must be a member of the Performance Log Users group.

The configuration of the events present in an ETW session is controlled by the process that hosts the Extended Events engine. The engine controls which events to fire and what conditions must be met in order for an event to fire.

After binding to an Extended Events session, which attaches the ETW target for the first time during the lifetime of a process, the ETW target opens a single ETW session on the SQL Server provider. If an ETW session already exists, the ETW target obtains a reference to the existing session. This ETW session is shared across all SQL Server instances on a given computer. This ETW session receives all the events from sessions that have the ETW target.

Because ETW needs providers to be enabled to consume events and flow them down to the ETW, all Extended Events packages are enabled on the session. When an event is fired, the ETW target sends the event to the session on which the provider for the event is enabled.

The ETW target supports synchronous publishing of events on the thread that fires the event. However, the ETW target does not support asynchronous event publishing.

The ETW target does not support control from external ETW controllers such as logman.exe. To produce ETW traces, an event session must be created with the ETW target. For more information, see CREATE EVENT SESSION (Transact-SQL).

NoteNote

Enabling the ETW target creates an ETW session that is named XE_DEFAULT_ETW_SESSION. If a session with the name XE_DEFAULT_ETW_SESSION already exists, it is used without modifying any properties of the existing session. The XE_DEFAULT_ETW_SESSION is shared between all instances of SQL Server. After you start the XE_DEFAULT_ETW_SESSION, you must stop it by using an ETW controller, such as the Logman tool. For example, you can run the following command at the command prompt: logman stop XE_DEFAULT_ETW_SESSION -ets.

The following table describes the available options for configuring the ETW target.

Option

Allowed values

Description

default_xe_session_name

Any string up to 256 characters. This value is optional.

The Extended Events session name. By default this is XESESSION0_4B7A5EC6-37EF-4d4c-81EC-13C547B9AC46.

default_etw_session_logfile_path

Any string up to 256 characters. This value is optional.

The path to the log file for the Extended Events session. By default this is %TEMP%\ XEEtw.etl.

default_etw_session_logfile_size_mb

Any unsigned integer. This value is optional.

The log file size, in megabytes (MB), for the Extended Events session. The default is 20 MB.

default_etw_session_buffer_size_kb

Any unsigned integer. This value is optional.

The in-memory buffer size, in kilobytes (KB), for the Extended Events session. The default is 128 KB.

retries

Any unsigned integer.

The number of times to retry publishing the event to the ETW subsystem before dropping the event. The default is 0.

Configuring the preceding settings is optional. The ETW target uses default values for these settings.

The ETW target is responsible for:

  • Creating the default ETW session.

  • Registering all Extended Events packages with ETW. This ensures that events are not dropped by ETW.

  • Managing the flow of events to ETW. The ETW target creates an ETW event with Extended Events data and sends it to the appropriate ETW session. If the event is larger than the buffer size, or data cannot fit in one ETW event, ETW splits the event into fragments.

  • Keeping Extended Events packages enabled at all times.

The following default file locations are used by ETW:

  • The ETW output file is in %TEMP%\XEEtw.etl.

    Important noteImportant

    The file path cannot be changed after the first session starts.

  • Managed Object Format (MOF) files are in <your install path>\Microsoft SQL Server\Shared. For more information, see Managed Object Format on MSDN.

The event file target is an asynchronous target that writes complete buffers to disk. The file target creates two types of files, log and metadata. The metadata file describes the event contents present in the target output log file. This enables correct parsing of all the events in the log file as well as parsing of the action data associated with an event.

To use this target you must specify the log file and metadata file location name. Other configuration options, such as file size and growth characteristics are optional.

The following table describes the available options for configuring the event file target.

Option

Allowed values

Description

filename

Any string up to 260 characters. This value is required.

The file location and filename.

You can use any filename extension.

max_file_size

Any 64 bit integer. This value is optional.

The maximum file size in megabytes (MB). If max_file_size is not specified, the file will grow until the disk is full. The default file size is 1GB.

max_file_size must be larger than the current size of the session buffers. If it is not, the file target will fail to initialize, reporting that the max_file_size is invalid. To view the current size of the buffers, query the buffer_size column in the sys.dm_xe_sessions dynamic management view.

If the default file size is smaller than the session buffer size, we recommend setting max_file_size to the value specified in the max_memory column in the sys.server_event_sessions catalog view.

When max_file_size is set to a size larger than the size of the session buffers, it may be rounded down to the nearest multiple of the session buffer size. This may create a target file that is smaller than the specified value of max_file_size. For example, if the buffer size is 100MB and max_file_size is set to 150MB, the resultant file size is rounded down to 100MB because a second buffer would not fit in the remaining 50MB of space.

If the default file size is smaller than the session buffer size, we recommend setting max_file_size to the value in the max_memory column in the sys.server_event_sessions catalog view.

max_rollover_files

Any 32 bit integer. This value is optional.

The maximum number of files to retain in the file system.

increment

Any 32 bit integer. This value is optional.

The incremental growth, in megabytes (MB), for the file. If unspecified, the default value for increment is twice the session buffer size.

metadatafile

Any string up to 260 characters. This value is required.

The location and name of the metadata for the file.

The first time that a file target is created, the filename you specify is appended with _0_ and a long integer value. The integer value is calculated as the number of milliseconds between January 1, 1600 and the date and time the file is created. Subsequent rollover files also use this format. From examining the value of the long integer, you can determine the most current file. The following example illustrates how files are named in a scenario where you specify the filename option as C:\OutputFiles\MyOutput.xel:

  • first file created - C:\OutputFiles\MyOutput_0_128500310259380000.xel

  • first rollover file - C:\OutputFiles\MyOutput_0_128505831770890000.xel

  • second rollover file - C:\OutputFiles\MyOutput_0_132410772966237000.xel

The synchronous event counter target counts all events that fire during an Extended Events session. Using the synchronous event counter target you can obtain information about workload characteristics without adding the overhead of full event collection. This target does not have any customizable parameters.

The following example illustrates synchronous event counter target output.

<CounterTarget truncated = "0">
  <Packages>
    <Package name = "[package name]">
      <Event name = "[event name]" count = "[number]" />
    </Package>
  </Packages>
</CounterTarget>

The ring buffer target briefly holds event data in memory. This target can manage events in one of two modes.

  • The first mode is strict first-in first-out (FIFO), where the oldest event is discarded when all the memory allocated to the target is used. In this mode (the default), the occurrence_number option is set to 0.

  • The second mode is per-event FIFO, where a specified number of events of each type is kept. In this mode the oldest events of each type are discarded when all the memory allocated to the target is used. You can configure the occurrence_number option to specify the number of events of each type to keep.

The following table describes the available options for configuring the ring buffer target.

Option

Allowed values

Description

default_memory

Any 32 bit integer. This value is optional.

The maximum amount of memory, in kilobytes (KB), to use. Existing events are dropped when this value is reached.

occurrence_number

One of the following values:

  • 0 (the default) = Oldest event is discarded when all the memory allocated to the target is used.

  • Any 32 bit integer = The number of events of each type to keep before being discarded on a per-event FIFO basis.

This value is optional.

The FIFO mode to use, and, if set to a value greater than 0, the preferred number of events of each type to keep in the buffer.

The following example illustrates ring buffer target output.

<RingBufferTarget eventsPerSec="" processingTime="" totalEventsProcessed="" eventCount="" droppedCount="" memoryUsed="">
 <event name="" package="" id="" version="" timestamp="">
    <data name="">
      <type name="" package="" />
      <value></value>
      <text></text>
    </data>
    <action name="" package="">
      <type name="" package="" />
      <value></value>
      <text></text>
    </action>
  </event>
</RingBufferTarget>

Updated content

Added note about the XE_DEFAULT_ETW_SESSION session to the "Event Tracing for Windows" section.

In the introduction, added short descriptions for each target type, and added recommended usage guidelines.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft