Extended Event Help Queries

When working with Extended Events, there are times when a little more information is, well, helpful.  You know you want to use extended events to try and monitor for a specific thing to happen.  But sometimes you don’t know if there is an event for that “thing”, or maybe you don’t know if there is a session already in place to do that specific task (or if it is even running), or maybe you just need better information about the event to see what kind of data it captures when it is fired.

Sometimes, this information is easy enough to find.  Sometimes, it is less than evident how to find the wanted information.  Some of the information may be visible through the GUI (if you are using SQL Server 2012 or later), and sometimes it is just flat out easier to run a quick script.  Today, I would like to share a few quick scripts to help find some of this information that can make the life of the DBA just a bit easier.

Does an Event Exist?

First let’s tackle the problem of discovery.  When we want to use extended events to try and troubleshoot a problem or to capture more information, it is really good to know if such an event exists.  There are many events that capture data for various different things within SQL Server.  More and more events are being added with each release.  More and more data is being made available to the DBA to help perform a better job and to help the DBA better understand what is really happening within the database environment.

In order to determine if there might be an event, that can provide the data for that one “thing” that may be happening within your environment, we could start by querying the SQL Server Internals.  This next query will do just that for us.

SELECT c.object_name AS EventName
,p.name AS PackageName
,o.description AS EventDescription
FROM sys.dm_xe_objects o
INNER JOIN sys.dm_xe_object_columns c 
ON o.name = c.object_name
and o.package_guid = c.object_package_guid
INNER JOIN sys.dm_xe_packages p
ON o.package_guid = p.guid
WHERE object_type='event'
AND c.name = 'channel'
AND (c.object_name like '%file_size%'
or c.object_name like '%growth%')
ORDER BY o.package_guid;

This will query the DMVs related to Extended Events and provide us with the event names that contain the terms used in the filter / predicate.  In this case, I am looking for any events that contain the terms “growth” or “file_size”.  This is in an effort to try and find anything that might be related to database files changing sizes.  You may have seen one of the previous articles on tracking file size changes here or here.

The use of the right term to try and trap the right data for the problem that is happening can greatly decrease the time required to find the correct event.  There may be a bit of an art involved in finding the correct search term.  A little practice can help improve the ability to find the appropriate event more quickly.

Is the Data any Good?

Knowing if an Extended Event exists for the desired problem is one thing.  Knowing what data can be parsed from that event is another thing.  Many times, we may not know what kind of data can be trapped with each of the events.  Many times, we may just not be trapping enough data.  And of course, knowing what kind of data can be trapped by the event may help us in filtering that data down to what is usable for efficient troubleshooting.

Finding that data is just a matter of another simple query against the DMVs.  Let’s take a look at the query that can help us find the data we need:

DECLARE @EventName VARCHAR(64) = 'trace_flag_changed'
,@ReadFlag VARCHAR(64) = 'readonly' --NULL if all columntypes are desired
SELECT oc.object_name as EventName
,oc.name AS column_name, oc.type_name,
oc.column_type AS column_type,
oc.column_value AS column_value,
oc.description AS column_description
FROM sys.dm_xe_objects AS o
INNER JOIN sys.dm_xe_object_columns AS oc
ON o.name = oc.object_name
AND o.package_guid = oc.object_package_guid
WHERE o.name = @EventName
AND (oc.capabilities IS NULL 
OR oc.capabilities & 1 = 0)
AND oc.column_type <> @ReadFlag
   ;
GO

In this case, I have taken the name of the event about which I want to learn more details.  In addition, I only want to retrieve the data columns that retrieve data that could change.  As you can see, I am investigating “trace_flag_changed”.  You may recall that particular event from a previous article and you can read more about it here.

You may also be looking at the query and thinking it seems a little overly complex for what we want.  Well, it is.  Here is an alternate version:

DECLARE @EventName VARCHAR(64) = 'trace_flag_changed'
,@ReadFlag VARCHAR(64) = 'readonly' --NULL if all columntypes are desired
SELECT oc.object_name as EventName
,oc.name AS column_name, oc.type_name
,oc.column_type AS column_type
,oc.column_value AS column_value
,oc.description AS column_description 
FROM sys.dm_xe_object_columns oc
WHERE oc.object_name = @EventName
AND oc.column_type <> @ReadFlag
;

The discovery of the columns available in each event will give us some options to filter as well as some options in what kind of data we parse from the resulting event.

Did I Already Create that Session?

This is arguably less critical than the others.  After all, some will just drop and recreate the session.  But why drop the session if you don’t need to?  On the other hand, a quick glimpse in the GUI could also display that information.  Sadly that doesn’t work for all versions of SQL Server (e.g. SQL Server 2008).  Maybe you have the session running, or maybe it is simply created and not in the running state.

Finding these sessions and whether they are running or not is rather simple.  Just run this next query and it will help you figure out if the session is there or not.  All you need to do is plug in the name of the session and away you go.

DECLARE @SessionName VARCHAR(64) = 'TrackDBFileChange';
SELECT sn.SessionName
, CASE WHEN ISNULL(es.name,'No') = 'No'
THEN 'NO'
ELSE 'YES'
End as XESessionExists
, CASE WHEN ISNULL(xe.name,'No') = 'No'
THEN 'NO'
ELSE 'YES'
End as XESessionRunning
FROM (Select @SessionName as SessionName) sn
LEFT OUTER JOIN sys.server_event_sessions es
ON sn.SessionName = es.name
LEFT OUTER JOIN sys.dm_xe_sessions xe
ON es.name = xe.name
;

This query has been confirmed to work in SQL 2008 and up through SQL 2014.  It should simplify some of the attempts to better understand what is happening with any sessions that might be out there.

These are a few quick and simple queries to help you along your way to becoming better acquainted with Extended Events.  I hope these will be useful to you and that you will get to know and use extended events regularly.

Update: This post is being added to the 60 day series which can be found here. While in here fixing a formatting issue that was discovered, it made sense to also include it in the ongoing library of articles on Extended Events.