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.
On the other hand, if you do not want to install all the modules, you can look for the SQL Server Profiler only:
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.
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.
In addition, we have the Standard_Azure template for Azure Connections.
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.
The TSQL-OnPrem template contains fewer fields than the standardonPrem templates. This template checks and monitors T-SQL queries.
The TSQL-Azure has similar columns to the templates on-premises.
We have options to filter that data.
The filter can be built as a series of expressions, as shown below.
In addition, you can start, stop, and pause the template data collection.
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.
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.
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.
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.