Extended Events overview
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
The Extended Events architecture enables users to collect as much or as little data as is necessary to troubleshoot or identify a performance problem in SQL Server, Azure SQL Database, and Azure SQL Managed Instance. Extended Events is highly configurable, lightweight, and scales very well. For more information, see Extended Events Architecture.
Extended Events replace the deprecated SQL Trace and SQL Server Profiler features.
Give XEvents a try: Quickstart: Extended Events in SQL Server.
Note
Azure SQL Database supports only database-scoped sessions. Learn how Code examples can differ for Azure SQL Database and SQL Managed Instance and more about Extended events in Azure SQL Database.
Benefits of SQL Server Extended Events
Extended Events is a lightweight performance monitoring system that uses minimal performance resources. SQL Server Management Studio provides a graphical user interface for Extended Events to create and modify sessions and display and analyze session data. Here you can find out more about those extensions:
Extended Events concepts
SQL Server Extended Events builds on existing concepts, such as an event or an event consumer, uses concepts from Event Tracing for Windows, and introduces new concepts.
The following table describes the concepts in Extended Events.
Topic | Description |
---|---|
SQL Server Extended Events Packages | Describes the Extended Events packages that contain objects. These objects are used to obtain and process data when an Extended Events session is running. |
SQL Server Extended Events Targets | Describes the event consumers that can receive data during an event session. |
SQL Server Extended Events Engine | Describes the engine that implements and manages an Extended Events session. |
SQL Server Extended Events Sessions | Describes the Extended Events session. |
Extended Events architecture
Extended Events is our name for a general event-handling system for server systems. The Extended Events infrastructure supports the correlation of data from SQL Server, and under certain conditions, the correlation of data from the operating system and database applications. In the operating system case, Extended Events output must be directed to Event Tracing for Windows (ETW). ETW can correlate the event data with operating system or application event data.
All applications have execution points that are useful both inside and outside an application. Inside the application, asynchronous processing may be enqueued using information that is collected during the initial execution of a task. Outside the application, execution points provide monitoring utilities with information. The information is about the behavioral and performance characteristics of the monitored application.
Extended Events supports using event data outside a process. This data is typically used by users either administering or supporting a product by doing performance monitoring or by user developing applications on a product for debugging purposes. Data is consumed or analyzed using tools such as XEvent Profiler and Performance Monitor, T-SQL or Windows command line tools.
Extended Events has the following key design aspects:
The Extended Events engine is event agnostic. The engine can bind any event to any target, because the engine is not constrained by event content. For more information about the Extended Events engine, see SQL Server Extended Events Engine.
Events are separated from event consumers, which are called targets in Extended Events. This means that any target can receive any event. In addition, any event that is raised can be automatically consumed by the target, which can log or provide additional event context. For more information, see SQL Server Extended Events Targets.
Events are distinct from the action to take when an event occurs. Therefore, any action can be associated with any event.
Predicates can dynamically filter when event data should be captured. Dynamic filtering adds to the flexibility of the Extended Events infrastructure. For more information, see SQL Server Extended Events Packages.
Extended Events can synchronously generate event data (and asynchronously process that data) which provides a flexible solution for event handling. In addition, Extended Events provides the following features:
A unified approach to handling events across the server system, while enabling users to isolate specific events for troubleshooting purposes.
Integration with, and support for existing ETW tools.
A fully configurable event handling mechanism that is based on Transact-SQL.
The ability to dynamically monitor active processes, while having minimal effect on those processes.
A default system health session that runs without any noticeable performance effects. The session collects system data that you can use to help troubleshoot performance issues. For more information, see Use the system_health Session.
Extended Events tasks
Using Management Studio or Transact-SQL to execute Transact-SQL Data Definition Language (DDL) statements, consume dynamic management views and functions, or catalog views, you can create simple or complex SQL Server Extended Events troubleshooting solutions for your SQL Server environment.
Task Description | Article |
---|---|
Use the Object Explorer to manage event sessions. | Manage Event Sessions in the Object Explorer |
Describes how to create an Extended Events session. | Create an Extended Events Session |
Describes how to view and refresh target data. | Advanced Viewing of Target Data from Extended Events in SQL Server |
Describes how to use Extended Events tools to create and manage your SQL Server Extended Events sessions. | Extended Events Tools |
Describes how to alter an Extended Events session. | Alter an Extended Events Session |
Describes how to get information about the fields associated with the events. | Get the Fields for All Events |
Describes how to find out what events are available in the registered packages. | View the Events for Registered Packages |
Describes how to determine what Extended Events targets are available in the registered packages. | View the Extended Events Targets for Registered Packages |
Describes how to view the Extended Events events and actions that are equivalent to each SQL Trace event and its associated columns. | View the Extended Events Equivalents to SQL Trace Event Classes |
Describes how to find the parameters you can set when you use the ADD TARGET argument in CREATE EVENT SESSION or ALTER EVENT SESSION. | Get the Configurable Parameters for the ADD TARGET Argument |
Describes how to convert an existing SQL Trace script to an Extended Events session. | Convert an Existing SQL Trace Script to an Extended Events Session |
Describes how to determine which queries are holding the lock, the plan of the query, and the Transact-SQL stack at the time the lock was taken. | Determine Which Queries Are Holding Locks |
Describes how to identify the source of locks that are hindering database performance. | Find the Objects That Have the Most Locks Taken on Them |
Describes how to use Extended Events with Event Tracing for Windows to monitor system activity. | Monitor System Activity Using Extended Events |
Using the Catalog views and the Dynamic management views (DMVs) for Extended Events | SELECTs and JOINs From System Views for Extended Events in SQL Server |
Use the following Transact-SQL (T-SQL) query to list out all possible events and their descriptions:
SELECT
obj1.name as [XEvent-name],
col2.name as [XEvent-column],
obj1.description as [Descr-name],
col2.description as [Descr-column]
FROM
sys.dm_xe_objects as obj1
JOIN sys.dm_xe_object_columns as col2 on col2.object_name = obj1.name
ORDER BY
obj1.name,
col2.name
Code examples can differ for Azure SQL Database and SQL Managed Instance
Some Transact-SQL code examples written for SQL Server on-premises need small changes to run in the cloud. One category of such code examples involves system views whose name prefixes differ slightly between the two database systems:
- server_ - prefix for SQL Server and Azure SQL Managed Instance
- database_ - prefix for Azure SQL Database and SQL Managed Instance
Azure SQL Database supports only database-scoped sessions. SQL Server Management Studio (SSMS) fully supports database-scoped sessions for Azure SQL Database: an Extended Events node containing database-scoped sessions appears under each database in Object Explorer.
Azure SQL Managed Instance supports both database-scoped sessions and server-scoped sessions. Server-scoped sessions are recommended for managed instances. SSMS fully supports server-scoped sessions for SQL Managed Instance: an Extended Events node containing all server-scoped sessions appears under the Management folder for each managed instance in Object Explorer.
Note
Database-scoped sessions are not displayed in Object Explorer in SSMS for Azure SQL Managed Instance. Database-scoped sessions may only be queried and managed with Transact-SQL when using a managed instance.
For illustration, the following table lists and compares two subsets of the system views. For brevity, the subsets are restricted to view names that also contain the string _event
. The subsets have differing name prefixes because they come from two different database systems.
Name from SQL Server | Name from cloud service |
---|---|
server_event_notifications server_event_session_actions server_event_session_events server_event_session_fields server_event_session_targets server_event_sessions server_events server_trigger_events |
database_event_session_actions database_event_session_events database_event_session_fields database_event_session_targets database_event_sessions |
The two lists in the preceding table were accurate as of March 2022. For an accurate list, run the following Transact-SQL SELECT statement:
SELECT name
FROM sys.all_objects
WHERE
(name LIKE 'database\_%' { ESCAPE '\' } OR
name LIKE 'server\_%' { ESCAPE '\' })
AND name LIKE '%\_event%' { ESCAPE '\' }
AND type = 'V'
ORDER BY name;
See also
- Extended Events Dynamic Management Views
- Extended Events Catalog Views (Transact-SQL)
- SQL Mysteries: Causality tracking vs Event Sequence for XEvent Sessions
- Analyze and prevent deadlocks in Azure SQL Database
Next steps
- Quickstart: Extended Events in SQL Server
- Event File target code for Extended Events in Azure SQL Database
- Extended events in Azure SQL Database
- XELite: Cross-platform library to read XEvents from XEL files or live SQL streams, released May 2019.
- Read-SQLXEvent PowerShell cmdlet, released June 2019.