Light Weight Monitoring using Extended Events

Introduction

SQL Server 2008 introduces Extended Events for performance
monitoring.

SQL Server Extended Events is a general event-handling
system for server systems.

So  why another event handling system? We already have
activity monitor, Perfmon, SQL Profiler, DMVs,. However, Extended Events have
several major advantages:

1. Light Weight. This is the primary advantage of  Extended Events. Unlike
the profiler,  Extended Events won’t eat your resources. Microsoft predicts that
20,000 events/sec on a 2 GHZ Pentium with 1 GB RAM takes uses than 2% of the
CPU.

2. Most of the counters available are cumulative, thus it is difficult to
find exact events.

3. Extended Events have a  highly scalable and highly configurable architecture
that allows DBAs  and developers to collect as much or as little information as
is necessary to troubleshoot or identify a problem.

If you wish to use this feature, make sure that you have SQL
Server 2008 SP1 installed as there were few bugs in the SQL Server 2008 RTM.

Terminology

As this is a new feature, there are few concepts in Extended
Events which we need to understand first.
 

Package

The Package is a container for Extended Events Objects.
There are four packages.

Package Name

Description

Package0

Extended Events system objects. This is the default
package.

Sqlos

SQL Server operating system related objects

Sqlserver

SQL Server related objects

SecAudit

Used only for built-in SQL Server auditing. This is a
private package name.

Existing packages can be viewed from following query.

SELECT
* FROM sys.dm_xe_packages

Packages can contain any Events, Targets, Actions, Types, Predicates
and Maps which will be discussed later.

Objects from different packages can be mixed when creating
Extended Events.

The following diagram shows what a package can contain.

The relationship of a module, packages, and object

Source: MSDN

This  Module can be either an executable or a DLL.

Events

Events are the trace events that we are going to monitor.  You
can view the events from following query.

SELECT
* FROM sys.dm_xe_objects WHERE
object_type = ‘event’

For each event there is a payload or event columns
associated with it.

For example, to view the columns for the cpu_config_changed
event, run following query.

SELECT * FROM  sys.dm_xe_object_columns

WHERE object_name = ‘cpu_config_changed’

Most of the time you will see read only columns and data
columns. In the above example, the first five are readonly columns while last
two are data columns.

These events are categorized by channel and keyword.

Actions

Actions   describe   the event. For example, from the action
you can identify parameters such as database_id, session_id, transaction_id.

You can view existing actions from following query.

SELECT  * FROM sys.dm_xe_objects

WHERE
object_type = ‘action’

color:red’>

Targets

Targets are   where the events data will reside. There are
six targets for Extend Events.

Target

Description

Type

Event bucketing

Number of times selected event occurred.

Synchronous and asynchronous

Event pairing

Shows incomplete events. There are some events which are   pairs
(lock acquired and lock released)

Synchronous

Event Tracing for Windows (ETW)

Use to correlate SQL Server events with Windows operating
system or application event data.

Synchronous

Event file

A binary file that all events are written to. There are options
to set the size of the file, the maximum of rolled over files and the file
increment size in MB.

Asynchronous

Synchronous event counter

Counts the  events that have occurred.

Synchronous

Ring buffer

Used to hold the event data in memory on a first-in first-out
(FIFO) basis, or on a per-event FIFO basis

Asynchronous

For ETW to work the SQL Server service account has to be in
the “Performance Log Users” group. This is a binary file that has to
be formatted with tracerpt.exe so that it can be readable.

When the event file is created, it can be read by using the
sys.fn_xe_file_target_read_file built in function.

In the Ring Buffer target, FIFO means that older events are
removed when new ones arrive and the buffer is full. The per-event FIFO holds a
specific number of each event type in the queue and removes them after the
buffer is full.

You can view the available targets by executing the following
query.

SELECT * FROM sys.dm_xe_objects

WHERE object_type = ‘target’

The above query will return six additional targets which
were stated above. Those are security audit targets. These are private and can
only be used by SQL Server for built in auditing functionality.

Predicts

Though events will fire, we
need to filter them. By filtering we can reduce the no of events to analyze. 
These filters are called Predicts. It is recommended to use  predicts as simple
as possible for  performance considerations. You can view the existing predicts
by running following query.

SELECT * FROM sys.dm_xe_objects

WHERE object_type in (‘pred_compare’, ‘pred_source’)

Types

A type is a simple or complex
data type that is used in the event payload.

SELECT * FROM sys.dm_xe_objects

WHERE object_type in (type)

Maps

Maps are internal values to a
string, which enables the  user to find out what the value represents. Instead
of only being able to obtain a numeric value, a user can get a meaningful
description of the internal value. The following query shows how to obtain map
values.

SELECT * FROM sys.dm_xe_objects

WHERE object_type in (‘map’)

If you need map values for
lock, you can get them from the following query.

SELECT map_key, map_value FROM sys.dm_xe_map_values

WHERE name = ‘lock_mode’

Continues…

Pages: 1 2 3




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |