Extended Events is a powerful feature in SQL Server that allows database administrators and developers to capture and analyze events that occur within the database engine. In this article, we will explore the basics of working with Extended Events and discuss some common use cases.

What are Extended Events?

Extended Events is a lightweight and highly scalable event-handling system built into SQL Server. It provides a flexible framework for capturing and analyzing events that occur within the database engine, such as queries, errors, and performance metrics.

Advantages of Extended Events

Compared to its predecessor, SQL Server Profiler, Extended Events offers several advantages:

• Improved performance: Extended Events has a minimal impact on server performance, making it suitable for capturing events in production environments.
• Rich event filtering: Extended Events allows you to filter events based on various criteria, such as event type, duration, and database name.
• Integration with SQL Server Management Studio: Starting from SQL Server 2012, the UI to manage and view Extended Events sessions is built into SQL Server Management Studio, making it easier to create, modify, and monitor sessions.
• Extensive event coverage: SQL Server provides over 1050 events that can be captured using Extended Events, covering a wide range of scenarios and use cases.

Working with Channels

In Extended Events, events are categorized into different channels, each targeting a specific audience or purpose. The available channel types are:

• Admin: Admin events are primarily targeted to end users, administrators, and support. They indicate problems with well-defined solutions that administrators can act on.
• Operational: Operational events are used for analyzing and diagnosing problems or occurrences. They can trigger tools or tasks based on the problem or occurrence.
• Analytic: Analytic events are published in high volume and describe program operation. They are typically used in performance investigations.
• Debug: Debug events are used solely by developers to diagnose problems for debugging purposes.

Getting Started with Extended Events

To get started with Extended Events, you can 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, you can see more than 1050+ events listed. However, it’s important to note that not all events may be relevant to your specific use case. It’s recommended to focus on events that align with your monitoring and troubleshooting needs.

Conclusion

Extended Events is a powerful tool for capturing and analyzing events in SQL Server. By leveraging its capabilities, database administrators and developers can gain valuable insights into the performance, behavior, and issues within their database environments. If you haven’t already, consider exploring Extended Events as an alternative to SQL Server Profiler and share your experiences and use cases with the community.

Have you started using Extended Events in your environments? What are some of the typical use cases you have encountered? Share your thoughts and experiences in the comments below!