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.

• Identify Page Splits Using Extended Events in SQL Server
• Knowing Deprecated or Discontinued Features Using Extended Events
• Filtering CPU Bound Execution Plans with Extended Events
• Introduction to Extended Events – Finding Long Running Queries

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:

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
FROM
(
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)
) c LEFT JOIN
(
SELECT event_package=c.object_package_guid, event=c.OBJECT_NAME,
keyword=v.map_value
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
ON
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.

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)