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:
- 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
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
Comments
- Anonymous
June 07, 2009
PingBack from http://weakbladder.info/story.php?id=5155