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.

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.

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';

Keywords

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.