Using XEVENT in SQL Server

Article
06/04/2009

Some one asked me how to use xevent. There are lot of system catalog views and DMVs that you can query. Here I will give you a brief introduction of those catalog views and DMVs and the SQL script that we usually use.

We add extended event (short for XEVENT) feature to SQL Server 2008. Along with this feature, we provide a set of system catalog views and DMVs to allow user to monitor extended event objects and sessions. For the usage of XEvent, please check the online help.

Those views and DMVs can be divided into 3 categories:

DMVs for XEvent packages and objects
System Catalog views for sessions
DMVs for online only sessions

Category 1: XEvent packages and objects

Now we take a look at the first category. There are only DMVs in this category.

· sys.dm_xe_packages will show you the packages loaded in the system.

sys.dm_xe_objects will show you the objects available in the system. object_type will tell you the type of the object, and do a join with sys.dm_xe_packages on package_guid will give you the package the object is in. if capabilities_desc contains "private", it can be only used by system internally.

· sys.dm_xe_object_columns will show youthe columns for events and targets.

· sys.dm_xe_map_values : If you see a column has a “strange” type, it might be a map, check it up in this DMV, if it’s a map type, it will show you a meaningful string for each value. For example, value 3 for lock_mode is S and lock_resource_type 5 means OBJECT. So if you captured an lock_acquire event with lock_mode = 3 and lock_resource_type=5, you will know it’s an S lock on an object.

If you want to create an event session, you need to find out what events are available in the system, what actions you can use and what the predicates you can use when you enable an event. Following are the script that will give you the answer:

--all available event

select P.name+'.'+ O.name

from sys.dm_xe_packages P join sys.dm_xe_objects O

on P.guid = O.package_guid

where object_type = 'event'

--available event can be used when create event session

select * from sys.dm_xe_objects

where object_type = 'event'

and (capabilities_desc not like '%private%'

or capabilities is null)

and name like '%begin%'

--available action

select P.name+'.'+ O.name, O.type_name

from sys.dm_xe_packages P join sys.dm_xe_objects O

on P.guid = O.package_guid

where object_type = 'action'

--available pred_source

select P.name+'.'+ O.name, O.type_name

from sys.dm_xe_packages P join sys.dm_xe_objects O

on P.guid = O.package_guid

where object_type like 'pred_source'

--available pred_compare

select P.name+'.'+ O.name, O.type_name

from sys.dm_xe_packages P join sys.dm_xe_objects O

on P.guid = O.package_guid

where object_type like 'pred_compare'

and O.type_name like 'unicode%'

--event data columns for an event, here I use lock_acquired event

select * from sys.dm_xe_object_columns

where object_name = 'lock_acquired' and column_type = 'data'

--when specify predicate for a map column, you need specify the key value instead of map value

--find a map value

select * from sys.dm_xe_map_values

where name = 'lock_resource_type'

--available target

select P.name+'.'+ O.name

from sys.dm_xe_packages P join sys.dm_xe_objects O

on P.guid = O.package_guid

where object_type = 'target'

--target cumstomizable columns, you must specify value for mandatory column

--when you add the target to a session

select O.name, C.name, C.column_type, C.capabilities_desc

from sys.dm_xe_object_columns C left join sys.dm_xe_objects O

on (C.object_name = O.name and C.object_package_guid = O.package_guid)

where

O.object_type = 'target'

and C.column_type = 'customizable'

and C.capabilities_desc like '%mandatory%'

and O.name like 'pair_matching'

Category 2: Views for Sessions

Following system catalog views will tell you the information about all sessions – no matter the session is online or offline. When a session is just created or stopped, it is offline, once you start it, it’s considered online.

· sys.server_event_sessions will show you all the sessions (both online and offline).

· sys.server_event_session_targets will show you all the targets in the sessions.

· sys.server_event_session_events will show you all the events you enabled in the sessions.

· sys.server_event_session_actions will show you all the actions you attached to the enabled events.

· sys.server_event_session_fields will show you the values that was provided for the customizable fields for the events and targets in the sessions.

Here is the script you can use:

--check existing sessions in the server

select * from sys.server_event_sessions

--check the event in a session

select S.name as sessionName, E.package + '.' + E.name as event_name, E.predicate

from sys.server_event_session_events E left join sys.server_event_sessions S

on E.event_session_id = S.event_session_id

--where S.name = 'system_health'

--check the actions in a session

select S.name as sessionName, E.package + '.' + E.name as event_name, A.package + '.' + A.name as action_name

from sys.server_event_session_actions A

left join sys.server_event_session_events E

on A.event_id = E.event_id

left join sys.server_event_sessions S

on E.event_session_id = S.event_session_id

--where S.name = 'system_health'

--check the targets in a session

select S.name as sessionName, T.package + '.' + T.name as event_name

from sys.server_event_session_targets T left join sys.server_event_sessions S

on T.event_session_id = S.event_session_id

--where S.name = 'system_health'

Category 3: DMVs for online sessions only

Following DMVs only contain information for online session (the session that you started).

· sys.dm_xe_sessions will show you all the online sessions

· sys.dm_xe_session_targets will show you information about targets enabled in those sessions.

· sys.dm_xe_session_events will show you information about events enabled in those sessions.

· sys.dm_xe_session_event_actions will show you information about actions appended to events in the sessions.

· sys.dm_xe_session_object_columns will show you the values you provided for customizable columns for the events and targets in the sessions.

--check started session

select * from sys.dm_xe_sessions

--where name = 'system_health'

--check target event

select T.target_name, convert(xml, T.target_data)

from sys.dm_xe_session_targets T

left join sys.dm_xe_sessions S

on T.event_session_address = S.address

--where S.name = 'system_health'

Manage event session

Create event session

Here is an example that give you all potential usage of create event session:

if exists(select * from sys.server_event_sessions where name = 'testsession')

drop event session testSession on server

CREATE EVENT SESSION testSession ON SERVER

ADD EVENT [CE79811F-1A80-40E1-8F5D-7445A3F375E7].[sqlserver].[cdc_error]

(

ACTION ([CE79811F-1A80-40E1-8F5D-7445A3F375E7].[sqlos].[worker_address])

WHERE (NOT ((NOT ( ( ([error_severity] >= 156))))))

ADD EVENT sqlserver.lock_acquired

(

ACTION ([sqlos].[task_elapsed_quantum], sqlserver.session_id)

ADD EVENT [sqlos].[multiple_tasks_enqueued]

(

WHERE ([sqlserver].[session_resource_pool_id] = 157)

)

ADD TARGET [CE79811F-1A80-40E1-8F5D-7445A3F375E7].[package0].[ring_buffer],

ADD TARGET [CE79811F-1A80-40E1-8F5D-7445A3F375E7].[package0].[asynchronous_file_target]

( SET filename = N'd:\temp\meta\zimbexxe.xel', metadatafile = N'd:\temp\meta\zimbexxe.xem'

ADD TARGET [CE79811F-1A80-40E1-8F5D-7445A3F375E7].[package0].[pair_matching]

( SET begin_event = N'sqlserver.event.sort_add_run_tracing', end_event = N'sqlserver.event.database_transaction_end'

)

WITH (MAX_MEMORY = 4817 KB,MAX_EVENT_SIZE = 19 MB,

EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,

MAX_DISPATCH_LATENCY = 1 SECONDS,

MEMORY_PARTITION_MODE = PER_NODE,

TRACK_CAUSALITY = ON,STARTUP_STATE = ON)

Note:

1. The numbers I used here is randomly generated, you should specify reasonable number

2. When enable event, you can reference the event by [module_guid.]| package_name.event_name, module_guid is required if the module is loaded from standalone DLL.

3. Generally the session is not online until you start it. But if you specify STARTUP_STATE = ON, if the SQL Server restarts, this session will start automatically.

Alter event session

You can do 3 things with alter event session DDL:

1. Change the session properties

2. Add or remove event and/or target

3. Start or stop the session

But you cannot start/stop event session at the same time you update the session properties or add/remove event/target.

Here are some examples that you can use:

ALTER EVENT SESSION test_session ON SERVER

ADD EVENT [CE79811F-1A80-40E1-8F5D-7445A3F375E7].[sqlserver].[leaf_page_disfavored]

(

ACTION ([CE79811F-1A80-40E1-8F5D-7445A3F375E7].[sqlos].[system_thread_id])

)

WITH (STARTUP_STATE = OFF)

ALTER EVENT SESSION test_session ON SERVER

WITH (MAX_MEMORY = 1 MB,MAX_EVENT_SIZE = 3760 KB,EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,MAX_DISPATCH_LATENCY = INFINITE,MEMORY_PARTITION_MODE = PER_CPU,TRACK_CAUSALITY = OFF,

STARTUP_STATE = OFF)

ALTER EVENT SESSION test_session ON SERVER

STATE = START

ALTER EVENT SESSION test_session ON SERVER

STATE = STOP

You can add/remove event/target at any time, even when the session is online.

Drop event session

You better make sure the session exists before you try to drop it. This also applys to alter event session.

if exists(select * from sys.server_event_sessions where name = 'testsession')

drop event session testSession on server

Predicate for event

You can specify predicate when you enable an event. The predicate is a boolean evaluated string, you can use AND, OR or NOT as other boolean expression on predicate leaves. There are 3 things you can use in the predicate:

1. Pred-comp: it is a comparator, you can use it as pred-comp(pred-source|column, value)

2. Operators: following operators could be used: =, <>, !=, >, >=, <, <=. Like operator will be provided soon.

3. Pred-source: it will be evaluated as a value, such as sqlserver.session_id will return the current session id. You can use pred-source as (pred-source) operator value or pred_comp(pred-source, value)

4. Event column: it’s the data columns that you can use.

For the available pred-comp, pred-source and event column of specified event, you can use query

--available pred_source

select P.name+'.'+ O.name, O.type_name

from sys.dm_xe_packages P join sys.dm_xe_objects O

on P.guid = O.package_guid

where object_type like 'pred_source'

--available pred_compare

select P.name+'.'+ O.name, O.type_name

from sys.dm_xe_packages P join sys.dm_xe_objects O

on P.guid = O.package_guid

where object_type like 'pred_compare'

--and O.type_name like 'unicode%'

--event data columns for an event, here I use lock_acquired event

select * from sys.dm_xe_object_columns

where object_name = 'lock_acquired'

and column_type = 'data'

if pred-source or data column is xe-map, when use it in predicate, you should specify the map_key as value instead of using the map_value directly. For example, when I use sqlserver.lock_acquired event, if I want to have a predicate on OBJECT S lock. the predicate should be specifed following:

add event sqlserver.lock_acquired

(where (resource_type = 5) and (mode = 3))

Since OBJECT map-key is 5 and S lock map-key is 3.

You cannot use variables directly in the predicate, such as (sqlserver.session_id == @@spid). You have to put the query into a string and execute the command you generated. Here is an example:

if exists(select * from sys.server_event_sessions where name = 'testSession')

drop event session testSession on server

--abort task if someone try to obtain an RID X lock in current session

declare @cmd varchar(2000)

set @cmd = 'create event session testSession on server '

set @cmd = @cmd + 'ADD EVENT [sqlserver].lock_acquired '

set @cmd = @cmd + '( '

set @cmd = @cmd + ' action ([2D671FAF-67B9-4670-9466-27E187DB6EEA].[FailPointPackage].abort_task) '

set @cmd = @cmd + ' where (package0.equal_uint64(sqlserver.session_id, ' + convert(varchar,@@spid) + ') '

set @cmd = @cmd + ' and resource_type=9 and mode = 5'

set @cmd = @cmd + ' and package0.equal_i_unicode_string(sqlserver.username, ''REDMOND\cshao'')'

set @cmd = @cmd + ' )'

set @cmd = @cmd + ')'

print @cmd

exec(@cmd)

Most of the time, we need to collect related event data with the event data we collected earlier. For example, I turned on event e1, I got event data for e1, then I want to only collect event data e2 that is related to e1, such as in the same session, or in the transation, or on the same resource, etc.

Here is an example that e1 is sqlserver.lock_acquired, we get the page lock information from the data event, we can start another event session to collect the transaction information when someone else touch the same page. Here is the script:

--prepare a table

if exists(select * from sys.tables where name = 't')

drop table t

create table t(c int)

if exists(select * from sys.server_event_sessions where name = 'testSession')

drop event session testSession on server

--we try to catch page lock infomation

create event session testSession on server

ADD EVENT [sqlserver].lock_acquired

(

action (sqlserver.transaction_id)

where (resource_type=6 and mode = 8)

)

add target package0.ring_buffer

with (MAX_DISPATCH_LATENCY = 1 seconds)

if not exists(select * from sys.dm_xe_sessions where name = 'testSession')

begin

alter event session testSession on server

state = start

end

begin tran

--fire the event

insert t values(1)

select * from sys.dm_tran_locks

rollback

--get the infomation to create another session

declare @eventData xml

select @eventData = CONVERT(xml, T.target_data)

from sys.dm_xe_session_targets T join sys.dm_xe_sessions S

on T.event_session_address = S.address

where S.name = 'testSession'

and T.target_name = 'ring_buffer'

--get resource0,resource1, and resource2

declare @res0 int

declare @res1 int

declare @res2 int

select @res0 = @eventData.value('(//RingBufferTarget/event/data[@name="resource_0"]/value)[1]','int'),

@res1 = @eventData.value('(//RingBufferTarget/event/data[@name="resource_1"]/value)[1]','int'),

@res2 = @eventData.value('(//RingBufferTarget/event/data[@name="resource_2"]/value)[1]','int')

print @res0

print @res1

print @res2

--creat another session to collect the information when

--someone try to acquire IX lock on the same page

if exists(select * from sys.server_event_sessions where name = 'testSession2')

drop event session testSession2 on server

declare @cmd varchar(2000)

set @cmd = 'create event session testSession2 on server '

set @cmd = @cmd + 'ADD EVENT [sqlserver].lock_acquired '

set @cmd = @cmd + '( '

set @cmd = @cmd + ' action (sqlserver.session_id, sqlserver.session_nt_username, sqlserver.sql_text) '

set @cmd = @cmd + ' where ( resource_0 = ' + convert(varchar, @res0)

set @cmd = @cmd + ' and resource_1 = ' + convert(varchar, @res1)

set @cmd = @cmd + ' and resource_2 = ' + convert(varchar, @res2)

set @cmd = @cmd + ' and resource_type=9 and mode = 5)'

set @cmd = @cmd + ')'

print @cmd

exec(@cmd)

if not exists(select * from sys.dm_xe_sessions where name = 'testSession2')

begin

alter event session testSession2 on server

state = start

end

--this will fire the event for testSession2

insert t values(2)

--cleanup

drop event session testSession on server

drop event session testSession2 on server

drop table t

Using XEVENT in SQL Server

Xevent related system catalog views and DMVs

Category 1: XEvent packages and objects

Category 2: Views for Sessions

Category 3: DMVs for online sessions only

Manage event session

Create event session

Alter event session

Drop event session

Predicate for event

Additional resources