Using XEVENT in SQL Server


Xevent related system catalog views and DMVs


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:



  1. DMVs for XEvent packages and objects
  2. System Catalog views for sessions
  3. 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 you the 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


go


     


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)


Go


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)


go


 


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)


go


ALTER EVENT SESSION test_session ON SERVER


 STATE = START


go


 


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


go


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


go


 


–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)


go


 


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


go


create table t(c int)


go


 


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


      drop event session testSession on server


go


 


–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)


go


 


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


begin


      alter event session testSession on server


      state = start


end  


go


 


begin tran


–fire the event


insert t values(1)


select * from sys.dm_tran_locks


go


rollback


go


 


–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


go


 


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)


go


 


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


begin


      alter event session testSession2 on server


      state = start


end  


go


 


–this will fire the event for testSession2


insert t values(2)


go


 


–cleanup


drop event session testSession on server


drop event session testSession2 on server


drop table t