Extended Events in Azure SQL Database and Azure SQL Managed Instance
Applies to: Azure SQL Database Azure SQL Managed Instance
For an introduction to Extended Events, see:
The feature set, functionality, and usage scenarios for Extended Events in Azure SQL Database and Azure SQL Managed Instance are similar to what is available in SQL Server. The main differences are:
- The
event_file
target always uses blobs in Azure Storage, rather than files on disk. - In Azure SQL Database, event sessions are always database-scoped. This means that:
- An event session in one database can't collect events from another database.
- An event must occur in the context of a user database to be included in a session.
- In Azure SQL Managed Instance, you can create both server-scoped and database-scoped event sessions. We recommend using server-scoped event sessions for most scenarios.
There are two examples to help you get started with Extended Events in Azure SQL Database and Azure SQL Managed Instance quickly:
- Create a session with an event_file target in Azure Storage. This example shows you how to capture event data in a file (blob) in Azure Storage using the
event_file
target. Use this if you need to persist captured event data, or if you want to use event viewer in SQL Server Management Studio (SSMS) to analyze captured data. - Create a session with a ring_buffer target in memory. This example shows you how to capture the latest events from an event session in memory using the
ring_buffer
target. Use this as a quick way to look at recent events during ad hoc investigations or troubleshooting, without having to store captured event data.
Extended Events can be used to monitor read-only replicas. For more information, see Read queries on replicas.
Adopt the following best practices to use Extended Events in Azure SQL Database and Azure SQL Managed Instance reliably and without affecting database engine health and workload performance.
- If you use the
event_file
target:- Use a storage account in the same Azure region as the database or managed instance where you create event sessions.
- Align the redundancy of the storage account with the redundancy of the database, elastic pool, or managed instance. For locally redundant resources, use LRS, GRS, or RA-GRS. For zone-redundant resources, use ZRS, GZRS, or RA-GZRS. See Azure Storage redundancy for details.
- Don't use any blob access tier other than
Hot
.
- If you want to create a continuously running event session that starts automatically after each Database Engine restart (for example, after a failover or a maintenance event), include the event session option of
STARTUP_STATE = ON
in yourCREATE EVENT SESSION
orALTER EVENT SESSION
statements. - Conversely, use
STARTUP_STATE = OFF
for short-term event sessions such as those used in ad hoc troubleshooting. - In Azure SQL Database, do not read deadlock events from the built-in
dl
event session. If there is a large number of deadlock events collected, reading them with the sys.fn_xe_file_target_read_file() function can cause an out-of-memory error in themaster
database. This might impact login processing and result in an application outage. For the recommended ways to monitor deadlocks, see Collect deadlock graphs in Azure SQL Database with Extended Events.
Azure SQL Database and Azure SQL Managed Instance support the following targets:
- event_file target. Writes complete buffers to a blob in an Azure Storage container.
- ring_buffer target. Holds event data in memory until replaced by new event data.
- event_counter target. Counts all events that occur during an extended events session.
- histogram target. Counts the occurrences of different values of fields or actions in separate buckets.
- event_stream. Streams event data to a .NET application.
Note
The event_stream
target in Azure SQL Database and Azure SQL Managed Instance is in preview.
When you execute the CREATE EVENT SESSION, ALTER EVENT SESSION, and DROP EVENT SESSION statements in SQL Server and in Azure SQL Managed Instance, you use the ON SERVER
clause. In Azure SQL Database, you use the ON DATABASE
clause instead, because in Azure SQL Database event sessions are database-scoped.
Extended Events provides several catalog views. Catalog views tell you about event session metadata or definition. These views don't return information about instances of active event sessions.
Name of catalog view | Description |
---|---|
sys.database_event_session_actions | Returns a row for each action on each event of an event session. |
sys.database_event_session_events | Returns a row for each event in an event session. |
sys.database_event_session_fields | Returns a row for each customize-able column that was explicitly set on events and targets. |
sys.database_event_session_targets | Returns a row for each event target for an event session. |
sys.database_event_sessions | Returns a row for each event session in the database. |
Extended Events provides several dynamic management views (DMVs). DMVs return information about started event sessions.
Name of DMV | Description |
---|---|
sys.dm_xe_database_session_event_actions | Returns information about event session actions. |
sys.dm_xe_database_session_events | Returns information about session events. |
sys.dm_xe_database_session_object_columns | Shows the configuration values for objects that are bound to a session. |
sys.dm_xe_database_session_targets | Returns information about session targets. |
sys.dm_xe_database_sessions | Returns a row for each event session running in the current database. |
There are additional Extended Events DMVs that are common to Azure SQL Database, Azure SQL Managed Instance, and SQL Server:
Just like in SQL Server, you can obtain available events, actions, and targets using this query:
SELECT o.object_type,
p.name AS package_name,
o.name AS db_object_name,
o.description AS db_obj_description
FROM sys.dm_xe_objects AS o
INNER JOIN sys.dm_xe_packages AS p
ON p.guid = o.package_guid
WHERE o.object_type IN ('action','event','target')
ORDER BY o.object_type,
p.name,
o.name;
Tip
In 2022, Microsoft introduced a number of new more granular permissions for Extended Events, for more information see Blog: New granular permissions for SQL Server 2022 and Azure SQL to improve adherence with PoLP.
In Azure SQL Database and Azure SQL Managed Instance, Extended Events support 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 or managed 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, members of the sysadmin
server role hold the CONTROL
permission on the instance.
When you use the event_file
target, event data is stored in blobs in an Azure Storage container. The Database Engine running the event session must have specific access to this container. You can grant this access in one of the following ways:
Assign the Storage Blob Data Contributor RBAC role to the managed identity of the Azure SQL logical server or Azure SQL managed instance on the container, and create a credential to instruct the Database Engine to use managed identity for authentication.
As an alternative to assigning the Storage Blob Data Contributor RBAC role, you can assign the following RBAC actions:
Namespace Action Microsoft.Storage/storageAccounts/blobServices/containers/
read
Microsoft.Storage/storageAccounts/blobServices/containers/blobs/
delete
Microsoft.Storage/storageAccounts/blobServices/containers/blobs/
read
Microsoft.Storage/storageAccounts/blobServices/containers/blobs/
write
Note
The use of managed identity with extended event sessions is in preview.
Create a SAS token for the container, and store the token in a credential.
In Azure SQL Database, you must use a database-scoped credential. In Azure SQL Managed Instance, use a server-scoped credential.
The SAS token you create for your Azure Storage container must satisfy the following requirements:
- Have the
rwdl
(Read
,Write
,Delete
,List
) permissions. - Have the start time and expiry time that encompass the lifetime of the event session.
- Have no IP address restrictions.
- Have the
In Azure SQL Database, memory consumption by extended event sessions is dynamically controlled by the Database Engine to minimize resource contention.
There's a limit on memory available to event sessions:
- In a single database, total session memory is limited to 128 MB.
- In an elastic pool, individual databases are limited by the single database limits, and in total they can't exceed 512 MB.
If you receive an error message referencing a memory limit, the corrective actions you can take are:
- Run fewer concurrent event sessions.
- Using
CREATE
andALTER
statements for event sessions, reduce the amount of memory you specify in theMAX_MEMORY
clause for the session.
Note
In Extended Events, the MAX_MEMORY
clause appears in two contexts: when creating or altering a session (at the session level), and when using the ring_buffer
target (at the target level). The above limits apply to the session level memory.
There's a limit on the number of started event sessions in Azure SQL Database:
- In a single database, the limit is 100.
- In an elastic pool, the limit is 100 database-scoped sessions per pool.
In dense elastic pools, starting a new extended event session might fail due to memory constraints even when the total number of started sessions is below 100.
To find the total memory consumed by an event session, execute the following query while connected to the database where the event session is started:
SELECT name AS session_name,
total_buffer_size + total_target_memory AS total_session_memory
FROM sys.dm_xe_database_sessions;
To find the total event session memory for an elastic pool, this query needs to be executed in every database in the pool.