SQLServerCentral Article

Is SQL Server Profiler in Azure Data Studio?

,

Introduction

Do we have the SQL Profiler in Azure Data Studio? In a previous article, we compared SSMS and Azure Data Studio. Now we will compare the new SQL Profiler against the old one. If you check the SQL Server Profiler documentation, you will find that SQL Profiler is deprecated and that that feature will be removed in a future version of SQL Server. However, if we look for the extensions in Azure Data Studio we can see the SQL Profiler.

Profiler in Azure Data Studio SQL Server Profiler

What is it doing here? Isn't it supposed to be deprecated?

In this article we will learn:

  • What is doing the SQL Profiler in Azure Data Studio?
  • How to install the SQL Profiler in ADS.
  • Learn how to use it, use the templates and fields available on it.
  • Disadvantages about the new Profiler.

What is doing the SQL Profiler in Azure Data Studio?

SQL Server Profiler was announced to be deprecated a long time ago, but it is still in the last versions of SQL Server. However, in Azure Data Studio, is just the name. Behind the Scenes, we are calling extended events. So, the name is used just because the functionality is similar, but behind the scenes, it is better technology with less resource consumption. It is named SQL Server Profiler just for old users.

How to install SQL Profiler in ADS

First of all, go to extensions and then on search text box write admin and you will see the Admin Pack for SQL Server. This extension includes SQL Profiler, SQL Server Agent, SQL Server Import, SQL Server dacpac.

Admin Pack for SQL Server

On the other hand, if you do not want to install all the modules, you can look for the SQL Server Profiler only:

Data Studio SQL Profiler

Learn how to use it, use the templates and fields available on it

First of all, in order to launch Profiler, go to SERVERS and right-click the SQL Server connection, and select Launch Profiler or press Alt+P.

Launch Profiler

Secondly, for SQL Server On-Premises, you have 2 available templates. The Standard_OnPrem and the TSQL_OnPrem. We will talk about the templates later in this article.

Standard OnPrem template

In addition, we have the Standard_Azure template for Azure Connections.

Azure template

The standardonPrem template contains the following columns:

  • The EventClass is the type of event that occurs in a database like login, logout, statement completed, statement recompile, and so on.
  • TextData is usually T-SQL commands that run.
  • NTUserName is the user that activated the event.
  • LoginName is the user that log in to the database.
  • ClientProcessID is the ID assigned by the host to the process with the client connection.
  • SPID is the ID of the session.
  • StartTime is the time where the event started.
  • CPU is the amount of CPU time in ms
  • Reads are the number of logical page reads.
  • Writes show the number of physical writes.

Standard on-Premises data

The TSQL-OnPrem template contains fewer fields than the standardonPrem templates. This template checks and monitors T-SQL queries.

TSQL on-prem data

The TSQL-Azure has similar columns to the templates on-premises.

Azure template data

We have options to filter that data.

Filter option

The filter can be built as a series of expressions, as shown below.

filters and operators in Profiler

In addition, you can start, stop, and pause the template data collection.

Start, Stop traces

You can select different sessions like the AlwaysOn_health to monitor an AlwaysOn configuration. System_health monitors deadlocks, diagnostics, CLR Allocation failures, virtual allocation failures, security errors, and more.

Sessions available in SQL Profiler in Azure Data Studio

You can also choose the Standard View to debug problems, TSQL to monitor T-SQL queries, the Tuning View allows tuning the performance, and  TSQL_Locks checks the locked transactions, and then we have TSQL Duration will allow us to find long-running queries to check the duration of the queries.

Views in SQL Profiler in Azure Data Studio

Disadvantages of the new Profiler

This may change in the future, but you could analyze the trace files in the Database Engine Tuning Advisor. Also, it was possible to replay trace files on a different server. You had options to select the events, save the trace to a file or a table and enable a trace stop time.

Conclusion

To conclude, we can say that Azure Data Studio has an extension with the SQL Profiler. This SQL Profiler is based on extended events, so it consumes fewer resources and is faster than the traditional SQL Profiler. The functionality, filters are similar, but the Profiler does not have the same functionality as the old Profiler. However, this may change in the future. Also, you have different templates for Azure than SQL On-premises.

 

Rate

5 (2)

Share

Share

Rate

5 (2)