Azure DB – A Quick Spelunk Into XEvents to Love

This is a very simple introduction into the creation of an Extended Event session using a template for Azure SQL DB. I demonstrate the use of the GUI to configure the session quickly. After which I then script that configuration before creating the session.

The Cloud

It has been a minute since I gave much love or attention to Extended Events in Azure SQL DB. Things have changed a touch since then. We will see about some of those changes in a future article. As for this time, let’s focus on how to get a session in Azure SQL DB up and running really quick and easy.

Create a Session in Azure

I am going to keep this as easy as possible for creating a session and thus I will demonstrate how to do it from the GUI. And for those more in tune with their scripting side, there is a future article on that as well as a glimpse of a script near the end of this article.

Recall from the previous article that things in Azure SQL DB are different for Extended Events. XE is database scoped rather than server scoped (as is the case with your on-premises servers). Due to this change, finding the GUI for XE is a little different.

In order to find the GUI for XE, you must drill down into the database and then you will see “Extended Events.” (Side note, there is no XE Profiler for Azure SQL DB as of this writing.) If you right-click Sessions, you will get a menu with the option to create a New Session.

After clicking “New Session…” the familiar window for a new session will appear.

More Azure XE Session Configs

Here, you see a session that I started creating already. For this session, I opted to go with the template called “Query Detail Tracking” (more on templates for Azure SQL DB in a future article). I also like to enable “Causality tracking” so that checkbox is ticked in this example. The template has the events I wish to capture already and I am only going to use the ring buffer (file target has some additional requirements for an Azure SQL DB session and should be treated in an entirely separate article) so I will skip those screens straight to the advanced screen before completing this session.

From the advanced screen, all I want to do for now is to decrease the dispatch latency from the default 30 seconds down to 10 seconds. For this session, I just want to capture the events quickly in the target and be able to review them as soon as possible. I won’t be able to utilize the “Watch Live data” option for this session (as shown below), so a quick dispatch is essential.

After I have selected all of the configurations I desire for this session, then I click the script button at the top of the window. From there, I will select “New Query Editor Window.” Once scripted, I will receive the following script for this particular session.

Azure XE Session Script

CREATE EVENT SESSION [AzureDB_Demo]
ON DATABASE
ADD EVENT sqlserver.error_reported
(ACTION
 (
 sqlserver.client_app_name
   , sqlserver.database_id
   , sqlserver.query_hash
   , sqlserver.session_id
 )
 WHERE (
   ([package0].[greater_than_uint64]([sqlserver].[database_id], (4)))
   AND ([package0].[equal_boolean]([sqlserver].[is_system], (0)))
   )
)
  , ADD EVENT sqlserver.module_end
(SET collect_statement = (1)
 ACTION
 (
 sqlserver.client_app_name
   , sqlserver.database_id
   , sqlserver.query_hash
   , sqlserver.session_id
 )
 WHERE (
   ([package0].[greater_than_uint64]([sqlserver].[database_id], (4)))
   AND ([package0].[equal_boolean]([sqlserver].[is_system], (0)))
   )
)
  , ADD EVENT sqlserver.rpc_completed
(ACTION
 (
 sqlserver.client_app_name
   , sqlserver.database_id
   , sqlserver.query_hash
   , sqlserver.session_id
 )
 WHERE (
   ([package0].[greater_than_uint64]([sqlserver].[database_id], (4)))
   AND ([package0].[equal_boolean]([sqlserver].[is_system], (0)))
   )
)
  , ADD EVENT sqlserver.sp_statement_completed
(SET collect_object_name = (1)
 ACTION
 (
 sqlserver.client_app_name
   , sqlserver.database_id
   , sqlserver.query_hash
   , sqlserver.query_plan_hash
   , sqlserver.session_id
 )
 WHERE (
   ([package0].[greater_than_uint64]([sqlserver].[database_id], (4)))
   AND ([package0].[equal_boolean]([sqlserver].[is_system], (0)))
   )
)
  , ADD EVENT sqlserver.sql_batch_completed
(ACTION
 (
 sqlserver.client_app_name
   , sqlserver.database_id
   , sqlserver.query_hash
   , sqlserver.session_id
 )
 WHERE (
   ([package0].[greater_than_uint64]([sqlserver].[database_id], (4)))
   AND ([package0].[equal_boolean]([sqlserver].[is_system], (0)))
   )
)
  , ADD EVENT sqlserver.sql_statement_completed
(ACTION
 (
 sqlserver.client_app_name
   , sqlserver.database_id
   , sqlserver.query_hash
   , sqlserver.query_plan_hash
   , sqlserver.session_id
 )
 WHERE (
   ([package0].[greater_than_uint64]([sqlserver].[database_id], (4)))
   AND ([package0].[equal_boolean]([sqlserver].[is_system], (0)))
   )
)
ADD TARGET package0.ring_buffer
WITH
(
MAX_MEMORY = 4096KB
  , EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
  , MAX_DISPATCH_LATENCY = 10 SECONDS
  , MAX_EVENT_SIZE = 0KB
  , MEMORY_PARTITION_MODE = NONE
  , TRACK_CAUSALITY = ON
  , STARTUP_STATE = OFF
)
GO

Notice that there is nothing about starting this session in the script to start the session.

ALTER EVENT SESSION [AzureDB_Demo] ON DATABASE
STATE = START;

Note that “on database” has been specified instead of the traditional “on server” for the session state command. In order to stop the session, it is just as easy as follows.

ALTER EVENT SESSION [AzureDB_Demo] ON DATABASE
STATE = STOP;

Put a Bow on It

This has been a very simple introduction into the creation of an Extended Event session using a template for Azure SQL DB. I demonstrated the use of the GUI to configure the session quickly and then to subsequently script that configuration before creating the session.

For more uses of Extended Events, I recommend my series of articles designed to help you learn XE little by little.

Interested in seeing the power of XE over Profiler? Check this one out!

This is sixth post in the 2021 “12 Days of Christmas” series. All articles in the series (for all years) is available via this page.

The post Azure DB – A Quick Spelunk Into XEvents to Love first appeared on SQL RNNR.