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:
- Tracing: It can monitor all operations executed over an instance
- 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:
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:
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:
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:
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:
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.
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
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023