Extended Events - Pro SQL Server Internals - page 539

Chapter 28
Extended Events
Extended Events is a highly scalable performance monitoring and troubleshooting solution introduced in
SQL Server 2008. This chapter provides an overview of the Extended Events framework and shows you how to work
with them.
Extended Events is targeted as a replacement for SQL Traces, which are deprecated in SQL Server 2012. Extended
Events are lightweight, and they have the flexibility to allow for troubleshooting of some scenarios, which were not
possible with SQL Traces. For example, capturing the first several occurrences of a specific event, obtaining session-level
wait statistics, or detecting objects with a large number of page splits.
Extended Events Overview
Even though SQL Traces are extremely easy to set up, they have serious limitations. All event types generate output
in the same format. The same columns in the output could provide different data for different SQL Trace events. For
example, in the SQL:Batch Completed event, the TextData column contains the text of the SQL batch. Alternatively,
in the Lock:Acquired event, the same column shows a resource where lock was acquired. It is complicated to analyze
the output of the traces that collect different events.
Performance is another important factor. A SQL Server component, called Trace Controller , manages SQL Traces
defined by all Trace Consumers . It keeps an internal bitmap, which shows event types that are consumed by currently
active traces and, therefore, need to be collected. Other SQL Server components, which in this context are called
Trace Producers , analyze that bitmap and fire corresponding events when needed.
Trace producers do not know what data columns are included in the trace. Data for all of the columns is collected
and passed to the controller, which evaluates trace filters and discards unneeded events and data columns.
As you can guess, this architecture introduces unnecessary overhead. Consider the situation where you want to
capture long-running SQL statements from a specific session. SQL Trace would have very few columns defined and
collect just a handful of events. Trace producers, however, would fire events for each SQL statement that comes to the
system. The trace controller would do all further filtering and column removal.
The Extended Events framework has been designed with the goal of addressing those limitations in mind. Similar
to SQL Traces, it includes Event Sessions that define the boundary for event collection. They specify event types and
data that needs to be collected, predicates that are used in filters, and targets where the data is stored. SQL Server can
write events to targets either synchronously, in the same thread where the event occurs, or asynchronously, buffering
data in the memory reserved for each event session.
Extended Events uses an XML format. Each event type has its own set of data columns. For example, the
sql_statement_completed event provides the number of reads and writes, CPU time, duration, and other execution
statistics of a query. You can collect additional attributes, for example the tsql stack, by executing operators called
actions. In contrast to a SQL Trace, Extended Events does not collect unnecessary data; that is, only a small set of
event data columns and specified actions are collected.