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:
|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 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)