Edit

Share via

Extended Events overview

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

The Extended Events (XEvents) architecture enables users to collect as much or as little data as is necessary to monitor, identify, or troubleshoot performance in SQL Server, Azure SQL Database, Azure SQL Managed Instance, and SQL database in Fabric. Extended Events is highly configurable, lightweight, and scales well. For more information, see Extended Events Architecture.

Extended Events replace the deprecated SQL Trace and SQL Server Profiler features.

To get started with Extended Events, use Quickstart: Extended Events.

Note

For Azure SQL Database, SQL database in Fabric, and SQL Managed Instance, code examples can differ because the files for the event_file target are stored in Azure Storage. For more information, see Extended Events in Azure SQL.

Benefits of Extended Events

Extended Events is a lightweight performance monitoring system that uses minimal system resources while providing a detailed, in-depth view of the database engine. SQL Server Management Studio provides a graphical user interface for Extended Events to create, modify, and drop event sessions and to display and analyze session data. To learn more about Extended Events support in Management Studio, see:

Extended Events concepts

Extended Events builds on the existing concepts from Event Tracing for Windows (ETW), such as event and event consumer, and introduces new concepts such as action and predicate.

The following table provides documentation references to understand the concepts in Extended Events.

Article Description
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.
Extended Events targets Describes the event consumers that can receive data during an event session.
Extended Events engine Describes the engine that implements and manages an Extended Events session.
Extended Events sessions Describes the Extended Events session.

Extended Events architecture

Extended Events is a name for a general event-handling system for server systems. The Extended Events infrastructure supports the correlation of data from the database engine, 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 can 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 SQL Server Management Studio, XEvent Profiler and Performance Monitor, and 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 isn't constrained by event content. For more information about the Extended Events engine, see 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 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 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 uses 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 use available Extended Events targets. Extended Events targets
Describes how to view and refresh target data. View event data in SQL Server Management Studio
Describes the architecture of Extended Events sessions. Extended Events sessions
Describes how to use Extended Events tools to create and manage your 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. SELECTs and JOINs From System Views for Extended Events
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 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. 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 Catalog Views and Dynamic Management Views (DMVs) for Extended Events SELECTs and JOINs From System Views for Extended Events

Extended Events catalog views

Extended Events provides several catalog views. Catalog views tell you about event session metadata or definition. For information about instances of active event sessions, see Extended Events dynamic management views.

Name of catalog view Description
sys.database_event_session_actions Returns a row for each action on each event of a database-scoped event session.
sys.database_event_session_events Returns a row for each event in a database-scoped event session.
sys.database_event_session_fields Returns a row for each customizable column that was explicitly set on events and targets of a database-scoped session.
sys.database_event_session_targets Returns a row for each event target for a database-scoped event session.
sys.database_event_sessions Returns a row for each database-scoped event session.

Extended Events dynamic management views

Extended Events provides several dynamic management views (DMVs). DMVs return information about active (started) event sessions, such as session and target statistics.

Name of DMV Description
sys.dm_xe_database_session_event_actions Returns information about database-scoped event session actions.
sys.dm_xe_database_session_events Returns information about database-scoped event session events.
sys.dm_xe_database_session_object_columns Shows the configuration values for objects that are bound to a database-scoped session.
sys.dm_xe_database_session_targets Returns information about database-scoped event session targets.
sys.dm_xe_database_sessions Returns a row for each database-scoped event session running in the current database.

Permissions

In Azure SQL Database, SQL database in Fabric, Azure SQL Managed Instance, and in SQL Server 2022 and later versions, Extended Events supports a granular permission model. The following permissions can be granted:

CREATE ANY DATABASE EVENT SESSION
DROP ANY DATABASE EVENT SESSION
ALTER ANY DATABASE EVENT SESSION
ALTER ANY DATABASE EVENT SESSION ADD EVENT
ALTER ANY DATABASE EVENT SESSION DROP EVENT
ALTER ANY DATABASE EVENT SESSION ADD TARGET
ALTER ANY DATABASE EVENT SESSION DROP TARGET
ALTER ANY DATABASE EVENT SESSION ENABLE
ALTER ANY DATABASE EVENT SESSION DISABLE
ALTER ANY DATABASE EVENT SESSION OPTION

For information on what each of these permissions controls, see CREATE EVENT SESSION, ALTER EVENT SESSION, and DROP EVENT SESSION.

All of these permissions are included in the CONTROL permission on the database, SQL managed instance, or SQL Server instance. In Azure SQL Database, the database owner (dbo), members of the db_owner database role, and the administrators of the logical server hold the database CONTROL permission. In Azure SQL Managed Instance and in SQL Server, members of the sysadmin server role hold the CONTROL permission on the instance.

Code examples can differ for Azure SQL Database, SQL database in Fabric, and SQL Managed Instance

Some Transact-SQL code examples written for SQL Server need small changes to run in Azure SQL Database or SQL database in Fabric. One category of such code examples involves catalog views whose name prefixes differ depending on the database engine type:

  • server_ - prefix for SQL Server and Azure SQL Managed Instance
  • database_ - prefix for Azure SQL Database, SQL database in Fabric, and SQL Managed Instance

Azure SQL Database and SQL database in Fabric support only database-scoped event sessions. SQL Server Management Studio (SSMS) supports database-scoped event 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. 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

Server-scoped event sessions are recommended for Azure SQL Managed Instance.

Database-scoped event sessions aren't displayed in Object Explorer in SSMS for Azure SQL Managed Instance. On a SQL managed instance, database-scoped event sessions can only be queried and managed with Transact-SQL.

For illustration, the following table lists and compares two subsets of catalog views. The subsets have differing name prefixes because they support different database engine types.

Name in SQL Server and Azure SQL Managed Instance Name in Azure SQL Database, SQL database in Fabric, and Azure SQL Managed Instance
sys.server_event_session_actions
sys.server_event_session_events
sys.server_event_session_fields
sys.server_event_session_targets
sys.server_event_sessions
 sys.database_event_session_actions
sys.database_event_session_events
sys.database_event_session_fields
sys.database_event_session_targets
sys.database_event_sessions

Related content

  • Last updated on