Extended events in Azure SQL Database
Applies to:
Azure SQL Database
The feature set of extended events in Azure SQL Database is a robust subset of the features on SQL Server and Azure SQL Managed Instance.
XEvents is an informal nickname that is sometimes used for 'extended events' in blogs and other informal locations.
Additional information about extended events is available at:
Prerequisites
This article assumes you already have some knowledge of:
The bulk of our documentation about extended events applies to SQL Server, Azure SQL Database, and Azure SQL Managed Instance.
Prior exposure to the following items is helpful when choosing the Event File as the target:
Code samples
Related articles provide two code samples:
Ring Buffer target code for extended events in Azure SQL Database
- Short simple Transact-SQL script.
- We emphasize in the code sample article that, when you are done with a Ring Buffer target, you should release its resources by executing an alter-drop
ALTER EVENT SESSION ... ON DATABASE DROP TARGET ...;
statement. Later you can add another instance of Ring Buffer byALTER EVENT SESSION ... ON DATABASE ADD TARGET ...
.
Event File target code for extended events in Azure SQL Database
- Phase 1 is PowerShell to create an Azure Storage container.
- Phase 2 is Transact-SQL that uses the Azure Storage container.
Transact-SQL differences
When you execute the CREATE EVENT SESSION command on SQL Server, you use the ON SERVER clause. But on Azure SQL Database you use the ON DATABASE clause instead.
The ON DATABASE clause also applies to the ALTER EVENT SESSION and DROP EVENT SESSION Transact-SQL commands.
A best practice is to include the event session option of STARTUP_STATE = ON in your CREATE EVENT SESSION or ALTER EVENT SESSION statements.
- The = ON value supports an automatic restart after a reconfiguration of the logical database due to a failover.
New catalog views
The extended events feature is supported by several catalog views. Catalog views tell you about metadata or definitions of user-created event sessions in the current database. The views do not 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. |
In Microsoft SQL Server, similar catalog views have names that include .server_ instead of .database_. The name pattern is like sys.server_event_%
.
New dynamic management views (DMVs)
Azure SQL Database has dynamic management views (DMVs) that support extended events. DMVs tell you about active 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 that is scoped to the current database. |
In Microsoft SQL Server, similar catalog views are named without the _database portion of the name, such as:
sys.dm_xe_sessions
instead ofsys.dm_xe_database_sessions
.
DMVs common to both
For extended events there are additional DMVs that are common to Azure SQL Database, Azure SQL Managed Instance, and Microsoft SQL Server:
Find the available extended events, actions, and targets
To obtain a list of the available events, actions, and target, use the sample 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;
Targets for your Azure SQL Database event sessions
Here are targets that can capture results from your event sessions on Azure SQL Database:
- Ring Buffer target - Briefly holds event data in memory.
- Event Counter target - Counts all events that occur during an extended events session.
- Event File target - Writes complete buffers to an Azure Storage container.
The Event Tracing for Windows (ETW) API is not available for extended events on Azure SQL Database.
Restrictions
There are a couple of security-related differences befitting the cloud environment of Azure SQL Database:
- Extended events are founded on the single-tenant isolation model. An event session in one database cannot access data or events from another database.
- You cannot issue a
CREATE EVENT SESSION
statement in the context of themaster
database.
Permission model
You must have Control permission on the database to issue a CREATE EVENT SESSION
statement. The database owner (dbo) has Control permission.
Storage container authorizations
The SAS token you generate for your Azure Storage container must specify rwl for the permissions. The rwl value provides the following permissions:
- Read
- Write
- List
Performance considerations
There are scenarios where intensive use of extended events can accumulate more active memory than is healthy for the overall system. Therefore Azure SQL Database dynamically sets and adjusts limits on the amount of active memory that can be accumulated by an event session. Many factors go into the dynamic calculation.
There is a cap on memory available to XEvent sessions in Azure SQL Database:
- In single Azure SQL Database in the DTU purchasing model, each database can use up to 128 MB. This is raised to 256 MB only in the Premium tier.
- In single Azure SQL Database in the vCore purchasing model, each database can use up to 128 MB.
- In an elastic pool, individual databases are limited by the single database limits, and in total they cannot exceed 512 MB.
If you receive an error message that says a memory maximum was enforced, some corrective actions you can take are:
- Run fewer concurrent event sessions.
- Through your CREATE and ALTER statements for event sessions, reduce the amount of memory you specify on the MAX_MEMORY clause.
There is a cap on number of started XEvent sessions in Azure SQL Database:
- In a single Azure SQL 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 may fail due to memory constraints even when the total number of started sessions is below 100.
Network latency
The Event File target might experience network latency or failures while persisting data to Azure Storage blobs. Other events in Azure SQL Database might be delayed while they wait for the network communication to complete. This delay can slow your workload.
- To mitigate this performance risk, avoid setting the EVENT_RETENTION_MODE option to NO_EVENT_LOSS in your event session definitions.
Related links
- Azure Storage Cmdlets
- Using Azure PowerShell with Azure Storage
- How to use Blob storage from .NET
- CREATE CREDENTIAL (Transact-SQL)
- CREATE EVENT SESSION (Transact-SQL)
- The Azure Service Updates webpage, narrowed by parameter to Azure SQL Database:
Feedback
Submit and view feedback for