Top 3 Easy Help Queries for XEvents in Azure SQL

When working with Extended Events (XEvents), there are times when a little more information is, well, helpful. You know you want to use XEvents 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 payload is captured/delivered 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 Management Studio 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 the event information for SQL Azure when attempting to use XEvents. In the long run, these little helper queries will help make your life easier.

Does an Event Exist?

First let’s tackle the problem of discovery. When we want to use XEvents to try and troubleshoot a problem (or to capture more information) in SQL Azure, it is really good to know if a relevant event exists. There are many events that capture data for various different things within SQL Server. There are far fewer events available in SQL Azure (that is the subject of a different article).

That said, more events are being added on a regular basis. 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 XEvent 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 '%index%'
or c.object_name like '%shrink%')
ORDER BY o.package_guid;

Not much Difference yet…

Of important note here is that so far, the DMVs to query SQL Azure XEvents is just the same as with on-prem SQL Server. The preceding query 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 “index” or “shrink”.

This is in an effort to try and find a wide swath of events that could help troubleshoot sudden performance impacts. You may have seen one of the previous articles on tracking shrink events for SQL Server (not Azure SQL) here. In addition, you may have seen an article about trying to monitor index changes in SQL Server (not Azure SQL) 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 Events 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. Often 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. Unfortunately, this particular event just doesn’t exist in SQL Azure so we will have to test with a different event name such as create_index_event.

Easier Query

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) = 'create_index_event'
,@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. Looking at this particular event, I can expect the following payload to be available.

Note here that in Azure SQL, we have a significant amount of data available for troubleshooting via this event just the same as this event provides for SQL Server. The slim difference may be the “is_in_remote_storage” attribute., which became available with SQL Server 2019.

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. 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.database_event_sessions es
ON sn.SessionName = es.name
LEFT OUTER JOIN sys.dm_xe_database_sessions xe
ON es.name = xe.name
;

This query illustrates where XEvents in Azure SQL starts to diverge from XEvents in SQL Server. This query will not run against your on-premises version of SQL Server due to the change in DMVs needed for Azure SQL. The most notable change comes in the addition of the term “database” to some of the DMV names. Naming scheme aside, it will simplify some of the attempts to better understand what is happening with any sessions that might be out there.

Put a bow on it

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