SQL Server Profiler is a powerful tool that is often underutilized by DBAs. It provides valuable insights into the inner workings of the SQL Server Engine, allowing users to trace and analyze T-SQL commands and query resolution. In this article, we will explore the key functions of SQL Server Profiler and address common concerns about its usage.

Key Functions of SQL Server Profiler

SQL Server Profiler offers several significant functions:

• Creating trace: Profiler allows users to create a trace that captures all the T-SQL scripts running on SQL Server.
• Watching trace: Users can monitor the trace in real-time to observe the execution of T-SQL commands and operations.
• Storing trace: Profiler enables users to save the trace data either in a database or as trace files for future analysis.
• Replaying trace: Users can import trace files back into Profiler to replay and analyze the captured events.

Dilemma of Using Profiler on Production Server

Despite its potential advantages, developers often hesitate to use SQL Server Profiler on production servers due to two main concerns:

1. Overhead on production server: Running Profiler can add additional load on the CPU of the production server. However, the impact of this overhead depends on the server’s CPU utilization. If the CPU is not heavily utilized, running Profiler may not significantly degrade performance.
2. Complexity of analyzing data: Profiler captures a vast amount of data, making it challenging to analyze. To address this, it is crucial to configure Profiler to capture only the necessary data for analysis.

Understanding Profiler Terminology

Before delving into the details of Profiler, it is essential to understand its terminology:

• Event: An event represents an action within the SQL Server Database Engine, such as running T-SQL commands or performing database-related operations.
• Event Class: An event class is a type of event that can be traced. Examples include “SP:Starting” and “RPC:Completed”.
• Event Category: Events are grouped into categories, such as “Stored Procedure” and “Locks”. Multiple event categories can be selected for a single trace.
• Data Column: A data column is an attribute of an event class that is captured in the trace. It contains the value of the event class.
• Filter: Filters are used to selectively collect data in the trace. It is important to choose only the columns necessary for analysis to avoid creating a large and unwieldy trace.
• Trace: A trace captures data based on selected event classes, data columns, and filters. It can be saved in a database or as trace files.
• Template: A template defines the default configuration for a trace. Templates can be saved, imported, and exported between SQL Server instances. However, templates from different SQL Server versions are not compatible.

Starting the Profiler

To start SQL Server Profiler, you can use one of the following methods:

1. Go to Start > All Programs > Microsoft SQL Server > Performance Tools > SQL Server Profiler
2. In SQL Server Management Studio, go to Tools > SQL Server Profiler
3. For SQL Server 2008, type “profiler” in the command prompt
4. For SQL Server 2005, type “profiler90” in the command prompt

Collecting Data

Once the Profiler is started, you can connect it to any database. It is not limited to the local database and can connect to any database with appropriate authentication and system admin role.

To collect data, click on Menu > File > New Trace. This will prompt you to enter the trace name and display the ‘Trace Properties’ screen. Here, you can select a template, choose events to be captured, apply column filters, and organize columns.

After configuring the trace, click on the ‘Run’ button to start capturing data. The trace can be paused or stopped as needed. The captured data can be saved in a database, as trace files, or as XML. Trace files can be imported back into Profiler for further analysis.

Summary

SQL Server Profiler is a powerful tool for tracing and analyzing T-SQL commands and query resolution. While concerns about overhead and data complexity are valid, proper configuration and selective data capture can mitigate these issues. In the next article, we will explore filtering and organizing columns, as well as storing trace files in database tables and querying them using T-SQL.

If you have any doubts or questions regarding this article, please feel free to reach out to me at pinal@sqlauthority.com or through the contact form on my blog.