Extended events– the objects

Before reading this blog, it might be a good idea to read the blog Your First extended event if you are not familiar with Extended events.

A friend of mine asked me (for some month ago if there was an easy way to figure out what events you can use, and that give me the idea of writing this blogpost (and today I have the time for it).

What events can we track? Well, when we setup an event session we write something like:

Create event session testSQL on server
add event sqlserver.sql_statement_completed

The text written in red is the event and the package the event belongs to. If we look at the package (sqlserver), then you can see the current packages by writing:

select name, description, guid
from sys.dm_xe_packages

It gives you the name, description and the guid for the package.

The main source of information is the view sys.dm_xe_objects, and you can utilize that by writing

select *
from sys.dm_xe_objects t1

As you can see, there is an object_type column, and it has multiple values (7 in 2008R2), but the once we are interested in now are:

  1. event
  2. target
  3. action

Combining that with the view sys.dm_xe_packages, then we get an SQL statement like the following to get the events we can use:

select pck.name as package, obj.name as eventName, obj.description
from sys.dm_xe_objects AS obj
    inner join sys.dm_xe_packages as pck on obj.package_guid=pck.guid
where object_type = ‘event’
order by pck.name, obj.name

So now you know what to write when you want to create an event session for a given event. If you are in doubt about what events you can use, then just google the event name and Extended event.

If we look at the action part of an event session, then it could look like:

Create event session testSQL on server
add event sqlserver.sql_statement_completed
        (    action (sqlserver.sql_text)
            where sqlserver.database_ID=30)

As you can see, then we have an action, where we will capture the sql_text for a database with the id = 30.

You can find the actions by replacing object_type=’event’ with object_type=’action’ in the above query:

select pck.name as package, obj.name as actionName, obj.description
from sys.dm_xe_objects AS obj
    inner join sys.dm_xe_packages as pck on obj.package_guid=pck.guid
where object_type = ‘action’
order by pck.name, obj.name

That’s very easy Smile.

The second last step we need to investigate when setting up an eventsession is the target:

Create event session testSQL on server
add event sqlserver.sql_statement_completed
        (    action (sqlserver.sql_text)
            where sqlserver.database_ID=30)
add target package0.asynchronous_file_target
        (set filename = N’C:\xetest\SQLTEST.xml’
        , metadatafile= N’C:\xetest\SQLTESTmeta.xml’
        )
with (max_dispatch_latency = 1 seconds)

As you can see, I have a target called package0.asynchronous_file_target, and I can find these targets by writing:

select pck.name as package, obj.name as actionName, obj.description
from sys.dm_xe_objects AS obj
    inner join sys.dm_xe_packages as pck on obj.package_guid=pck.guid
where object_type = ‘target’ and pck.name=’package0′
order by pck.name, obj.name

   

So the final part is now to find out how we can setup the WITH clause for an event session, and that is very easy. Read the document from Microsoft.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s