Microsoft® SQL Server® 2012 Bible

941

Chapter 40: Extended Events

40

Events and the associated Targets that consume the Event data. One signifi cant feature of

an Extended Events session is that it is defi ned in metadata and will persist a restart of SQL

Server. This is an enhancement over a SQL Trace session, which would not survive a SQL

Server reboot without administrative intervention.

To fi nd all the Sessions defi ned on the SQL Server instance, use the following query:

select *

from sys.server_event_sessions.

To fi nd all the Sessions defi ned and running on the SQL Server instance, use the following

query:

select *

from sys.dm_xe_sessions

Channels

Although not an Extended Events object, a Channel provides useful Event association. A

Channel is nothing more than a meta-data description that describes the typical audience

for an event. In SQL Server 2008 and SQL Server 2012, four Channels associate with events:

■ (^) Admin: Events typically geared toward DBAs and end users. Often signals events
with well-defi ned resolutions.
■ (^) Analytic: Events geared toward performance investigations. Typically high in vol-
ume and describe program operation.
■ (^) Debug: Events to assist product support and developers. Used to troubleshoot spe-
cifi c problems with working on a support ticket.
■ (^) Operational: Events geared toward DBAs and end users like Admin events, but
include events that Operations may be interested in as well, such as when a data-
base was detached.
Channels are useful when searching for types of events and are used heavily in the new
Extended Events Profi ler UI.

The system_health Session

The system_health session is a predefi ned Extended Events session that collects system

data to help troubleshoot issues in the Database Engine. The session collections informa-

tion associated with high-severity errors, deadlocks, nonyielding scheduler problems, and

sessions that have had to wait a long time for locks or preemptive (OS) waits. This session

starts when SQL Server starts and outputs data to a ring buffer as well as a fi le on the fi le

system. You can disable or modify this session, but it is recommended that you do not

because it can trap a lot of useful information that can be used to troubleshoot and fi x SQL

Server problems.

c40.indd 941c40.indd 941 7/31/2012 10:02:41 AM7/31/2012 10:02:41 AM

http://www.it-ebooks.info