Another Query to Help Find the Right Event

Finding the right event or combination of events to monitor may seem like quite a daunting task with so many events to explore and (frequently) too little familiarity with Extended Events. In this follow-up article to Extended Event Help Queries, I will share another means to quickly explore the Extended Events metadata in the effort to find the precise event to fit your needs.

Today I will be sharing a new query or two that I have used on more than one occasion to help track down the event(s) that I wanted to, at least, try while troubleshooting specific problems within the SQL Server Instance. I found these queries useful once again in a recent opportunity to help out some friends from the SQL Community. (Maybe, just maybe, these are really just a spin of other previously shared queries.) I will share the most recent experience in another follow-up post.

For the sake of posterity, I am also adding this to the 60 Days of Extended Events Table of Contents.

Is There an Event that Contains pertinent Data?

In my previous article I demonstrated how to find an event based solely on the name or description of the event. This is fantastic if the event name (or description) contains one of the magical words you have used. What if the event name or description has nothing to do with the terms you selected? Or, what if the data you seek may be attached to the event but wouldn’t necessarily stand out as a description for that event (by name or description details for that event)?

Now comes the more difficult task right? If the name or description of the event doesn’t relate to the search terms then you just might overlook a few events and be stuck trying to troubleshoot a problem. An equally big problem this could cause is yet another invisible barrier to using Extended Events. It would be easy to slide down the slippery slope and not transition to Extended Events just because an event, applicable to the problem at hand, could not be found.

This is where searching on the payload comes in handy. Remember from a previous article that the payload is the event data that is attached to each event. Imagine how much more accurate we can become by extending our search from the name and description of the event object to next search on the payload names and descriptions? That is exactly what I will now show.

DECLARE @TermDescription VARCHAR(64) = 'Select'
, @ColumnDesc VARCHAR(64) = 'statement'
, @ReadFlag VARCHAR(64) = NULL; --readonly' --ALL if all columntypes are desired --data --customizable
 
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
, ca.map_value AS SearchKeyword
, oc.column_type
FROM sys.dm_xe_object_columns oc
CROSS APPLY ( SELECT mv.map_value
FROM sys.dm_xe_object_columns occ
INNER JOIN sys.dm_xe_map_values mv
ON occ.type_name = mv.name
AND occ.column_value = mv.map_key
AND occ.object_package_guid = mv.object_package_guid
WHERE occ.name = 'KEYWORD'
AND occ.object_name = oc.object_name
) ca
WHERE (oc.object_name = @TermDescription
OR oc.description LIKE '%' + ISNULL(@ColumnDesc,@TermDescription) + '%')
AND oc.column_type = ISNULL(@ReadFlag, oc.column_type)
ORDER BY EventName, column_name;
GO

Basically, I am searching against the object_columns metadata dmv for Extended Events just to find any columns that match my theoretical needs. In this case, I am trying to find anything that may help me find events related to the terms “select” or “statement”. When run, there will be quite a few results for these terms. That may actually be a bit of a problem (just the same as if nothing were returned in the results). There is a fix for that (well probably several fixes as you begin to tweak the query).

One possible fix is to also include map_values to refine the search. Another fix might be to change the search terms. And still other fixes might include tweaking the predicate. For today, I am going to demonstrate the inclusion of map_values as a secondary tweak to help isolate the needed event.

DECLARE @TermDescription VARCHAR(64) = 'Select'
, @ColumnDesc VARCHAR(64) = 'statement'
, @ReadFlag VARCHAR(64) = NULL; --readonly' --ALL if all columntypes are desired --data --customizable
 
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
, ca.map_value AS SearchKeyword
, mv.map_value AS MapValue
, mv.map_key AS MapKey
, oc.column_type
FROM sys.dm_xe_object_columns oc
INNER JOIN sys.dm_xe_map_values mv
ON oc.type_name = mv.name
AND mv.object_package_guid = oc.object_package_guid
CROSS APPLY ( SELECT mv.map_value
FROM sys.dm_xe_object_columns occ
INNER JOIN sys.dm_xe_map_values mv
ON occ.type_name = mv.name
AND occ.column_value = mv.map_key
AND occ.object_package_guid = mv.object_package_guid
WHERE occ.name = 'KEYWORD'
AND occ.object_name = oc.object_name
) ca
WHERE (oc.object_name = @TermDescription
OR oc.description LIKE '%' + ISNULL(@ColumnDesc,@TermDescription) + '%')
AND mv.map_value = @TermDescription
AND oc.column_type = ISNULL(@ReadFlag, oc.column_type);

In this iteration, my result-set is trimmed significantly due to the predicate and the requirement for a match based on the map_value name. Ultimately, this trimmed my results down to precisely the events that I needed in this case. With this slightly more advanced version, I have greater visibility into the Extended Event realm to help me find just the right event to match my needs.

Are there other Events related to the new found Event?

This is some pretty awesome stuff so far. But it doesn’t end right there. Many events are tied together to help provide a more holistic view of the problem. These events are tied together via keyword. You can read more about keywords here. By searching for events based on a keyword, we can get a lot more intelligent about the sessions we create with Extended Events.

Taking the single result from the last query and then using the SearchKeyword value, for that event, in this next query – I can potentially go from a Great DBA to a Super DBA.

DECLARE @Keyword VARCHAR(64) = 'execution' --'synchronization'
 
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
,REVERSE(LEFT(REVERSE(olm.name),CHARINDEX('',REVERSE(olm.name))-1)) AS DLLName
,olm.file_version
,xp.name AS PackageName
,mv.map_value AS SearchKeyword
,ch.Channel
FROM sys.dm_xe_object_columns oc
INNER JOIN sys.dm_xe_map_values mv
ON oc.type_name = mv.name
AND oc.column_value = mv.map_key
AND oc.name = 'KEYWORD'
INNER JOIN sys.dm_xe_packages xp
ON oc.object_package_guid = xp.guid
INNER JOIN sys.dm_os_loaded_modules olm
ON xp.module_address = olm.base_address
INNER JOIN (SELECT c.object_name AS EventName,c.object_package_guid AS PkgGuid, v.map_value AS Channel
FROM sys.dm_xe_object_columns c
INNER JOIN sys.dm_xe_map_values v
ON c.type_name = v.name
AND c.column_value = CAST(v.map_key AS NVARCHAR)
WHERE c.name = 'channel') ch
ON ch.EventName = oc.object_name
AND ch.PkgGuid = oc.object_package_guid
WHERE mv.map_value = @Keyword
ORDER BY EventName
;
GO

I intentionally included the columns from each of the matching events. Doing this, I can also see what potential payload may be trapped when I attempt to trap any of the events from this result set.

Conclusion

There is great power in Extended Events. There is more power in being able to query the metadata for Extended Events in order to trap the appropriate information to properly troubleshoot a problem.

Have fun with these scripts and I hope you enjoy.