I recently delved into a brief explanation of Packages as they correlate to Extended Events. In that article, I left a couple of things un-said about packages – leaving them for this article.
Packages have a couple of descriptors (or classifiers) that can be useful to help figure out what events contained within that package might be related to other events. This would be particularly helpful in putting together a session to troubleshoot a specific issues.
The classifiers within packages are called keywords and channels.
A channel in Extended Events can be very closely defined just the same as a radio station or a TV channel. The channel indicates a stream of interest or in other words it identifies the audience of interest to the event.
|Admin||Events that are targeted to the administrators and support. These events indicate a problem with a solution that an administrator can act on. These events typically have a message associated with them telling the consumer what to do to resolve the problem.|
|Operational||Events that are used for analyzing and diagnosing a problem. One may use these to trigger tasks based on the occurrence. Think Administrator when considering who the consumer might be.|
|Analytic||Use these in performance investigations. These are high volume type of events. Think of anybody working to resolve performance issues as the consumers of these types of events.|
|Debug||Debug events are used solely by developers to diagnose a problem for debugging. Think CSS when dealing with these.|
Depending on the day or the issue, your interest point may be in a different Channel. Keep that in mind as we progress through event classification.
You can find these channels within the XEvent metadata by performing a query similar to the following:
SELECT DISTINCT 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';
The term “keywords” should invoke a common meaning for most people. If you perform internet searches, you should be familiar with what a keyword would be in “Google” or “Bing.” It is a search phrase that would yield a group of results that are related to the term that describes the problem you are researching.
Within Extended Events, the Keyword is a fine-grained classification of the events. When used in conjunction with the Channel, it becomes more refined and as a result a better classification of the events being sought.
To discover these keywords, one could execute the following query:
SELECT DISTINCT map_value AS Keyword FROM sys.dm_xe_map_values mv WHERE mv.name = 'keyword_map' ORDER BY mv.map_value;
Within SQL Server 2014, that will yield 53 unique keywords. That doesn’t tell the entire story though. Notice the emphasis on unique. A keyword may exist in more than one package and be applicable to more than one Channel.
To see this a little more clearly, we could do something like this:
SELECT map_value AS Keyword , xp.name AS Package FROM sys.dm_xe_map_values mv INNER JOIN sys.dm_xe_packages xp ON mv.object_package_guid = xp.guid WHERE mv.name = 'keyword_map' ORDER BY mv.map_value,xp.name;
Notice the highlighted results? We see that query_store (even in SQL Server 2014) has a few results as a keyword. There is even what appears to be a duplication within the sqlserver package for that keyword. Well, if we explore that a bit further and add the Channel, we should see more clarity.
SELECT DISTINCT ke.KeyWord AS Keyword , xp.name AS Package ,ch.Channel AS Channel FROM sys.dm_xe_packages xp INNER JOIN (SELECT oc.object_package_guid, v.map_value AS Channel ,xo.name AS EventName FROM sys.dm_xe_object_columns oc INNER JOIN sys.dm_xe_map_values v ON oc.type_name = v.name AND oc.column_value = CONVERT(NVARCHAR,v.map_key) INNER JOIN sys.dm_xe_objects xo ON oc.object_package_guid = xo.package_guid AND oc.object_name = xo.name WHERE oc.name = 'channel' AND xo.object_type = 'event' ) ch ON ch.object_package_guid = xp.guid INNER JOIN (SELECT xoc.object_package_guid, mv.map_value AS KeyWord ,xo.name AS EventName FROM sys.dm_xe_map_values mv INNER JOIN sys.dm_xe_object_columns xoc ON xoc.type_name = mv.name AND xoc.column_value = CONVERT(NVARCHAR,mv.map_key) AND xoc.type_package_guid = mv.object_package_guid INNER JOIN sys.dm_xe_objects xo ON xo.package_guid = xoc.object_package_guid AND xo.name = xoc.object_name WHERE mv.name = 'keyword_map' AND xo.object_type = 'event' ) ke ON ke.object_package_guid = xp.guid AND ch.EventName = ke.EventName ORDER BY ke.KeyWord,xp.name;
This would reveal to us the following:
Taking note of the highlighted rows again, one can see that the query_store keyword affects multiple Channels along with the multiple packages. This will be useful after we start to learn more about events. Once we do that, we can learn to correlate the events to these channels and keywords.
There are many useful keywords coupled with the four channels. We can see through various queries that the keywords are reused between packages and channels. These keywords and channels offer a granularity in helping to search for events and group events into common themes.
Other than the usefulness in searching for like events, why do we have this kind of granularity? Well, as it turns out, this was done to have Extended Events follow the same setup seen in Event Tracing for Windows (ETW). This also allows an integration between Extended Events in SQL Server and the tools available for ETW. Think of it as one step in an effort to bring sysadmins, developers and DBAs to a standard means of monitoring and tracing on the Windows platform. You want to be able to work efficiently with the sysadmins and developers, right?
Stay tuned for the next article where I will discuss events.