Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

An Overview of Extended Events in SQL Server 2008


By:   |   Last Updated: 2010-10-28   |   Comments (3)   |   Related Tips: More > Extended Events

Problem

As a DBA, we are faced with performance problems which we need to diagnose, trace and troubleshoot. Although there are several different tracing and troubleshooting mechanisms (DBCC, SQL Trace, Profiler, etc...) none of these tools provide deep levels of tracing/troubleshooting, like identifying page splits, high CPU utilization, etc... So how can we diagnose, trace and troubleshoot these kinds of performance problems in SQL Server 2008. In this tip we look at Extended Events for SQL Server 2008 and how they are different from earlier tracing and troubleshooting methods.

Solution

Extended Events (also called XEvent) in SQL Server 2008 and later versions, provide a generic tracing and troubleshooting framework which allows deeper and more granular level control of tracing which was not possible using earlier methods like DBCC, SQL Trace, Profiler, etc... Extended Events also have more events that can be captured, compared to SQL Trace, which gives the DBA more power to trace and troubleshoot performacne issues. For example, now you can identify page splits, high CPU utilization by ad-hoc queries as well as other things.

For further troubleshooting, you can use Extended Events to correlate SQL Server captured event data with that from the operating system (or any other ETW enabled applications) and for that reason Extended Events allows you to direct output to ETW (Event Tracing for Windows).

The choice of Extended Events becomes more evident because of the flexibility it provides. For example, you can bind any event to any target as well as you can specify any action with any event. You can also use predicates (filter criteria) to dynamically filter the events. Even though the events are fired synchronously in a host application they can be processed either synchronously or asynchronously depending on your need.

So let's take a look at what makes up Extended Events in SQL Server.


Package

For SQL 2008 and SQL 2008 R2, the SQL Server module (a module is an executable or a dynamic link library) contains 4 different packages. A package is a top level container which contains all the extended events objects; like Events, Actions, Targets, Predicates, etc...

The 4 packages are:

  • package0 - is the default package and contains system level extended events' objects
  • sqlserver - package contains objects which are related to SQL Server
  • sqlos - contains objects which are related to SQL Server Operating System (SQLOS) of which can be used in user defined event session
  • SecAudit - is for internal use by SQL Server auditing feature

You can query sys.dm_xe_packages DMV to get information about packages as shown below.

Script #1 - Extended Event Packages

SELECT pkg.name, pkg.description, mod.*
FROM
sys.dm_os_loaded_modules mod
INNER
JOIN sys.dm_xe_packages pkg
ON
mod.base_address = pkg.module_address

Events

An event refers to an occurrence in an execution path (or point) of the code which you would like to trace. For example, the sql_statement_completed event is raised when the execution of sql command is complete, the error_reported event is raised when SQL Server reports an exception during execution. All these events are fired synchronously in the host application, however they can be processed either synchronously or asynchronously by the targets as per your need.

SQL Server 2008 has 254 events whereas SQL Server 2008 R2 had 259. To list all of the available events, run this query:

Script #2 - Package events

select pkg.name as PackageName, obj.name as EventName
from
sys.dm_xe_packages pkg
inner
join sys.dm_xe_objects obj on pkg.guid = obj.package_guid
where
obj.object_type = 'event'
order
by 1, 2

Each event has a set of associated columns or payload. To get more information about these columns refer to sys.dm_xe_object_columns DMV:

Script #3 - Events Columns

select * from sys.dm_xe_object_columns
where
object_name = 'sql_statement_completed'

select
* from sys.dm_xe_object_columns
where
object_name = 'error_reported'

As Extended Events are aligned with ETW (Event Tracing for Windows), these are also categorized on the basis of two properties called channel and keyword.

These are the available channels:

  • Admin - Events in this channel are mostly used by the DBA and end-users. These events are well defined or well documented and tells the end-user exactly what to do to resolve the problem.
  • Analytic - Events in this channel are published in high volume and mostly used by DBA or Sr. DBA or database developers to identify performance related issues. Examples of some of the events in this channel include: checkpoint_begin, checkpoint_end, databases_log_cache_hit, page_split, databases_log_cache_hit
  • Operational - Events in this channel, apart from being used by DBA and end users, are also used by support/operation engineers. These events can be used to trigger additional tasks based on reported problems. Examples of some of these events in this channel include: database_started, database_stopped, long_io_detected, trace_flag_changed, page_compression_attempt_failed
  • Debug - Events in this channel are used by developer to debug/diagnose problems. Examples of some of the events in this channel include: ghost_cleanup, deadlock_monitor_mem_stats, deadlock_monitor_perf_stats, deadlock_monitor_pmo_status, log_buffer_allocated, log_buffer_freed

A keyword is a grouping of events specific to an application. To get a list of all keywords, use the query below:

Script #4 - Events Categorization Keyword

SELECT map_value [Event Keywords]
FROM
sys.dm_xe_map_values
WHERE
name = 'keyword_map'

Actions

When an event is raised we can take programmatic responses to that event and those responses are called actions. When we create an event session, we bind actions to take on the firing of that event. An event bounded action is invoked synchronously on the thread that fired the event (after all predicates/filters have been evaluated and before the event is sent to its target for processing) and obviously in some cases it will have some negative impact on performance. We use actions to append additional data to event data, aggregate event data, calculate run time statistics, collect user input on exception, etc...

Script #5 - Package wise actions

select pkg.name as PackageName, obj.name as ActionName
from
sys.dm_xe_packages pkg
inner
join sys.dm_xe_objects obj on pkg.guid = obj.package_guid
where
obj.object_type = 'action'
order
by 1, 2

Targets

The consumers of the events are called Targets. You can define one or more targets for your event session. Targets are capable of processing event data both synchronously on the same thread that fired the event or asynchronously on a background system provided thread. It's recommended to use an asynchronous target wherever possible to avoid impacting performance. If the event session has multiple targets, the synchronous targets process the event data first and then it is queued in event session buffers to be processed by asynchronous targets asynchronously. For example Event File target is an asynchronous target that writes complete buffers to disk in the files (log file and metadata file; metadata file contains associated meta information). Another frequently used target is Ring Buffer which holds the event data in memory either on a FIFO (First-In First-Out) basis or a per event FIFO basis. To learn more about these targets click here.

Script #6 - Package wise targets

select pkg.name as PackageName, obj.name as TargetName
from
sys.dm_xe_packages pkg
inner
join sys.dm_xe_objects obj on pkg.guid = obj.package_guid
where obj.object_type = 'target'

order
by 1, 2

Predicates

We can dynamically filter out events during processing by using logical rules or boolean expressions; these rules are called Predicates. Predicates can store data in a local context that can be used for creating predicates that return true once every n minutes or every n times that an event fires.

Script #7 - Package wise predicates

select pkg.name as PackageName, obj.name as PredicateName
from
sys.dm_xe_packages pkg
inner
join sys.dm_xe_objects obj on pkg.guid = obj.package_guid
where obj.object_type = 'pred_source'
order
by 1, 2

Types and Maps

Event data is a collection of bytes strung together, to interpret this event data a Type provides the length and characteristic of the byte collection for segregation.

Maps are a kind of mapping between internal numeric values and a more descriptive meaning of the value. You can query sys.dm_xe_map_values DMV for list of this mapping.


Session

A session is way of grouping events, their associated actions and predicates for filtering and different targets to process event firing. An event/action/target can be used in more than one session simultaneously with different levels of data collection and predicates without impacting each other.

With a session you can also specify buffering and dispatch policies, the buffering policy dictates how much memory to use for event data and how data loss will be handled when available memory is already consumed whereas the dispatch policy dictates the amount of time events will be in buffers before it's handed over for processing to targets.

If you query sys.dm_xe_sessions DMV you will notice session_health event session is already created and running. This event session is a default session which collects SQL Server health information for the ring buffer target. The number of records in this DMV increases as you start creating sessions. The below query will give you list of all the events, actions and target for the specified event session:

Script #8 - Event session with its events, actions and targets

SELECT sessions.name AS SessionName, sevents.package as PackageName,
sevents
.name AS EventName,
sevents
.predicate, sactions.name AS ActionName, stargets.name AS TargetName
FROM
sys.server_event_sessions sessions
INNER
JOIN sys.server_event_session_events sevents
ON
sessions.event_session_id = sevents.event_session_id
INNER
JOIN sys.server_event_session_actions sactions
ON
sessions.event_session_id = sactions.event_session_id
INNER
JOIN sys.server_event_session_targets stargets
ON
sessions.event_session_id = stargets.event_session_id
WHERE
sessions.name = ''
GO

Please note these two things:

  1. When an extended event is created it does not get started on its own you need to use the ALTER EVENT SESSION command to start or stop the event session as per your need.
  2. To create, alter or drop an event session you need to have the CONTROL SERVER permission.

In this tip I gave you an overview of the new tracing mechanism in SQL Server 2008 called Extended Events (XEvents). In my next tip, I will provide real examples to understand how this powerful tracing feature can be used to troubleshoot performance problems.

Next Steps


Last Updated: 2010-10-28


next webcast button


next tip button



About the author
MSSQLTips author Arshad Ali Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, September 10, 2014 - 5:32:49 AM - Divine Flame Back To Top

Nice article. keep it up.


Tuesday, April 08, 2014 - 11:05:48 PM - Ernest Wong Back To Top

For Script #8, seems duplicated record is resulted.

How about this? I've joined the sevents and sactions on the event_id also and it seems more reasonable.

SELECT sessions.name AS SessionName, sevents.package as PackageName, 

sevents.name AS EventName, 

sevents.predicate, sactions.name AS ActionName, stargets.name AS TargetName 

FROM sys.server_event_sessions sessions 

INNER JOIN sys.server_event_session_events sevents 

ON sessions.event_session_id = sevents.event_session_id 

INNER JOIN sys.server_event_session_actions sactions 

ON sessions.event_session_id = sactions.event_session_id and sevents.event_id = sactions.event_id

INNER JOIN sys.server_event_session_targets stargets 

ON sessions.event_session_id = stargets.event_session_id 

WHERE sessions.name = '' 

 

Thursday, October 28, 2010 - 10:09:19 AM - Vik Back To Top

This is a nice article. There is also a GUI from codeplex community to create extended events which has helped me a lot:

                           http://extendedeventmanager.codeplex.com/

Hope this helps.

-Vikram


Learn more about SQL Server tools