SQL Server Extended Events Targets

Event Bucketing Target

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.

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

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

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.

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

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.

The following example illustrates bucketing target output format.

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

Event Pairing Target

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.

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>

Event Tracing for Windows Target

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:

• Windows Events

• Improve Debugging And Performance Tuning With 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.

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).

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

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

  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.

Event File Target

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.

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

Synchronous Event Counter Target

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>

Ring Buffer Target

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.

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>

See Also

Concepts