Introduction to SQL Server 2008 Extended Events

By Mladen Prajdić on 19 May 2009 | Tags: Administration , Profiler & Trace , SQL Server 2008 Features

SQL Server 2008 Extended Events are the new low level, high performance eventing system in SQL Server. They use less system resources and provide better tracking of SQL Server performance than previous methods like Perfmon and SQL Trace/Profiler events.

Performance Analysis history - SQL Server 2000 & 2005

In versions prior to SQL Server 2008 the tools for performance analysis were good but they had their cons. The methods and tools got better with each new version. However the common problem with all these is that the data is mostly pre-aggregated.

In SQL Server 2000 we used

System tables:  A wide array of tables that hold data with various system information.

Perfmon: A monitoring utility for server status with a wide variety of counters.

SQL Server Profiler/SQL Trace: A way to monitor SQL Server activity in real time.

PSSdiag: A general purpose diagnostic collection utility that natively collects Performance Monitor logs, SQL Profiler traces, SQL Server blocking script output, Windows Event Logs, etc...

With SQL server 2005 we got

Dynamic Management Views (DMV's) and Functions (DMF's): This new feature is a big leap forward in performance troubleshooting. These provide server state information that can be used to monitor the health of a server instance or diagnose problems.

Event Notification: We could get asynchronous notifications when subscribing to a subset of profiler events. This was and still is one of my favorite ways of debugging deadlocks.

SQLdiag & SQL Nexus: SQLdiag is a successor of PSSdiag. SQL Nexus is a tool used to load and analyze performance data collected by SQLdiag (SQL Server 2005) and PSSdiag (SQL Server 2000).

DMVStats: An application that can collect, analyze and report on SQL Server 2005 DMV performance data.

Event Tracing for Windows: ETW is a low level event tracing used to troubleshoot a wide variety of applications.  SQL Server 2005 has full support for this and can be used to troubleshoot problems. This feature isn't well known and is worth getting to know.

The problem with pre-aggregated data

Pre-aggregated data means that the counters are cumulative. In SQL Server 2000 and 2005 90+ percent of all counters were pre-aggregated. To get meaningful information we had to query those counters at different times which meant we actually had a sampling based system. And by definition sampling looses data. The other problem was that sampling too often would result in system strain.

To see this just look at the sysperfinfo system table (SQL Server 2000) or sys.dm_os_performance_counters DMV (SQL Server 2005). They both hold various performance counters for the system but all time base counters are cumulative and need sampling.

To get around this problem we had to use SQL Server Profiler/SQL Trace to get real time information on system activity. However there are a lot of stories of how SQL Server Profiler took a production system down in one way or another. Just google it up :)

In SQL Server 2008 this problem was addressed in a very cool way. Enter SQL Server 2008 Extended Events.

What are SQL Server 2008 Extended Events

SQL Server 2008 Extended Events are a completely new general eventing system. They are defined based on Event Tracing for Windows (ETW) which allows us to integrate SQL Server tracing with ETW traces from the operating system, IIS and other ETW enabled applications.

Events themselves fire synchronously but can be processed either synchronously or asynchronously depending on the use case. They are extremely fast and don't put noticeable strain on the system. For example a reported figure by Microsoft is that 20,000 events/sec on a 2 GHZ Pentium with 1 GB RAM takes less than 2% of the CPU. This means that on a concrete heavy duty server the performance impact is very close to zero.

Another good thing is that the Microsoft PSS team introduced a system_health Extended Events Session, which is always on and collects different high severity error data when it happens. This gives them an instant idea about system stability and problems speeding up the whole problem tracing and solving.

For now the only SQL Server 2008 feature built on top of the Extended Events is the the Auditing capability but the next version will probably have all other eventing structures moved to Extended Events.

SQL Server 2008 Extended Events Building Blocks

As they are a completely new capability of SQL Server 2008 we have to learn some new terminology.

Packages

A package holds all components needed for Extended Events. A package exists in an EXE or DLL. Currently there are 4 packages and they all reside inside SQLSERVER.EXE. Those 4 packages are: Package0 (default), Sqlos, Sqlserver, SecAudit (is private and used only for the built-in SQL Server auditing). Existing packages can be viewed with this SQL statement:

select * from sys.dm_xe_packages

Events

Events are points in code that we can trace. They always execute synchronously. However forwarding them to a target is done either synchronously or asynchronously. Currently there are 254 events defined in SQL Server 2008. I expect that with with future service packs and versions more will be added. Each event contains a set of columns called a payload. The payload is the default collected data for the event. Since each event is defined with the ETW model they easily integrate into the broader ETW tracing outside the SQL Server. Existing events and their payload can be viewed with these SQL statements:

-- view all defined events in sql server
SELECT * FROM sys.dm_xe_objects WHERE object_type = 'event' ORDER BY name
-- view all columns for each event - the event payload
SELECT * FROM  sys.dm_xe_object_columns

Predicates

Predicates are used to filter events based on some conditions before they are sent to the target. They fully support short-circuiting so it's a good idea to first specify simple predicates followed by more complex ones. All predicates are processed synchronously so be aware that heavy duty conditions can cause some performance degradation. It's best to keep predicates simple like filtering the event based on a transaction ID or object ID or something similar. Existing predicates can be viewed with this SQL statement:

SELECT * FROM sys.dm_xe_objects WHERE object_type in ('pred_compare', 'pred_source') ORDER BY name

Actions

Actions are commands that provide additional information for an event. Actions provide additional data like SQL statement text, SQL statement execution plan handle or a resource governor pool that the SQL statement is being run in and are appended to the event payload. Any action can be linked to any event. They are also processed synchronously after all predicates have been evaluated and before the event is sent to its target. Existing actions can be viewed with this SQL statement:

SELECT * FROM sys.dm_xe_objects WHERE object_type = 'action' ORDER BY name

Targets

There are currently 6 targets for event consuming events.

Event bucketing

The event bucketing can be viewed as a histogram of values. We pick events to monitor and the target creates a histogram (actually an XML document) of how many times each event was fired. This target is both synchronous and asynchronous.

Event pairing

The event pairing show us incomplete events that can be paired. So for example if we're monitoring lock_acquired and lock_released events the event pairing target will show only payload of lock_acquired events for which lock_released event hasn't happened yet. This target is only synchronous.

ETW target

The ETW target is a file that can be merged with other ETW logs from OS or IIS or some other ETW enabled application. For it to work the SQL Server service startup account has to be in the "Performance Log Users" group. This is a binary file that has to be formatted with tracerpt.exe to make it human readable. This target is only synchronous.

Event file

The event file is just a binary file that all events are written to. We can set the size of the file, the maximum of rolled over files and the file increment size in MB. The event files can be read by using the sys.fn_xe_file_target_read_file built in function. This target is only asynchronous.

Synchronous event counter

The synchronous event counter target, as its name suggests, only counts how many times an event has been fired. It does not collect any other data. This makes it very useful for getting an overview of system activity. This target is only synchronous.

Ring buffer

The ring buffer target is a circular in-memory only target that is represented by a FIFO queue. It can work in two modes: strict FIFO and per-event FIFO. The strict FIFO acts like a normal FIFO queue where 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 . We set the ring buffer size (max_memory) and the number of held events (occurrence_number) when defining a session. This target is only asynchronous.

  All targets can be viewed with this SQL statement:

SELECT * FROM sys.dm_xe_objects WHERE object_type = 'target' ORDER BY name

Note that the security audit target is private and can only be used by SQL Server for built in auditing functionality.

Sessions

Sessions are means of grouping events, actions, predicates and targets. Any events can be linked with any action within a session. This means that we can have one event defined in multiple sessions independently of each other.

For each session we have to define how much memory is used for event buffering, how many events can be disregarded if there is memory pressure, how long will the events stay in the buffer before being sent to the target, whether the session will be auto started when SQL Server service starts and if we want to enable causality tracking. Causality tracking enables correlating related events on different connections. After a session is created we always have to start it using the ALTER EVENT SESSION statement since they are always created in the stopped state. Existing sessions can be viewed with this SQL statement:

SELECT * FROM sys.dm_xe_sessions

Unless it was dropped there should always be the built-in system_health session running.

Types and Maps

A type is a simple or complex data type that is used in the event payload. Maps are dictionaries that map some numeric value to a text. Existing types and maps can be viewed with these SQL statements:

SELECT * FROM sys.dm_xe_objects WHERE object_type in ('map', 'type') ORDER BY name; 
SELECT * FROM sys.dm_xe_map_values;

You can look through the Extended Events DMV's you can query them to get any information you desire. The below picture shows the grouping of all Extended Events terminology we've learned above:

Open Source SQL Server 2008 Extended Events Management Software

Extended Events Manager: A GUI to create and manage Extended Events since their manipulation is pure T-SQL for now.

NTrace:  A library that provides flexible, high-performance, and zero-configuration trace logging for your C# applications. With this you enable ETW for your applications and thus can integrate their trace with SQL Server 2008 Extended Events ETW target for complete trace picture.

XPerf: A tool that eases managing Windows ETW traces. You can use this to ETW trace IIS, while your application uses NTrace and SQL Server 2008 uses an Extended Events ETW target. Then you can combine all 3 traces and analyze them.

Simple Example

This is the full code needed for running a simple Extended events session. The comments in code provide info about what each part does.

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE Name='BucketizerExampleSession')
    DROP EVENT SESSION BucketizerExampleSession ON SERVER
GO
-- create the event session
CREATE EVENT SESSION BucketizerExampleSession ON SERVER
    -- will only check for any lock_acquired event. 
    -- this includes any kind of lock (S, X, IX, etc...)
    ADD EVENT sqlserver.lock_acquired 
    (
        -- 8 is the AdventureWorks DB_ID on my machine
        WHERE database_id = 8 
    ) 
    ADD TARGET package0.synchronous_bucketizer 
    ( 
        -- only show us lock_acquired events
        SET filtering_event_name='sqlserver.lock_acquired', 
            -- source for the bucketizer data comes from event (0), 
            -- could come from action (1)
            source_type=0, 
            -- resource_0 is actually an object_id
            source='resource_0' 
    )
    -- set any of the session properties which are:
    -- MAX_MEMORY, EVENT_RETENTION_MODE, MAX_DISPATCH_LATENCY, MAX_EVENT_SIZE, 
    -- MEMORY_PARTITION_MODE, TRACK_CAUSALITY, STARTUP_STATE
    WITH (MAX_MEMORY = 60MB, MAX_EVENT_SIZE = 30MB);
GO
-- we have to manually start the session
ALTER EVENT SESSION BucketizerExampleSession ON SERVER 
STATE = START
GO
-- run some simple test queries
SELECT TOP 1 * FROM Sales.vIndividualCustomer
SELECT TOP 1 * FROM Sales.vIndividualDemographics
SELECT TOP 1 * FROM Production.vProductModelInstructions
GO

-- The XML data of the Bucketizer target
SELECT    CAST(t.target_data AS XML) AS xmlLockData
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 ss ON s.Name = ss.Name
WHERE    t.target_name = 'synchronous_bucketizer' 
        AND s.Name = 'BucketizerExampleSession'

-- tabular data from the upper XML
SELECT  name, OBJECT_ID, lock_count 
FROM (
        SELECT  LockStats.value('.','bigint') AS lock_object_id, 
                LockStats.value('@count', 'bigint') AS lock_count
        FROM    (
                 SELECT CAST(t.target_data AS XML) AS xmlLockData
                 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 ss ON s.Name = ss.Name
                 WHERE   t.target_name = 'synchronous_bucketizer' 
                         AND s.Name = 'BucketizerExampleSession'
                ) Locks
                CROSS APPLY xmlLockData.nodes('//BucketizerTarget/Slot') AS T(LockStats)
     ) LockedObjects 
     -- join to sys.objects to get object name
     JOIN sys.objects o ON LockedObjects.lock_object_id = o.object_id 
WHERE o.Type = 'U' -- get results only for user defined tables
ORDER BY lock_count DESC
GO

-- manually stop the session
ALTER EVENT SESSION BucketizerExampleSession ON SERVER
STATE = STOP
GO

All information about Extended events can be seen with sys.dm_xe_* DMV’s and sys.server_event_* catalog views. Also remember to start the session after creating since it’s always created in the stopped state.

Conclusion

This is an introductory article to get acquainted with the new SQL Server 2008 Extended Events infrastructure and terminology. Although they are so far the most powerful troubleshooting method yet in SQL Server, they have a few laps to go to really get there and be fully useful. They should be used as your main tool to resolve hardcore performance issues, but don’t forget about the others.