By: Arshad Ali | Updated: 2010-10-28 | Comments (3) | Related: > 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
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
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
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
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
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
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
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
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:
- 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.
- 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
- Review SQL Server Extended Events Dynamic Management Views
- Review Event Notifications in SQL Server for Tracking Changes
- Review Identify Deprecated SQL Server Code with Extended Events
- Review SQL Server Tempdb Usage and Bottlenecks Tracked with Extended Events
- Review my previous tips
About the author
Arshad Ali is a SQL and BI Developer focusing on Data Warehousing projects for Microsoft.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2010-10-28
