SQL SERVER – To Find Events Mapped to Channels in Extended Events

I have outlined a number of blogs here that point to the basics of working with Extended Events. Some of them also detail you some specific scenarios that you can use inside your environments.

As I wrote them and used them in a number of external facing conferences, guess what – a number of you asked me during the sessions and via emails to the advantage of using the same. I used to give a standard definition and say it is quite exhaustive when compared to using SQL Server Profiler. Though this was subjective, I needed a way to quantify and this question did come from one DBA during a conversation – how exhaustive is this?

In other words, he wanted to know what are the exhaustive list of events we can collect with extended events and across the channels. Unlike the standalone tool (SQL Server Profiler) in previous releases, starting with SQL Server 2012, the UI to manage and view extended events sessions is built into SQL Server Management Studio. There is a new node in the Management tree in Object Explorer called Extended Events. Use this node to graphically create or modify a session, start or stop session, view data captured by sessions live as well as script the session definition.

What is Channel?

A channel identifies the audience of an event. Events are categorized into these different buckets. The available channel types are:

Admin Admin events are primarily targeted to the end users, administrators, and support. The events that are found in the admin channels indicate a problem with a well-defined solution that an administrator can act on.
Operational Operational events are used for analyzing and diagnosing a problem or occurrence. They can be used to trigger tools or tasks based on the problem or occurrence.
Analytic Analytic events are published in high volume. They describe program operation and are typically used in performance investigations.
Debug Debug events are used solely by developers to diagnose a problem for debugging.

Now that we have put the foundation in each of the channels, Use the following query to get a list of all events and the channel to which they belong:

SELECT p.name AS package, c.event, k.keyword, c.channel, c.description
SELECT event_package=o.package_guid, o.description,
event=c.OBJECT_NAME, channel=v.map_value
FROM sys.dm_xe_objects o
LEFT JOIN sys.dm_xe_object_columns c ON o.name = c.OBJECT_NAME
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 object_type='event' AND (c.name = 'channel' OR c.name IS NULL)
SELECT event_package=c.object_package_guid, event=c.OBJECT_NAME,
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 = v.map_key
AND c.type_package_guid = v.object_package_guid
INNER JOIN sys.dm_xe_objects o ON o.name = c.OBJECT_NAME
AND o.package_guid=c.object_package_guid
WHERE object_type='event' AND c.name = 'keyword'
) k
k.event_package = c.event_package AND (k.event = c.event OR k.event IS NULL)
INNER JOIN sys.dm_xe_packages p
ON p.guid=c.event_package
WHERE (p.capabilities IS NULL OR p.capabilities&1 = 0)
ORDER BY channel, keyword, event

On a SQL Server 2016 instance I can see more than 1050+ events getting listed. I am sure this is more than what someone can chew.

SQL SERVER - To Find Events Mapped to Channels in Extended Events extended_channel_01

At a high level the number of events associated with each of the Channels are as shown above.

Do let me know if you have moved away from Profiler and started adopting Extended Events inside your environments? What are some of the typical usecases in your environments? Drop a line as comments as it would be useful for others too.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Exit mobile version