SQL Server Profiler in Azure Data Studio

Overview

The SQL Server Profiler for Azure Data Studio is an extension that provides similar functionality to the SQL Server Profiler, a tool traditionally used in SQL Server Management Studio (SSMS). It helps in tracking the events in the SQL Server to debug issues.

Why Use Extended Events Over Traditional Profiler?

While the traditional Profiler has been a staple tool for many years, the adoption of Extended Events is encouraged due to several advantages:

• Lower Performance Overhead: Extended Events are designed to minimize performance impact, making them suitable for continuous monitoring even on production servers.

• Greater Flexibility: XEvents offer a more robust event handling system, allowing for more detailed and customizable data collection strategies.

• Better Integration: Extended Events are more seamlessly integrated with modern SQL Server features and provide better support for newer versions of SQL Server.

• Scalability: XEvents are more scalable when dealing with large data volumes or highly concurrent systems.

Prerequisites

• Ensure Azure Data Studio is installed.

• SQL Server instance accessible from Azure Data Studio.

• Install the SQL Server Profiler extension in Azure Data Studio.

Installation of the SQL Server Profiler Extension

1. Open Azure Data Studio.

2. Navigate to the Extensions view by clicking on the square icon on the sidebar or pressing Ctrl+Shift+X.

3. Search for "SQL Server Profiler" in the search bar.

4. Click on the install button next to the extension. Once installed, you might need to reload Azure Data Studio.
   
   

   Open

   

Using SQL Server Profiler

1. Connect to the database:

   • Open Azure Data Studio and connect to your SQL Server instance.

   • Navigate to the server or database you want to profile.

2. Start a profiling session:

   • From the top menu, select View > Command Palette or press Ctrl+Shift+P.

   • Type Profiler: Launch Profiler and select it or press Alt +P

   • Choose the connection you want to profile if not already selected.
     
     

     Open

     

3. Select a template:

   • After launching the Profiler, you will need to choose a template that best suits the type of analysis you wish to perform. Templates define which events are tracked and what data is collected.
     
     

4. Start and stop the trace:

   • After youStart to begin capturing events, click the stop button and change the session to use ADS_Filtered_OnPrem extended event as it filters only the custom stored procedures and it’s lighter.
     
     

     
     then click the filter button to specify the keyword you want to profile:

   • To stop collecting data, click Stop. Note: It’s important to stop the profiler when you have gathered enough data to avoid performance overhead, which is still way lighter than the traditional profiler.

5. Analyzing the trace:

   • The events will be displayed in a grid format.
     
     
     

   • You can analyze the events directly in Azure Data Studio or export the results for further analysis.
     
     

6. Saving and Loading Profiler Sessions:

   • To save a session, click Save on the profiler toolbar. This allows you to save the session to a file.

   • To load a session, open the Command Palette, type and select Profiler: Open Profiler, and navigate to the saved file.