Maintenance and Monitoring - Microsoft SQL Server 2008 R2 Administration - page 310

Monitoring resource usage with Extended
Events
The performance monitoring process requires an event handling system in addition to existing
tools such as PERFMON, SQL Profiler, and DMVs. The design and testing of an application
system will always have unanticipated problems that require extensive troubleshooting
techniques. To avoid any sort of interruption to the performance monitoring, an event-handling
system for the server is essential. SQL Server 2008 addresses this situation by providing
Extended Events (XE). The XE infrastructure is a lightweight mechanism that supports
capturing, filtering, and action on events generated by the server process.
The process of events and data are managed by the internal objects and it is ideal to mention
the Extended Events concepts.
F SQL Server Extended Events packages works as a container for XE objects such
as package. The three kinds of XE packages are: package 0 (XE system objects),
sqlserver , and sqlos .
F Every XE package contains objects such as: actions, events, predicates, maps,
targets, and types.
Actions are the series of responses to an event that may have a unique set
of actions
Events are monitoring points of interest in the execution path of a program
that can be used solely for tracing purposes or triggering actions
Predicates works as a set of logical rules that will be used to evaluate events
when they are processed
A Map is an internal table that maps internal value to a string
Targets are consumers that will process synchronous events on the thread
that fires the event
Types will hold the data for collection of bytes, length, and characteristics of
the collection to interpret the data.
In this recipe, we will go through the steps that are included in monitoring the system resource
usage using an Extended Events framework that can be utilized to help SQL Server monitoring
in many ways. The process will help us to isolate excessive CPU utilization, monitor locking/
deadlock, and identify long-running SQL queries.
Getting ready
Ensure that the following prerequisites are met before proceeding further:
F The underlying SQL Server version must be 2008 or higher