Improving Extended Events in Azure SQL

Executive summary

 

Extended Events is a powerful tool for database engine monitoring, troubleshooting, and activity tracing. While Azure SQL supported Extended Events for many years, there were notable gaps in the functionality and the ease of use, compared to Extended Events in SQL Server.

 

To better support Extended Events in Azure SQL, in recent months we have made several improvements. Now, you can:

• View event data in SSMS without downloading xel files from Azure Storage
• Watch live data for an event session
• Use the XEvent Profiler
• Use the histogram target
• Monitor Extended Events performance
• Use granular Extended Events permissions

We are grateful to our customers for their feedback and suggestions that enabled many of these improvements.

 

View event data from Azure Storage in SSMS

 

Previously, to open an xel file from Azure Storage in the SSMS event viewer, you had to download it to a local folder. With this improvement, you can open xel files from Azure Storage by double-clicking the event_file target for an event session, or by selecting View Target Data from the context menu. This also automatically reads event data from all rollover files for the session.

 

 

This improvement is available starting with SSMS 19.2, for both Azure SQL Database and Azure SQL Managed Instance.

Opening xel files may take longer if the network bandwidth between the machine running SSMS and the Azure SQL database or managed instance is limited.

 

Note: As of 2023-11-13, this improvement is only available in a subset of Azure regions for Azure SQL Database. Worldwide availability is expected by the end of November 2023.

 

Watch live data

 

The Watch Live Data feature of SSMS lets you see new events in near real time.

 

Internally, Watch Live Data uses the event_stream target. This target is now enabled in Azure SQL Database in preview, allowing .Net applications such as SSMS receive the event stream. The event_stream target is always present for any event session. You do not need to add any other targets such as event_file or ring_buffer to use the Watch Live Data feature in SSMS.

 

You can access the Watch Live Data feature from the context menu of any event session.

 

 

Watch Live Data is available starting with SSMS 19.2, for both Azure SQL Database and Azure SQL Managed Instance.

 

To see the Watch Live Data menu item in Azure SQL Database, you need to connect Object Explorer to your user database, rather than to the default master database. To do that, select Options>>Connection Properties in the Connect to Server dialog, and then select or enter the database name.

 

 

For those of you who prefer to use command-line tools, near real time event data can be obtained using the PowerShell Read-SqlXEvent cmdlet. Here’s a code example:

 

Import-Module SqlServer
Read-SqlXEvent -ConnectionString "Server=***.database.windows.net;Initial Catalog=***;User ID=***;Password=***" -SessionName ***

 

This cmdlet has been available for a few years, and now you can use it with event sessions in Azure SQL.

 

XEvent Profiler

 

With a single click, this SSMS feature creates an event session and opens the event viewer UI to see events as they arrive. There are two built-in sessions supported in XEvent Profiler, named Standard and TSQL (corresponding event session names are QuickSessionStandard and QuickSessionTSQL). You can use these sessions to monitor connections, query executions, and errors.

 

Similar to the Watch Live Data feature, XEvent Profiler uses the event_stream target (currently in preview in Azure SQL Database) to deliver events in near real time, without saving event data in persistent storage. If you do want to save the events produced by these sessions, select the Extended Events menu, select Stop Data Feed, and then select Export to, XEL File.

 

 

XEvent Profiler is available for both Azure SQL Database and Azure SQL Managed Instance starting with SSMS 19.2. Just like Watch Live Data, you need to connect Object Explorer to your user database to see the XEvent Profiler menu option in Azure SQL Database.

 

The histogram target

 

The histogram target, sometimes informally known as the asynchronous bucketizer target, processes incoming events and counts the occurrences of values for an event field or an action in histogram slots, or buckets.

 

For example, if you see high network waits in sys.dm_os_wait_stats, it might indicate that an application is processing query results slowly, one row at a time (sometimes known as the RBAR pattern). You can use the histogram target to narrow the problem down to a specific application.

 

Here’s how you can use T-SQL to create such a session:

 

CREATE EVENT SESSION [histogram-example] ON DATABASE 
ADD EVENT sqlos.wait_completed
(
SET collect_wait_resource=(0)
ACTION (sqlserver.client_app_name)
WHERE (wait_type='NETWORK_IO')
)
ADD TARGET package0.histogram
(
SET filtering_event_name=N'sqlos.wait_completed',
    source=N'sqlserver.client_app_name'
);

 

This session collects the wait_completed event, including the client_app_name action that identifies the connected application. It filters the occurrences to include only those with the NETWORK_IO waits. Finally, it adds the histogram target, with a bucket for each client application name.

 

We can see that one of the applications has a much larger number of NETWORK_IO waits, which narrows the scope of our investigation.

 

 

You can create and configure a session with the histogram target using either T-SQL or the SSMS UI.

 

The histogram target is now enabled in Azure SQL Database and Azure SQL Managed instance. All recent versions of SSMS support creating event sessions with the histogram target.

 

Events enabled or updated in Azure SQL Database

 

Several events useful in advanced monitoring and troubleshooting have been enabled or updated in Azure SQL Database. We enabled the latch_acquired, latch_released, and spinlock_backoff events, and added the resource_description field to the wait_info event.

 

Performance diagnostic improvements

 

We have made two recent improvements to the diagnostics of Extended Events itself. One of them is described in our earlier blog, XEvent Performance Metrics. In brief, this improvement adds columns to the sys.dm_xe_database_session_events DMV to describe event publishing statistics, such as the average time required to publish an event, and the total number of event occurrences published. This can help you find sessions with a significant performance impact, for example those collecting query plans or using predicates that filter long string fields.

 

The other improvement is the addition of the failed_buffer_count column to the sys.dm_xe_database_session_targets DMV. This column tracks the number of buffers that a target failed to process due to target specific issues, such as an expired SAS token with the event_file target, or a memory allocation failing for a ring_buffer target.

 

Permission improvements

 

Historically, all Extended Event operations such as creating, dropping, starting, or stopping a session required the ALTER ANY EVENT SESSION permission in SQL Server and Azure SQL Managed Instance, and the ALTER ANY DATABASE EVENT SESSION permission in Azure SQL Database.

 

Recently we have added more granular permissions for a fine-grained control over specific DDL statements, for example adding and removing events, adding and removing targets, as well as starting, stopping, enabling, and disabling sessions. These new permissions are available in Azure SQL and in SQL Server 2022. All of them are contained in the older ALTER ANY EVENT SESSION and ALTER ANY DATABASE EVENT SESSION permissions, which remain available.

 

Another notable change is for the sys.fn_xe_file_target_read_file() function. This T-SQL function returns event data from xel files as a relational rowset. Executing this function no longer requires the CONTROL permission, which restricted its use to server administrators and database owners. Now, this function requires the VIEW SERVER PERFORMANCE STATE permission. In Azure SQL Database,  this permission can be granted to non-administrators via membership in either ##MS_ServerStateReader## or ##MS_ServerPerformanceStateReader## server role.

 

Updated documentation

 

Last but not least, we have updated several documentation articles for Extended Events, focusing on Azure SQL content. The Extended Events in Azure SQL Database article, and the walkthroughs for creating event sessions with event_file and ring_buffer targets in Azure SQL Database have been mostly rewritten, focusing on helping customers new to Extended Events get started quickly.

 

Conclusion

 

If you’ve been hesitant to use Extended Events in Azure SQL because it looks complicated, these changes have made it easier. We encourage folks to give it a try. Use of Extended Events adds very powerful and flexible tools to your monitoring and troubleshooting toolbox.

 

We remain committed to improving Extended Events in Azure SQL and in SQL Server. Just like with any other SQL feature, we encourage customers to leave Extended Events feedback at https://aka.ms/sqlfeedback.