Hadi Fadlallah
running SQL Server Profiler from the Management Studio

An overview of the SQL Server Profiler

December 9, 2019 by

In this article, we will describe SQL Server Profiler and how it can be used. In addition, we will mention the tools that are integrated with it- plans about its deprecation, and what are the alternatives?

Introduction

SQL Server Profiler is a tracing tool provided by Microsoft since SQL Server 2000 version. It is used to trace activities and operations executed on a specific SQL Server database engine or Analysis Service to be analyzed later.

SQL Server is responsible for two main operations:

  1. Tracing: It can monitor all operations executed over an instance
  2. Replay: It can rerun all operations logged in a trace later

Profiler is a standalone tool installed with SQL Server. You can access it from Windows Explorer or SQL Server Management Studio.

Under Windows 10, you can write SQL Server Profiler from the Start menu, and the application will show in the results:

running SQL Server profiler from the start menu in Windows 10

Figure 1 – Accessing Profiler from the Start menu

Or you can simply find a shortcut of this tool under the Tools menu inside the SQL Server Management Studio as shown in the image below:

running SQL Server Profiler from the Management Studio

Figure 2 – Accessing SQL Server Profiler from Management Studio

How to use Profiler?

When you open the Profiler, the authentication form is shown. You have to select whether you need to connect to an Analysis Service instance or a Database Engine. Then you should enter the instance name, the authentication type, and the credentials:

connecting to an instance in SQL Server Profiler

Figure 3 – Profiler authentication form

When the connection is established, a new trace form is shown. In this form, there are two tabs: (1) General tab and (2) Events Selection.

In this first tab, you have to enter the trace name. Then you can select whether you need to save the trace results into a table or external file and also if you need to pause the trace at a specific date-time.

Also, you can select a trace template from a drop-down list based on your needs. Note that a template is a set of tuning events that are caught by the profiler trace. There are different predefine templates such as T-SQL, Tuning or you can select the standard (default) template or start from scratch by selecting the Blank template:

General tab of the trace properties form

Figure 4 – General tab of the Trace Properties form

In the Events Selection tab, you can select all events that you need to include in the trace, not that you can add some filters. As an example, if you need only to trace the operations executed in a specific database, then you can add a filter on the database name property. Note that even if you select a template in the General tab, you can edit the event’s selection:

Events selection tab of the Trace properties from

Figure 5 – Events selection tab of the Trace Properties from

For more details about creating and running a trace over a specific database, you can refer to a previously published article, where I used SQL Server Profiler to catch the SQL command executed by an SQL Server Integration Services package: SSIS OLE DB Source: SQL Command vs Table or View.

SQL Server Profiler can be used to detect what are the commands executed by an application over a database for security and tuning purposes. To read more about this tool, you can refer to the official documentation.

Database Engine Tuning Advisor

After quickly describing SQL Server Profiler, it is worth mentioning that there is another tool provided by SQL Server that can read and analyze the traces created by the Profiler. This tool is called SQL Server Tuning Advisor. You can access it from SQL Server Management Studio. This tool takes a profiler trace as input. It gives some recommendations (missing indexes, partitioning, etc.) to improve the Database Engine performance based on the operations saved within the trace. To read more about this tool, you can refer to the official documentation: Database Engine Tuning Advisor.

Deprecation and alternatives

When you access the official documentation, you will see that SQL Server Profiler for Database Engine is deprecated and it might be removed in a future version. Also, they recommended avoiding using this feature anymore. While using this tool to trace Analysis Services, workloads are not deprecated.

As we mentioned at the beginning of this article, the Profiler is responsible for two operations: Trace and Replay. After deprecation, Microsoft decided to create a feature for each operation.

They recommended using Extended Events to create traces over a Database Engine, while they recommended using distributed replay to perform replay operations. While they still recommend using SQL Server Profiler to create traces and to perform replays over Analysis Services instance.

Conclusion

In this article, we have described the old SQL Server tracing tool called Profiler. We mentioned this tool use cases and other SQL Server tools that can integrate with it. Finally, we mentioned the alternatives that can be used after it is deprecated for tracing the Database Engine only.

In the end, learning this feature is very important since it is effortless and easy to learn. Furthermore, it will get you more familiar with tracing and replay concepts, which makes learning new alternatives easier.

Hadi Fadlallah
Monitoring, SSAS monitoring, Utilities

About Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies. Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional. On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques. Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website. View all posts by Hadi Fadlallah

168 Views