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




Related Articles :

  • No Related Articles Found

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 |