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.
The following are some of the characteristics of Extended Events that I consider to be important:
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.
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.
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:
The next section of the chapter goes deeper in explaining the XEVENT elements.
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.
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:
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.
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.
SELECT name, description FROM sys.dm_xe_packages
The output is shown in listing 4.
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 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.
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.
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'
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:
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 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 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 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.
The final section of this chapter is dedicated to real-life XEVENT usage scenarios as well as to some performance considerations.
Extended Events are likely to become the preferred method of monitoring and troubleshooting; therefore, the following are some possible usage scenarios:
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.
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.
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.
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.
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.
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:
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.
-- 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;
Before we conclude this chapter and before using XEVENT, you should keep in mind the following facts:
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.
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].