Chapter 49. XEVENT: the next event infrastructure
SQL Server gives many options for monitoring and troubleshooting, such as SQL Server Profiler and SQL Trace, Dynamic Management Views and Functions, the default trace, trace flags, performance counters, deadlock graphs, ring buffers, blocked process reports, DBCC commands, various logs, and others. So you may wonder why anyone would need a new event infrastructure such as Extended Events. It may sound shocking, but the diversity of the tools is the main reason. Having so many techniques available makes it difficult to maintain the code for each new release of SQL Server. That’s from a developer’s point of view. For the rest of us—customers, consultants, users, and even for Microsoft support—it’s challenging to obtain relevant performance or troubleshooting information. Why? Because the information needed usually comes from multiple sources. Furthermore, each of the tools available before SQL Server 2008 has its own strengths and weaknesses.
Think about monitoring SQL Server using SQL Server Profiler: it’s graphical, easy to use, and can be used in correlation with PerfMon logs, but the performance overhead can be pretty serious.
Extended Events is the potential event infrastructure for the next releases of SQL Server. This is my personal opinion, and the role of this chapter is to support this opinion with facts.
Extended Events infrastructure characteristics
The following are some of the characteristics of Extended Events that I consider to be important:
- XEVENT is a general event-handling infrastructure for servers.
- It allows defining events to be monitored and provides a variety of ways to consume the events.
- It can be synchronous or asynchronous (events are fired synchronously).
- It can trigger actions when an event fires.
- It uses predicates to filter events from consumers.
- It has causality tracking (through inherited IDs).
- It has high performance and good scalability.
- The cost of firing a single event is extremely small (approximately 2 μs CPU time on a 2 GHz processor). But keep in mind that when consuming the events, it’s a different story—depending on how the event is consumed, the overhead can become significant.
- ETW (Event Tracing for Windows) is enabled (integration of Extended Events and ETW is supported).
- It’s controlled through T-SQL DDL statements (such as CREATE and ALTER).
- It exposes rich metadata through a complete set of catalog views, Dynamic Management Views, and functions.
Listing 1 uses Extended Events to monitor long-running queries. We’ll use the example to understand some of the concepts introduced by Extended Events.
Listing 1. Using Extended Events to monitor long-running queries
USE master;
GO
-- Create Event Session
CREATE EVENT SESSION GetLongRunningQueries ON SERVER
ADD EVENT sqlserver.sql_statement_completed(
ACTION (sqlserver.sql_text)
WHERE sqlserver.database_id=1 AND duration > 20000);
GO
-- Add Event Bucketing target
-- source_type = {0|1}, 0 = event 1 = action
ALTER EVENT SESSION GetLongRunningQueries ON SERVER
ADD TARGET package0.synchronous_bucketizer (set filtering_event_name='sqlserver.sql_statement_completed', source_type=1, source='sqlserver.sql_text'),
GO
-- Start the Event Session
ALTER EVENT SESSION GetLongRunningQueries
ON SERVER STATE=START;
GO
-- For testing purpose we will create a stored procedure that will run
-- longer than 20 seconds
CREATE PROCEDURE usp_WaitForATime AS
BEGIN
DECLARE @D DATETIME=GETDATE(),@S VARCHAR(128);
WHILE (DATEDIFF(ss,@D,GETDATE())<22)
SET @S = @@VERSION;
END;
GO
-- Execute the procedure 5 times
-- This works in SQL Server Management Studio
EXEC usp_WaitForATime;
GO 5
-- Getting the information
DECLARE @MyXML XML;
SELECT @MyXML = CAST(T.target_data AS XML)
FROM sys.dm_xe_session_targets T
JOIN sys.dm_xe_sessions S
ON S.address = T.event_session_address
JOIN sys.server_event_sessions ES
ON S.name = ES.name
WHERE T.target_name = 'synchronous_bucketizer'
AND S.name = 'GetLongRunningQueries';
-- Display the information as raw XML
SELECT @MyXML AS XML_Representation;
-- Display the information using XQuery for a better output
SELECT
T.Slot.value('@count', 'int') [NbmOfExecutions],
T.Slot.value('value[1]', 'varchar(MAX)') [QueryText]
FROM @MyXML.nodes('/BucketizerTarget/Slot') AS T(Slot)
-- Clean-Up
DROP PROCEDURE usp_WaitForATime;
GO
ALTER EVENT SESSION GetLongRunningQueries
ON SERVER STATE=STOP;
GO
DROP EVENT SESSION GetLongRunningQueries
ON SERVER;
GO
Running the code produces the output shown in listing 2.
Listing 2. Output from running listing 1
XML_Representation
----------------------------------------------
<BucketizerTarget truncated="0" buckets="256">
<Slot count="5" trunc="1">
<value>EXEC usp_WaitForATime;</value>
</Slot>
</BucketizerTarget>
NbmOfExecutions QueryText
--------------- ----------------------
5 EXEC usp_WaitForATime;
In this chapter, I’ll use the code from listing 1 to explain the following concepts:
- Events— Points of interests in the code of an application such as SQL Server. In the example, we’re interested in the event fired when a T-SQL statement is finished (sqlserver.sql_statement_completed).
- Packages— Containers for Extended Events objects (events, targets, actions, types, predicates, and maps). In our example we use package0, which contains Extended Events system objects.
- Targets— The event consumers. In the case of our example, we use the Event Bucketing Target, which retains data (the sql_text) for the sql_statement_completed event.
- Actions— Programmatic responses or a series of responses to an event (such as capturing the T-SQL stack collection or generating a user dump). For the previous example, the action is collecting the sql_text column for the sql_statement_completed event. Other possible actions include capturing a stack dump, aggregating event data, appending information to event data, or storing state information.
- Predicates— Boolean expressions that filter the events. In our example, the sqlserver.database_id predicate filters the events that occurred in the database with the ID of 1 (the master database).
- Types— Encapsulate the characteristics of collected data. For example, the type of the sqlserver.database_id predicate is pred_source.
- Maps— Associate internal values to more meaningful descriptions. For example, the internal value 3 for lock_resource_type is mapped to the string FILE (which is self explanatory). The complete mappings can be obtained by running the following code:
SELECT name, map_key, map_value FROM sys.dm_xe_map_values - Sessions— Contain targets and the enabled events.
The next section of the chapter goes deeper in explaining the XEVENT elements.
XEVENT architecture
If you need more details about the Extended Events architecture, this section is for you. I also encourage you to use Books Online for additional information.
SQL Server Extended Events engine
The SQL Server Extended Events engine is a collection of services and objects that enables you to define events, process event data, and manage the XEVENT sessions, services, and objects. One characteristic that makes XEVENT a general event infrastructure is that the events engine is event-agnostic. The processes that use the engine define the interaction with the engine and supply event points and actions to take when an event fires. Thus any event can be bound to any target.
Figure 1 represents the high-level architecture of the SQL Server Extended Events engine:
- The SQL Server Extended Events engine is integrated in the SQL Server database engine. A host process contains only one instance of the Extended Events engine. It handles session management, dispatching, and memory buffers for events.
- A session can contain enabled events, targets, and buffers.
- Instances of targets may be created and added to a session.
- The memory buffers for the events are dispatched to targets when they’re filled.
- Dispatchers are worker threads.
- Each Windows process can have one or more modules (a Win32 process or Win32 module). Each of the Windows process modules can contain one or more Extended Events packages, which contain one or more Extended Events objects (types, targets, actions, maps, predicates, and events).
Figure 1. SQL Server Extended Events engine
Packages
Packages are containers for Extended Events objects such as events, targets, actions, types, predicates, and maps. As already mentioned, a Windows process can have one or more modules (a Win32 process or Win32 module), also known as binaries or executable modules. Each binary can contain one or more Extended Events packages.
Packages are identified by a name, a GUID, and the binary module that contains the package. Figure 2 gives a high-level representation for the architecture of a package.
Figure 2. Extended Events packages
SQL Server 2008 has three public packages (package0, sqlos, sqlserver) and a private one for the Security Audit feature (SecAudit). The code in listing 3 uses the sys.dm_xe_packages Dynamic Management View (DMV) to list the names and the description of packages present on a SQL Server instance.
Listing 3. Using sys.dm_xe_packages to list packages
SELECT name, description FROM sys.dm_xe_packages
The output is shown in listing 4.
Listing 4. Output from running listing 3
name description
---------- -----------------------------------------------------------
package0 Default package. Contains all standard types, maps, compare
operators, actions and targets
sqlos Extended events for SQL Operating System
sqlserver Extended events for Microsoft SQL Server
SecAudit Security Audit Events
Another DMV, sys.dm_xe_objects, allows you to get all objects contained by a package.
Events
Events are specific points of interest in the code of an application. In the case of SQL Server, they can be the start of a broker activation task, a lock timeout, a cache hit, and so on.
When an event fires, it signals that the point of interest was reached. The firing of an event can be used for tracing purposes or triggering actions. The actions (or the consumption of the event) can be synchronous or asynchronous.
Each event has a versioned schema that defines its payload (content). This schema is represented by event columns with well-defined types.
Event classification follows the ETW model (channel, keyword) to facilitate the integration of Extended Events with ETW and its tools.
Targets
The event consumers are the targets. The events are separated from consumers, a fact that allows any target to receive any event. Targets can process single events or full buffers of events, and can do so synchronously or asynchronously. They can start a task, write to a trace file, or aggregate event data.
The targets included in the three public packages of SQL Server 2008 are listed in listing 5. Listing 6 shows the output.
Listing 5. Enumerating Extended Events targets
SELECT
o.name,
o.description
FROM sys.dm_xe_objects o
JOIN sys.dm_xe_packages p
ON p.guid = o.package_guid
WHERE p.name in ('package0','sqlos','sqlserver')
AND o.object_type='target'
Listing 6. Output from running listing 5
name description
----------------------- ------------------------------------------------
etw_classic_sync_target Event Tracing for Windows (ETW) Synchronous
Target
synchronous_bucketizer Synchronous bucketizing target
asynchronous_bucketizer Asynchronous bucketizing target
asynchronous_file_target Asynchronous file target
pair_matching Pairing target
synchronous_event_counter Synchronous Counter target
ring_buffer Asynchronous ring buffer target.
Without going into too much detail for each target (as they’re well documented in Books Online), their main usage is as follows:
- Event Tracing for Windows (ETW) Synchronous Target— Allows correlation between SQL Server and Windows
- Synchronous bucketizing target and Asynchronous bucketizing target— Allows creation of a histogram over event data in order to find the hot spots
- Asynchronous file target— Saves event data to a file for later analysis
- Pairing target— Allows identification of resource misuse (such as acquiring a lock and not releasing it)
- Synchronous Counter target— Keeps statistics about how often events are fired
- Asynchronous ring buffer target— In-memory ring buffer for saving event history
Actions
Actions are defined as programmatic responses or a series of responses to an event. Actions happen before the event is available for targets to consume, and are invoked synchronously on the thread firing the event.
Events are distinct from actions, and can add data to an event’s payload. An example of an action is collecting the T-SQL stack.
Predicates
Predicates allow you to filter the events before they’re published. They can maintain state, for example firing every nth time an event occurs or storing the maximum value reached for an event. They can operate on global or local event payload data.
Types and maps
Types define the characteristics of collected data and can be events, actions, targets, or predicates. To get an idea of the existing objects and their types, you can use SELECT name,object_type,description FROM sys.dm_xe_objects. As a simple example, the deprecation_announcement has the type event and has the following description: “Occurs when you use a feature that will be removed from a future version of SQL Server, but will not be removed from the next major release of SQL Server.”
Maps allow you to associate internal values to more meaningful descriptions. If you understand the principles behind database normalization, it would make sense to you to store locking modes as byte data types rather than strings. But then, you’d have to know that lock mode 18 means RangeI-X. That’s exactly what maps allow you to do by using the sys.dm_xe_map_values DMV.
Sessions
Sessions contain targets and the enabled events. The same event can be enabled in multiple sessions. The binding of actions and predicates is done per session level.
Sessions provide policy support for buffering (how the event data is stored) and dispatch (the time the data is retained in the buffers). Sessions are also a security boundary (the user-level permissions are checked per session when the session is created or altered).
The mapping between package objects and sessions is many-to-many. The event sessions can be created and managed through the DDL statements CREATE EVENT SESSION and ALTER EVENT SESSION. The event session DDL statements allow you to add targets, events, predicates, actions, and event session level options; set an event session to autostart at server startup; and add or remove targets and events from running event sessions.
XEVENT in action
The final section of this chapter is dedicated to real-life XEVENT usage scenarios as well as to some performance considerations.
Usage scenarios
Extended Events are likely to become the preferred method of monitoring and troubleshooting; therefore, the following are some possible usage scenarios:
- Monitoring system activity using Extended Events and Event Tracing for Windows (ETW)
- Determining which queries are holding locks
- Finding objects with the most locks taken on them
- Determining which queries are exceeding certain thresholds (such as a specified duration)
- Tracking waits
- Tracking spinlock contention (spinlocks are low-level synchronization objects used to protect access to resources for short periods of time)
- Investigating latch contention
- Generating a user dump
The number of usage scenarios is quite large and beyond the scope of this chapter. But, to give you another example of using XEVENT, listing 7 shows how to generate a user dump when an error occurs.
Listing 7. Generating a user dump
USE master;
GO
-- trigger a user dump for the error 208
-- 'Invalid object name'
CREATE EVENT SESSION GenerateUserDump ON SERVER
ADD EVENT sqlos.exception_ring_buffer_recorded (
ACTION (sqlserver.create_dump_single_thread)
WHERE (error = 208 and state=1 and package0.counter < 2));
GO
ALTER EVENT SESSION GenerateUserDump ON SERVER
STATE=START;
GO
-- Generate the 208 error
SELECT *
FROM NonExistentTable;
-- Cleanup
DROP EVENT SESSION GenerateUserDump ON SERVER;
GO
After generating the error, if you inspect the SQL Server error log, you’ll find something similar to listing 8.
Listing 8. Output from running listing 7
Source Message
------ --------------------------------------------------------------------
spid51 External dump process return code 0x20000001.<nl/>External dump
process returned no errors.
spid51 Stack Signature for the dump is 0xC0D2B2AE
spid51 Short Stack Dump
spid51 * ------------------------------------------------------------------
spid51 * ******************************************************************
spid51 *
spid51 * SELECT * FROM NonExistentTable;
spid51 * Input Buffer 92 bytes -
spid51 *
spid51 * Dump triggered by event 'exception_ring_buffer_recorded'.
spid51 *
spid51 * BEGIN STACK DUMP:
spid51 *
spid51 ********************************************************************
spid51 ***Stack Dump being sent to C:Program FilesMicrosoft SQL
ServerMSSQL10.MSSQLSERVERMSSQLLOGSQLDump0002.txt
spid51 *
spid51 * User initiated stack dump. This is not a server exception dump.
System health session
One feature based on Extended Events is the SQL Server Audit feature that allows you to monitor server-level and database-level groups of events or individual events.
Whereas the SQL Server Audit is known and well documented, the system health session, another feature based on XEVENT, is quite unheard of.
The system health session is an always-on session that captures events considered unhealthy. The events are captured using the ring buffer target. To convince yourself, run the statement shown in listing 9.
Listing 9. Querying sys.dm_xe_sessions to determine oldest session
SELECT TOP 1 name, total_buffer_size FROM sys.dm_xe_sessions
ORDER BY create_time;
The result should look like the following table.
name total_buffer_size
------------- -------------------------------------------------------------
system_health 4324761
How do we interpret the results? The name of the session is self explanatory, and the other column reveals that the ring buffer stores 4 MB worth of information.
To go further, we can see what events are captured by this session by running the code presented in listing 10.
Listing 10. Getting the system health session active events and filters
SELECT
e.event_name,
e.event_predicate
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_events e
ON s.address = e.event_session_address
WHERE s.name = 'system_health';
From the output, we can conclude that system health records the following events:
- error_reported
- xml_deadlock_report
- scheduler_monitor_non_yielding_ring_buffer_recorded
- wait_info
- wait_info_external
Complex predicates limit the events that fire to out-of-memory errors, severe errors, lock waits exceeding 30 seconds, latches exceeding 15 seconds, and deadlocks.
The following is a partial example of the predicates used to limit the number of the events:
<or>
<leaf>
<comparator name="greater_than_equal_int64" package="package0"></
comparator>
<event name="error_reported" package="sqlserver" field="severity">
</event>
<value>20</value>
</leaf>
...
To get the information recorded by the system health session, you can use the code in listing 11.
Listing 11. Obtaining information from the system health session
-- Getting the information
DECLARE @MyXML XML;
SELECT @MyXML = CAST(T.target_data AS XML)
FROM sys.dm_xe_session_targets T
JOIN sys.dm_xe_sessions S
ON S.address = T.event_session_address
JOIN sys.server_event_sessions ES
ON S.name = ES.name
WHERE S.name = 'system_health';
-- Display the information as raw XML
SELECT @MyXML AS XML_Representation;
Performance considerations
Before we conclude this chapter and before using XEVENT, you should keep in mind the following facts:
- Though the performance cost for a single event firing is light—just 2 μs CPU time on a 2 GHz processor—consuming the event is a different story. So you may consider asynchronous targets instead of synchronous targets, as the former have less effect on runtime performance.
- You can partition session buffers to improve scalability. As the number of CPUs grows, event session buffers can be partitioned to keep overhead low.
- Examine event retention needs—XEVENT allows you to specify the event retention by configuring the EVENT_RETENTION_MODE event session option (ALLOW_SINGLE_EVENT_LOSS—an event can be lost from the session; ALLOW_MULTIPLE_EVENT_LOSS—full event buffers containing multiple events can be lost from the session; NO_EVENT_LOSS—no event loss is allowed).
- Retaining every event may have a much larger impact from a performance point of view because firing threads will stall waiting on a free buffer.
- Many events can’t be added to an event session with NO_EVENT_LOSS.
- Choose ALLOW_SINGLE_EVENT_LOSS or ALLOW_MULTIPLE_EVENT_LOSS whenever possible.
Summary
Extended Events is a vast subject. You could probably fill a book and still just touch it. Yet I hope that this chapter convinced you to take a look at this new event infrastructure.
About the author
Cristian Lefter is a SQL Server MVP and a former SQL Server developer, currently working as a consultant at Micro Training, a consulting and training company. Cristian is a writer, blogger, SQL expert, and frequent reviewer for Manning, Apress, Wiley, and other publishers, as well as for ASPToday, Simple-Talk, Microsoft E-Learning, Asentus, Content Master, GrandMasters, and so forth. He is based in Bucharest, Romania.
You can reach him at his blog, http://sqlserver.ro/blogs/cristians_blog/default.aspx, or his email address, [email protected].