Rediger

Del via

Extended Events in Azure SQL Database and Azure SQL Managed Instance

  • Artikkel

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.

Get started

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.

Best practices

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 your CREATE EVENT SESSION or ALTER 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 the master 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.

Event session targets

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.

Transact-SQL differences

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 catalog views

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 dynamic management views

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.

Common DMVs

There are additional Extended Events DMVs that are common to Azure SQL Database, Azure SQL Managed Instance, and SQL Server:

Available events, actions, and targets

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;

Permissions

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.

Storage container authorization and control

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.

Resource governance

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 and ALTER statements for event sessions, reduce the amount of memory you specify in the MAX_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.

Related content