Data Management optimize

Step-By-Step: An introduction to SQL Server Profiler

Discover how SQL Server Profiler works

SQL Profiler is a tool that captures SQL Server events from the server and saves those events in what's known as a trace file. You can then analyze or use the trace file to troubleshoot logic or performance problems. You can use this utility to monitor several areas of server activity, such as:
  • Analyzing and debugging SQL statements and stored procedures.
  • Monitoring slow performance.
  • Stress analysis.
  • General debugging and troubleshooting.
  • Fine-tuning indexes.
  • Auditing and reviewing security activity.

For the most part, Profiler is an administrative tool that requires a bit of experience to master. (Use Profiler to monitor only the events you're troubleshooting or analyzing.) Fortunately, Profiler provides a graphical interface, which makes both learning and monitoring much simpler. However, Profiler requires 10 MB of free space; if free space falls below 10 MB, Profiler stops.

To access Profiler, you must be the administrator or have permission to connect to a specific instance of SQL Server and have granted permissions to execute Profiler stored procedures.

Working with Profiler will introduce some terms with which you may be unfamiliar. In a nutshell, you create a template or use an existing template that defines the data you want to collect. Then, you actually collect the data by running a trace on the events defined in your template. During the run, Profiler displays the event classes and data columns that describe the event data being collected. The following list of definitions should help you through the learning process.

Use a template to define the criteria for each event you want to monitor. You don't actually execute a template. Rather, a template is used by a trace.

The trace does the actual data capture, based on the events you defined in the template.

An event is an action generated by the SQL Server engine, such as a login connection or the execution of a T-SQL statement. Events are grouped by event categories. All the data generated by an event is displayed in the trace, which contains columns of data that describe in detail the event.

Event Class
One of the data columns in a trace—this particular column describes the event.

Data Column
Another data column in a trace—this column describes the type of data collected.

Let's get started
Let's launch Profiler and take a look. Profiler is available from the Microsoft Windows Start menu or SQL Server Enterprise Manager. Use either of the following methods to launch Profiler:
  • From the Start menu, locate Microsoft SQL Server among your available programs and then click Profiler from the SQL Server group.
  • In Enterprise Manager, choose SQL Profiler from the Tools menu.

You're met by, for the most part, a blank screen. From the File menu, choose New, select Trace from the submenu, identify the appropriate SQL Server instance, and click OK. Use the resulting Trace Properties dialog box and its four tabs to initiate the process:
  • Use the General tab, shown in Figure A, to identify specific trace properties, such as name and location.
  • The Events tab lets you identify the events you want to trace.
  • The Data Column tab lets you specify the data the trace returns about a specific event.
  • Use the Filters tab to narrow the trace to a specific file(s).

Figure A
Identify the trace file.

To continue with the actual example, refer to Figure A to set the following options on the General tab:
  • Name the trace and identify the server on which you'll run the trace—the Trace SQL Server property defaults to the instance identified earlier.
  • Use the Template Name control's drop-down list to choose one of the available templates. If you create a template, be sure to specify the path to that file (tdf extension). You can add a default template via the Options menu off the Tools menu.
  • Save the trace to a file, reducing overhead on the server. Selecting this option enables the two check boxes immediately below: The Enable File Roller option permits you to open a new file for the trace once the original file is full, and Server Processes SQL Server Traces Data indicates whether the server or the client application should perform the trace. Performing lengthy complicated event tracing on the server can reduce performance.
  • Saving the trace to a table is an alternate to saving the trace to a file. This again can have performance implications on a busy server. Specifically, a table trace requires more overhead.
  • The final option, Enable Trace Stop Time, allows you to determine when the trace ends.

Next, click the Events tab to set the event classes and events you want to trace. In Figure B, we've selected T-SQL Event and all its classes. Double-click an event class in the Available Event Classes control to move it to the Selected Event Classes control to the right. (The User Configurable event selected in Figure B is simply the result of the cursor choosing the next item after moving TSQL.)

Figure B
Select an event class.

After selecting the event classes you want to trace, click the Data Columns tab and specify the data to be exposed in the trace. As before, simply move items from the Unselected Data control to the Select Data control. Figure C shows this tab running a trace against the Northwind Access Data Project with most of the default items removed (highlight an item and click Remove). In addition, we added the DatabaseName item and a few other simple items (select an item and click Add).

Figure C
Identify the data you want to see in the trace.

Now you're ready to specify the file (or files) you want to trace. For instance, in this example, we'll narrow the trace to just the NorthwindCS project file. Click the Filters tab, expand the DatabaseName item, and add Northwind to the Like option, as shown in Figure D.

Figure D
Limit the trace to the NorthwindCS project file.

When you're ready to run the trace, click the Run button. Figure E shows the results of running the example trace when the NorthwindCS Project opens and executes a stored procedure. This trace returns the user name, the application name, and all executed T-SQL statements.

Figure E
Review the results for problems.

Using the results
The simple example in this article doesn't provide us with a lot to analyze. However, in a real trace, you'll find a lot of useful information that should help you pinpoint a particular problem, whether it's slow performance, security, or even programming logic.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database and Web technologies. Martin W. P. Reid is an analyst at Queens University Belfast and has been working with databases for several years. They coauthored SQL: Access to SQL Server, by Apress. Susan's latest books are Mastering Dreamweaver MX Databases, by Sybex and Absolute Beginner's Guide to Microsoft Access 2002, by Que. Martin's latest book is Beginning Access 2002 VBA, by WROX.


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.