Extended Events - Pro SQL Server Internals - page 566

The Enterprise Edition of SQL Server 2012-2014 introduces another default Extended Events session called
AlwaysOn_health . As you can guess by the name, this session collects information about AlwaysOn Availability
Groups-related events, such as errors and failovers. This session is enabled only when SQL Server participates in an
AlwaysOn Availability Group.

■ You can examine events collected by system_health and AlwaysOn_health sessions by scripting them in
sQL server Management studio. You can even modify session definitions if needed. Be careful, however, because those
changes can be overwritten during sQL server upgrades or service packs installations.

Tip

Detecting Expensive Queries
You can detect expensive queries in the system by capturing sql_statement_completed and rpc_completed events
with execution metrics that exceed some thresholds. This approach allows you to capture queries that do not have
an execution plan cached, which are not exposed by the sys.dm_exec_query_stats view. However, you will need to
perform additional work aggregating and analyzing the collected data afterwards when choosing what queries need to
be optimized.
Listing 28-25 shows an event session that captures queries that use more than five seconds of CPU time or
that issued more than 10,000 logical reads or writes. Obviously, you need to fine-tune filters based on your system
workload, avoiding collection of excessive amounts of data.

Listing 28-25. Capturing expensive queries

create event session [Expensive Queries]
on server
add event
sqlserver.sql_statement_completed
(
action (sqlserver.plan_handle)
where
(
(
cpu_time >=5000000 or -- Time in microseconds
logical_reads >=10000 or
writes >=10000
) and
sqlserver.is_system = 0
)
),
add event
sqlserver.rpc_completed
(
where
(
(
cpu_time >=5000000 or
logical_reads >=10000 or
writes >=10000
) and