DaveWentzel.com            All Things Data

Extended Events

 

Overview
Ships with 2008.
Correlates SQL Server data, OS data, and database application data using Event Tracing for Windows (ETW).  It works on all Editions of 2008 on any OS release at least XP/2003 or later. 
 
Extended Events is often abbreviated as XE.  XE just allows you to trace events, conceptually not much different from SQL Trace.  But it also pulls together information that you can't easily correlate from just the event in Profiler, namely information on waits, q'ing, and other information we could only rudimentarily correlate with DMVs. 
 
There is a 3rd Party GUI tool that works pretty well with Extended Events at codeplex.  Extended Events can even be scripted with PowerShell Home
 
 
Events
...fire synchronously but can be processed synch or asynch.  An event is a defined point in your TSQL.  There are over 250 events that ship with 2008...last I checked. 
 
SELECT p.name, o.*
FROM sys.dm_xe_objects o
JOIN sys.dm_xe_packages p
ON p.guid = o.package_guid
AND o.object_type = 'event';
 
Specific event payload:
SELECT *
FROM sys.dm_xe_object_columns
WHERE object_name = 'sql_statement_completed';
 
 
Predicates
...filter events before they are consumed. 
 
Actions
...a set of commands that are performed synchronously before an event is consumed.  These can be expensive.
 
SELECT p.name, o.*
FROM sys.dm_xe_objects o
JOIN sys.dm_xe_packages p
ON p.guid = o.package_guid AND o.object_type = 'action'
 
Targets
...the consumer of the event.  Any target can consume any event.  Can consume events synch or asynch.  To find the list of available targets...
 
AND o.object_type = 'target'
 
It's basically where to log the data.  A simple target is a file for instance. 
 
Syntax looks something like:
CREATE EVENT SESSION <name>
ON SERVER
ADD EVENT...
ADD TARGET <pkg>.<name> (various options depending on target)
 
 
Packages
...containers that define and associate events, actions, and targets.  Documentation on packages
 
Sessions
...is a way of linking together objects for processing.  Can link objects from any registered package.  You manipulate these using TSQLsessions documentation
 
SELECT * FROM sys.dm_xe_sessions
 
 
CREATE EVENT SESSION
 
 
Extended Events Output
...is XML.  You can read the XML by using sys.fn_xe_file_target_read_file (depending on your target). 
 
 
Peformance
...is the key.  Some factors to consider
  • synch vs asynch. 
  • if you use asynch targets then you need to consider how to configure event buffering. 
  • event loss.  On a busy system you can configure the system to handle individual event loss, loss of an entire buffer, or no event loss.  The last option can be a performance killer on a very busy system. 
 
Extended Event Examples
I've just started to look at this.  BOL mentions using this to determine which queries are holding locks and find the objects that have the most locks taken on them.  It would seem as though there are better ways to do this but I can see the usefulness using just these examples. 
 
--find me Sev 20 and higher errors
SELECT CAST(target_data as xml) AS targetdata
INTO #system_health
FROM sys.dm_xe_session_targets xet
JOIN sys.dm_xe_sessions xe
    ON xe.address = xet.event_session_address
WHERE name = 'system_health'
AND xet.target_name = 'ring_buffer';
 
SELECT
    DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), CURRENT_TIMESTAMP), xevents.event_data.value('(@timestamp)[1]', 'datetime2')) AS ErrorTime,
    xevents.event_data.value('(data[@name="severity"]/value)[1]', 'bigint') AS ErrorSeverity,
    xevents.event_data.value('(data[@name="error_number"]/value)[1]', 'bigint') AS ErrorNumber,
    xevents.event_data.value('(data[@name="message"]/value)[1]', 'nvarchar(512)') AS ErrorMessage,
    xevents.event_data.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS SQLText,
    xevents.event_data.query('.') as EventDtls
FROM #system_health
CROSS APPLY targetdata.nodes('//RingBufferTarget/event') AS xevents (event_data)
WHERE xevents.event_data.value('(@name)[1]', 'nvarchar(256)')='error_reported';
 
 
 
system_health Session
...is a predefined session, set to run by default, that ships with 2008.  It tracks events to a ring buffer to ensure that it does not consume too much memory.  More info.  system_health Session
 
SELECT ...
FROM sys.dm_xd_session_targets t
JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
WHERE s.name = 'system_health'
 
 
 

Add new comment