Data Management

Configure IT Quick: Use SQL Profiler to diagnose SQL Server performance issues

Use Profiler templates, event traces, and other monitoring tools to fix problems with your SQL server.


SQL Profiler can help you diagnose SQL Server performance issues quickly and easily. This graphical tool lets you monitor SQL Server events based on criteria you choose. You can use it to trace events and then play back the results, which can help you determine application and/or query problems, such as long-running queries or ineffective indexes. Here's a look at how you can use the SQL Profiler to monitor, analyze, and tune your SQL server.

SQL Profiler basics
When you open SQL Profiler (Figure A), you can run traces on a set of criteria you specify.

Figure A


These criteria or events can be one or more of the following:
  • Cursors
  • Database
  • Errors and Warnings
  • Locks
  • Objects
  • Performance
  • Scans
  • Security Audit
  • Server
  • Stored Procedures
  • Transactions
  • TSQL

Profiler includes a set of templates you can begin using immediately. After becoming adept at working with Profiler, you can create your own templates or modify existing ones. Profiler offers the eight templates shown in Table A.

Table A
Template name Template filename Definition
Stored Procedure Counts SQLServerProfilerSP_Counts This template provides a collection of events about the Stored Procedure name that has started. The results of this trace are grouped by Event Class, Server Name, Database ID, and Object ID. In addition, the Server Process ID (SPID) is trapped.
Standard SQLServerProfilerStandard.tdf This template provides detailed information about Security Audits (Audit Login/Audit Logout), Sessions (Existing Connection), Stored Procedures, and TSQL Statements that have been completed.
TSQL SQLServerProfilerTSQL This template traps TSQL Statements in the order that they occur. The results include the Event, SQL Statement, SPID, and Start Time.
TSQL Duration SQLServerProfilerTSQL_Duration This template traps the TSQL Statements, Event Class, SPID, and the time it takes in milliseconds for the TSQL Statement to execute.
TSQL Grouped SQLServerProfilerTSQL_Grouped This template groups your TSQL Statements by Application Name, NT User Name, Login Name, and the Client Process ID of the application being called by SQL Server.
TSQL Replay SQLServerProfilerTSQL_Replay This template traps detailed information of the TSQL Statements that have been issued and enables you to replay the trace.
TSQL Stored Procedures SQLServerProfilerTSQL_SPs This template traps the Stored Procedures and TSQL commands associated with that Stored Procedure. This trace also traps the SPID and start time of the Stored Procedure.
Tuning SQLServerProfilerTuning This template traps TSQL Statements and SQL Batches that have completed.

Now that we've introduced the basics of the SQL Profiler, let’s look at how you can use the utility.

Tracking events with SQL Profiler
You open SQL Profiler from the Start menu by choosing Programs | Microsoft SQL Server | Profiler. The Profiler window opens but has a blank interface, as you saw in Figure A. You must specify the data you want to trap before Profiler begins collecting information. From the File Menu, select New Trace and then provide your SQL Server authentication information, as shown in Figure B.

Figure B


Once you provide your authentication info, you can configure your trace options. In the General tab, you enter a Trace Name and choose a template file (Figure C).

Figure C


Furthermore, you can save your trace to a file or a table, and you can specify when you want the trace to stop. In the Events tab, shown in Figure D, you can use the template's default trace events or add your own.

Figure D


After you set your events, you can configure your Data Columns and Filters as shown in Figure E and Figure F. After all of your options are configured, click Run to begin your trace. If you used an existing template and modified it, you can save your new trace as a new template to be used again.

Figure E


Figure F


Now let's turn our attention to some common trace scenarios. Suppose you have long-running queries you need to troubleshoot. In that case, a common trace would be TSQL-SQL:BatchCompleted (Figure G). Grouping by the Duration column would allow you to find your longest-running queries (Figure H).

Figure G


Figure H


After running the trace, you can view the longest-running queries, which would be a good starting place for troubleshooting your performance problems (Figure I).

Figure I


Another example would be a trace that looks at third-party applications or people who use excessive resources. To trap this information, use Sessions:ExistingConnection and TSQL-SQL:BatchCompleted and then group by CPU, Reads, and Writes, as shown in Figures J and K.

Figure J


Figure K


System monitoring with SQL Profiler
In addition to Tracing SQL Server events, you can use the SQL Profiler for system monitoring. When using Profiler as a system monitor, trace the following:
  • Sessions:Exisiting Connection
  • Errors and Warnings:Error Log
  • Errors and Warnings: Event Log
  • Security Audit: Audit Login Failed

Once you have the output, you can save the results to a file or a table for later viewing.

Another key feature of Profiler is its ability to debug SQL Server transactions and stored procedures by trapping and replaying SQL statements. Let’s explore this a bit further.

To capture a SQL statement for replay, open Profiler and choose the SQL Profiler TSQL_Replay template. After you run the trace, save it to either a file or a table. Your next step is to open the saved trace by choosing File | Open | Trace File or Trace Table and enter your connection information and your replay SQL Server options (Figure L).

Figure L


Next, choose Step from the Replay menu. You can also choose the following:
  • Step—This steps you through each TSQL statement one at a time.
  • Start—This replays the entire trace.
  • Run To Cursor—This runs the trace to the where the cursor is currently highlighted.
  • Pause—This pauses the trace.
  • Stop—This stops the trace.
  • Toggle Break-Point—This allows you to specify a break point in your trace.

To specify a break point:
  1. Open Profiler from the Start menu.
  2. Open the Trace that you saved to a file or a table.
  3. Highlight a SQL statement, as shown in Figure M, and choose Toggle Break-Point from the Replay menu. A red circle will appear to the left of the SQL statement you selected.

Figure M


Specifying break points throughout your trace will let you stop at the predefined spots along the way, so you don't have to single-step through the entire trace or file.

Summary
Now you know a little bit about how SQL Profiler works and how to run various traces. To become an expert using this tool, you should experiment with the different templates and create a few of your own. By tracing events and playing back the results, you'll be able to quickly identify application and query problems.

Editor's Picks