Azure Data Studio SQL Server Profiler Extension

By:   |   Updated: 2021-12-22   |   Comments   |   Related: > Azure Data Studio


Problem

I have Azure Data Studio available and there is a performance issue reported on one of the SQL Servers supported. Is there any way by which I can make use of Azure Data Studio to monitor the performance issues on this server?

Solution

Using SQL Server Management Studio (SSMS), we can easily use extended events as simply as just clicking the option to 'Launch Session' to collect performance data as shown in the screenshot below.

launch extended events session

Before using Extended Events, SQL Profiler was quite commonly used. However, it is not recommended to be use any longer due to issues around performance while using it. SQL Server Profiler is a graphical tool and it generally collects more information than required and on a heavily used production system, using this can cause considerable performance issues. Due to these risks, it is not advisable to run it on production servers any more. Moreover, SQL Profiler is considered a deprecated feature and may not be available in future releases.

Azure Data Studio as such does not have this tool in the base install, but it can be downloaded as an extension. The good news is that even though the extension is known as 'SQL Server Profiler' in Azure Data Studio, under the hood it is making use of Extended Events. So, it is very much light weight with very low resource requirements.

Installing Azure Data Studio

Starting with SQL Server Management Studio (SSMS) 18.7, Azure Data Studio is automatically part of the install and you don't have to perform any additional installations. However, you can also install Azure Data Studio separately from this link, if you don't already have SSMS installed. Also, as Azure Data Studio is available on various platforms, make sure to download and install the installer file for the correct operating system – Windows, macOS or Linux.

From within SSMS, you can view the option to use Azure Data Studio as shown.

start azure data studio from ssms

How to use SQL Server Profiler on Azure Data Studio

You can make use of extensions that are available in Azure Data Studio. There is an extension known as the 'SQL Server Profiler' specifically for this purpose. Either you can download this separately or this extension is also available when you install the extension known as the 'Admin Pack for SQL Server'.

admin pack for sql server

As you can see the 'SQL Server Profiler' is available when you install the 'Admin Pack for SQL Server' extension. If you just want to install the 'SQL Server Profiler', it is available separately as well.

sql server profiler for azure data studio

As you can see, the process to install this extension is simple and straight forward and can be done in a few easy steps.

Using SQL Server Profiler on Azure Data Studio

In order to launch Profiler, you can do so from the 'Command Palette' as shown or use the relevant shortcut keys (Alt + P).

azure data studio and sql server profiler

Once on the 'Command Palette', you can use the option to launch the profiler as shown.

azure data studio and sql server profiler

When you click on the option to 'Launch Profiler', you will see this screen where you can provide details of your server.

azure data studio and sql server profiler

Provide the server details and click on the 'Connect' option. Once done, you will see this option.

azure data studio and sql server profiler

If you check the session templates available, you will see these two options.

azure data studio and sql server profiler

The server we are trying to connect to is an on-premises server. If you check closely, this is the same options available when you try to start extended events using SSMS.

ssms extended events sessions

Starting the profiler session on Azure Data Studio

You can select the session template based on your requirement and click on the option to start the profiler session. This will start the profiler session and you will be able to see in real time the session activity as shown.

azure data studio and sql server profiler starting

You can review the section highlighted yellow where you can view additional options available to use. There are various view options that can be used. You can select the option of your choice to view the session activity.

azure data studio and sql server profiler views

The filter option is quite useful to collect activity based on the database you wish to monitor or any other specific filter option you wish to add.

azure data studio and sql server profiler filters

Here, you can see that the session activity is filtered by 'DatabaseName'. You can add additional clauses based on your requirement. There are a number of additional filter clauses that are available as shown in the screenshot below.

azure data studio and sql server profiler event classes

Clearing all the filters is easy as there is an option available within the template as shown.

azure data studio and sql server profiler clear filters

Once you are fine with the session activity collected, you can stop the profiler using the [Stop] option which is in red.

Viewing data collected on Azure Data Studio

You will be able to view the data collection in real time. It will help if you apply the correct filter clauses as shown earlier to focus on the correct information and eliminate unwanted noise. You can review the code quite easily by clicking on the relevant rows and viewing additional information as shown.

azure data studio and sql server profiler details

You can see the actual code that was running against the database. I had applied the filter clause in this case. If you click on the 'Details' option, you will be able to see more useful information which will help you to analyze further.

azure data studio and sql server profiler details

Likewise, you will be able to review the other data collected based on the activity information that is collected.

Can we use this feature on an Azure SQL Database?

So far, the demo was performed using an on-premises SQL Server. The next obvious question is whether this extension can be used to run a profiler trace session on an Azure SQL Database. The answer is 'Yes'. Make sure that you select the correct Azure SQL Database when you provide the Azure server details in the 'Connection' option as shown. You will get this 'Connection' option after you click on 'Launch Profiler' in the 'Command Palette' which was shown in the earlier sections.

azure data studio and sql server profiler against azure sql database

By default, 'master' database option is available. Make sure that you change it to the correct Azure SQL Database. If you select the 'master' database option for your Azure server and try to launch the profiler, you will get this error message.

azure data studio and sql server profiler against azure sql database

However, when you select the correct Azure SQL database and launch the profiler, you will get to view this template specifically for Azure.

azure data studio and sql server profiler against azure sql database

You can click on the 'Start' option which will start the profiler on the Azure SQL Database. The interface will be similar to what was performed while using the profiler on an on-premises server as shown below.

azure data studio and sql server profiler against azure sql database

The features are the same, but this is running on an Azure SQL Database. As you can see in this demo, you saw how we could make use of Azure Data Studio to run the profiler tool. As mentioned earlier, the name profiler is misleading in this case, as this Azure Data Studio extension is built using extended events unlike the profiler that is available in SSMS. The process to run the profiler is quite simple and can be started in a few easy clicks.

Next Steps
  • You can try out Azure Data Studio that is already available in SSMS. If not, you can download Azure Data Studio separately from this link.
  • Download the required profiler extension described in this tip
  • Review this tip and try out the demo on your lab server to launch the profiler



Get Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights




get scripts

next tip button



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips


Article Last Updated: 2021-12-22

Comments For This Article





download














get free sql tips
agree to terms