Extended Events - Pro SQL Server Internals - page 553

You can use multiple event targets in one event session. For example, you can combine the event_file target
together with the ring_buffer using the latter for real-time troubleshooting while retaining events in the file.
As you have already seen, targets can be either synchronous or asynchronous. SQL Server writes data to
synchronous targets in the execution thread that fires an event. For asynchronous targets, SQL Server buffers events
in the memory, periodically flushing them out to the targets. The EVENT_RETENTION_MODE event session configuration
setting controls what happens with new events when buffers are full:
The NO_EVENT_LOSS option indicates that all events must be retained and event loss is
unacceptable. SQL Server execution threads wait until buffers are flushed and have the free
space to accommodate the new events. As you can guess, this option can introduce a major
performance impact on SQL Server. Think about an event session that collects information
about acquired and released locks using the event_file target as an example. That event
session can collect an enormous amount of events, and I/O throughput quickly becomes a
bottleneck when the event data is saved.
The ALLOW_SINGLE_EVENT_LOSS option allows a session to lose a single event when
the buffers are full. This option reduces the performance impact on SQL Server while
minimizing the loss of event data collected.
The ALLOW_MULTIPLE_EVENT_LOSS option allows a session to lose multiple events when the
buffers are full. This option minimizes the performance impact on SQL Server at the cost of
a potential loss of a large number of events.
Creating Events Sessions
Now it is time to bring everything together and look at Extended Events sessions. We will focus on a T-SQL
implementation; however, you can use Management Studio with SQL Server 2012-2014 or Jonathan Kehayias' SSMS
Add-In with SQL Server 2008/2008R2 if you prefer to work through the UI.
Each Extended Events session specifies the events to collect, targets for collected data, and several configuration
properties. Listing 28-11 shows a statement that creates an Extended Events session that collects information about
tempdb spills using the hash_warning and sort_warning events. This code works in SQL Server 2012 and above
because SQL Server 2008/2008R2 does not support hash_warning or sort_warning events. However, the syntax of the
CREATE EVENT SESSION command is the same in every version of SQL Server.
Listing 28-11. Creating an Event Session
create event session [TempDB Spills]
on server
add event
sqlserver.hash_warning
(
action
(
sqlserver.session_id
,sqlserver.plan_handle
,sqlserver.sql_text
)
where(sqlserver.is_system=0)
),
add event
sqlserver.sort_warning