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.
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’
