Using SQL Server 2008 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.

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

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.

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

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.

Channel

Audience of Interest

Admin

Admin events are primarily geared
towards database administrators and end-users. Events in this channel signal
an error that generally already has a well-defined solution for it like
deadlocking, or broker activation termination.

Analytic

Analytic events are geared
towards performance investigations, and exist almost every place a
performance counter is updated in SQL Server. They are published in high
volume and describe program operation. Analytic events closely parallel
existing SQL Trace events like RPC starting/completed, SP statement
starting/completed, and SQL statement starting/completed.

Debug

Debug events are geared towards
product support and developers. They are used to troubleshoot a specific
problem and may be requested by Microsoft CSS when working on an open
incident.

Operational

Operational events are also
geared towards administrators and end-users, but they may also be of interest
to an operations engineer or similar role. Events in this channel signal a
change like database started/stopped, or attached/detached. They can be used
to trigger additional tasks based on the problem being reported.

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.

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.

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.

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:

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.

Target
Type

Sync

Async

Target
Description

Event Bucketing

X

X

Creates buckets based on the user-specified columns,
and only collects and retains data for the specified source. If the event
data is too large for retention, it is truncated, and the XML output is
updated to reflect the number of bytes truncated. The output of bucketing is XML
that does not conform to any schema.

Event File

X

Creates two types of files, log and metadata for
writing complete buffers to disk. The metadata file describes the output log
file and allows the events and associated actions to be parsed correctly. The
file names and locations must be provided to use this target.

Event Pairing

X

Matches two events based on one or more columns present
in the events. Matched pairs are then discarded leaving only unmatched events
for analysis. To use this target, you must provide the events to be matched
and the columns to be used for the matching. All data from the event and any
associated actions are collected and retained in memory until matched or
dropped due to memory pressure. The output of pairing is XML that does not
conform to any schema.

Event Tracing for Windows (ETW)

X

Outputs event data to the SQL Server ETW provider. Only
one ETW session to the SQL Server provider can be open at a time. For this
reason, the ETW target obtains a reference to this one session for all ETW
targets regardless of their Extended Events session scope.

Ring Buffer

X

Temporarily holds event data in memory, in either a
first-in first-out mode where events are discarded in the order received, or
on a per-event mode where a set number of each event type is kept and then
discarded. The output is XML and does not conform to any schema.

Synchronous Event Counter

X

Counts all events that fire during the Extended Events session.
It allows collection of workload without doing a full event collection. The
output is XML and does not conform to any schema.

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:

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:

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:

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

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

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:

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

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:

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.

Option

Default value

Description

EVENT_RETENTION_MODE

ALLOW_SINGLE_EVENT_LOSS

Specifies the event retention
mode to use for handling event loss. There are three possible values:

ALLOW_SINGLE_EVENT_LOSS

A single event can be dropped
and lost from the session when all the event buffers are full. This allows
for acceptable SQL Server performance, while minimizing the loss of data.

ALLOW_MULTIPLE_EVENT_LOSS

Allows entire full event
buffers containing multiple events to be dropped and lost from the session.
This minimizes the performance impact on the server, but at the cost of
losing many events. The number of events lost depends on the size of the
events, the MAX_MEMORY setting for the session and the MEMORY_PARTITION_MODE.

NO_EVENT_LOSS

No event loss is allowed. This
guarantees that all events are captured, but at the expense of performance. Any
task that fires an event waits until space is available in a memory buffer.

MAX_DISPATCH_LATENCY

30 seconds

Specifies how long events are
held in the buffers before being dispatched to the session targets. The time
is in seconds, with a minimum value of one second. A value of zero or
INFINITE flushes the buffers only when they are full.

MAX_EVENT_SIZE

0KB

Sets the maximum size in memory
for events. This option should be used to allow single events that are larger
than the MAX_MEMORY setting. Allowable sizes are in either kilobytes (KB) or
megabytes (MB). If a size in KB is specified, then the minimum value is 64KB.

MAX_MEMORY

4MB or 4096KB

Sets the maximum amount of
memory in kilobytes (KB) or megabytes (MB) that the session allocates for
event buffering.

MEMORY_PARTITION_MODE

NONE

Specifies how buffers are
created and partitioned. There are three valid values for this option:

NONE

A single set of buffers is
created within the SQL Server instance.

PER_NODE

A set of buffers is created for
each NUMA node.

PER_CPU

A set of buffers is created for
each CPU.

This option can be used on
larger servers to partition the buffers for better performance.

STARTUP_STATE

OFF

Specifies whether or not the
event session starts automatically when the instance of SQL Server starts up.
Valid values are ON and OFF

TRACK_CAUSALITY

OFF

Allows related events on
different SPIDs to be correlated together. Valid values
are ON and OFF.

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.

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.

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:

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.

Contains a row for each active event session in the
system. There is also a row for the server audits if they are enabled which
run on top of but are not a part of Extended Events.

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

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:

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.

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:

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:

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:

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

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.

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.

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.

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.

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.

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.

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.

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:

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.

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.

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.

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.

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.

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.

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.

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.