Introduction to Extended Events in SQL Server 2008


Home About Us Sign Guest Book Contact Us Subscribe RSS

SQL

Articles

.NET

Blogs

Newsletter

Click on the image to subscribe to our Monthly Newsletter.

Click here for Archives

Expert Chat

No Chats Available

Introduction to Extended Events

Extended Events (xEvents) is something that we introduced with SQL Server 2008 version. This is a complete tracing environment and must NOT be confused with the profiler version. Though the fundamentals of xEvents revolves around a general event-handling and at certain cases can be linked to OS functions via the ETW (Event Tracing for Windows). ETW has been around with the OS for a while now and this detailed view of stack dumps of memory, threads for a given process are quite a revelation to many. Though the number of people who would like this level of sophistication is limited, it is always good to know these features as in highly-transactional production environment systems they can come really handy for troubleshooting or performance tuning if needed. In this article we will get some basics understanding on how to use xEvents with SQL Server 2008.

Taxonomy demystified

Before we get into a typical example of how we can create a xEvent inside SQL Server, let us look at some of the taxonomies that are involved with xEvents. One need to keep in mind that xEvents is not first class objects inside SQL Server but is more of an infrastructure for tracing and logging in some scenarios.

Event

A name associated with interesting point in the code. Events have properties called fields. Events can be used for tracking or taking actions.

Action

Fundamentally is a response to an event. Always synchronous call. There can be multiple actions for a single event.

Target

Consume the event data and do with it what you want. Comes in two flavors asynchronous and synchronous.

Predicate

Some expression that establishes criteria for when to pass event data to targets – in T-SQL terms like a filter.

Map

Group of logical names that map to values. One of the features of maps is provide keywords and channels.

Package

A named collection of the above objects

And these are bound within a session context. The mappings between the package objects and the session are a many-to-many relation and this is not a rigid relation. One more sub categorization that happens under Event is the channel and keyword. The Channel identifies the audience classification like Admin, Operational, Analytical and Debug while Keyword helps in finer grouping of Events similar to the groupings of our SQL Traces inside Profiler.

Simple example – table missing

Now having some foundation, let us make a simple xEvent and let us monitor the same. The scenario is a simple error of object not found that we will try to log. First we need to create a session that will capture the event. A typical syntax looks like:

create event session xEvents_Session on server

-- Session Name

add event sqlserver.error_reported

-- Event we want to capture

(

action

(

sqlserver.session_id,

sqlserver.sql_text

-- What contents to capture

)

where sqlserver.error_reported.error = 208

and package0.counter <= 5

-- Some predicate or filter (here it is object not found error number)

)

add target package0.ring_buffer

with (max_dispatch_latency=1seconds)

-- The target. We are just using the buffers (dm_os_ring_buffers will have it)

GO

There are a whole bunch of DMVs you can query to get information about the session, action and targets. These are part of the sys.server_event_* set of objects.

-- Session is available

select ses.name, sese.name, sese.package, sese.predicate

from sys.server_event_session_events sese

join sys.server_event_sessions ses

on ses.event_session_id = sese.event_session_id

where ses.name = 'xEvents_Session'

-- Action's for the session

select ses.name, sesa.name, sesa.package

from sys.server_event_session_actions sesa

join sys.server_event_sessions ses

on ses.event_session_id = sesa.event_session_id

where ses.name = 'xEvents_Session'

-- Targets for the Session

select ses.name, sest.name, sest.package

from sys.server_event_session_targets sest

join sys.server_event_sessions ses

on ses.event_session_id = sest.event_session_id

where ses.name = 'xEvents_Session'

Till now we have just initialized the event, the event as such is NOT running. We will need to start the session. We will alter the session and the command looks like:

alter event session xEvents_Session on server

state = start

To check our session state, you can query another set of DMVs which start with sys.dm_xe_* as:

select * from sys.dm_xe_sessions where name like 'xEvents_Session'

Just to make create an error by making a SELECT to a non-existent object like:

Select * from ExtremeExperts

Output:

Msg 208, Level 16, State 1, Line 1

Invalid object name 'ExtremeExperts'.

Just make a note of the error number, level and state. To get the dump from the buffer which our Extended Event added, just query them off the sessions DMV’s like:

select CAST(xet.target_data as xml) from sys.dm_xe_session_targets xet

join sys.dm_xe_sessions xe

on (xe.address = xet.event_session_address)

where xe.name = 'xEvents_Session'

The output looks like:

Thatz it. As simple as it gets. Now for folks would want to undo the operations we did in the article, just stop the event and drop the same. And the commands are:

alter event session xEvents_Session on server state = stop

drop event session xEvents_Session on server

Conclusion

We just took a quick tour of Extended Events from basics to a simple demo of using Extended Events. This in anyway doesn’t show the real power of what Extended Events can achieve. In subsequent articles I will try to get a little deeper with some niche scenario’s to use xEvents to troubleshoot and performance tuning SQL Server. Do drop in your comments …

	Comment about this article
Free Hit Counters	Copyright © 2000-2003 ExtremeExperts.com, Terms of Use Best viewed on 1024x768 using Microsoft Internet Explorer 5.5 and later. Hosted on Windows Server 2003/.NET Framework 1.1.