Tips & Tricks #2: SQL Server Profiler (xEvents) for Azure SQL DB

Microsoft

Apr 09, 2022

The SQL Server Profiler using SQL Server Management Studio (SSMS) currently support Azure SQL Manage Instance and SQL Server platforms and does not support Azure SQL DB platform, so as a workaround we can use SQL Server Profiler extension for Azure Data Studio (ADS).

"The SQL Server Profiler extension provides a simple SQL Server tracing solution similar to SQL Server Management Studio (SSMS) Profiler except built using Extended Events. SQL Server Profiler is very easy to use and has good default values for the most common tracing configurations. The UX is optimized for browsing through events and viewing the associated Transact-SQL (T-SQL) text. The SQL Server Profiler for Azure Data Studio also assumes good default values for collecting T-SQL execution activities with an easy to use UX. This extension is currently in preview." More information can be found here.

In this article we will cover the below contents:

How to use the SQL Server Profiler extension for Azure SQL DB.
How to export the output session into an XML-file (.xml).
How to export the output session into an XEL-file (.xel) and store it into an Azure Storage Account.

Prerequisites:

An Azure Data Studio (ADS). Download it from here.
An SQL Server Management Studio (SSMS). Download it from here.
An Azure account with an active subscription. Create an account for free.
An Azure Storage Account. Create one from here.

How to use the SQL Server Profiler extension for Azure SQL DB:

Open ADS and click on Extensions from the left-side then search for SQL Profiler For Azure Data Studio and finally click on Install, please have look at the below snapshot:
Click on Connections (Left-side), then click on Add Connection, please have a look at the below snapshot:
In the add new connection dialog, fill in your Azure SQL Server information and select the database, please have a look at the below snapshot:
In the create new firewall rule, click on reenter your credentials, please have a look at the below snapshot:
After entering your credentials and back to the firewall rule dialog, select add my client IP and then click OK, please have a look at the below snapshot:
After that you will be back to the connection dialog, then select your database and click connect, please have a look at the below snapshot:
After establishing a connection, right-click on your Azure SQL database and select Launch profiler (Alt+P), please have a look at the below snapshot:
From Start New Profiler Session dialog, enter session name and click on Start, please have a look at the below snapshot:
A profiler tab will appear with all the events that is currently running on the Azure SQL database, please have a look at the below snapshot:
Right-click on your Azure SQL database and select New Query, please have a look at the below snapshot:
Enter your specified query and click on Run, then click on the profiler tab and you will notice your query event is logged, please have a look at the below snapshot:

How to export the output session into an XML-file (.xml):

Open SSMS and connect to your Azure Database.
Open Extended Events and select the session that you have created in Azure Data Studio.
Right-click on the session buffer and select View Target Data.
In the Session buffer tab (in the right-side) right-click and select Refresh (XML data will be created).
Click on XML Data and save the new view tab as XML and please send it to me.

Please have a look at the following snapshots:

How to export the output session into an XEL-file (.xel) and store it into an Azure Storage Account:

From SSMS, right-click on your session and select properties
From session properties, select Data Storage to and enter your Azure Storage Account to save the file as “.xel” as shown in the below image and click OK.
From SSMS, right-click on your session and select start session (if not running already).
After you finish from capturing events, open your storage account where you saved your “.xel” file.
Download it into your local machine then open it using SSMS and follow this document to export the results into table to be able to filter the results as per your needs.