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 Events Help Queries, I will share another means to quickly explore the Extended Events metadata for Azure SQL in the effort to find the precise event to fit your needs.
In this article, 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 MASSIVE collection of Extended Events articles.
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.
Amazing Query
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.
Another Killer Query
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 Events 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 ,xp.name AS PackageName ,mv.map_value AS SearchKeyword ,ch.Channel ,xp.module_address 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 (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.
Put a bow on it
Extended Events (XEvents) is here to stay. Not only is it here to stay, it is a powerful tool for ALL of your SQL implementations – whether they be Azure SQL or the traditional on-premises SQL Server installations. This article demonstrated three quick scripts to help you learn more about XEvents in a short time so you can be a SuperStar DBA.
Interested in learning more deep technical information? Check these out!
Want to learn more about your indexes? Try this index maintenance article or this index size article.
This is the twelfth article in the 2020 “12 Days of Christmas” series. For the full list of articles, please visit this page.
The post 3 Highly Improved Help Queries for XEvents in Azure SQL first appeared on SQL RNNR.