Working with the SQL Profiler

Steven Warren discusses how to use the SQL Profiler to monitor, analyze, and tune SQL server.

In SQL Server 2000, the SQL Profiler can help you diagnose and fix SQL Server performance issues. This tool lets you trace events and play back the results, which can help you determine application and/or query problems, such as long-running queries or ineffective indexes. Let's take a look at how you can use the SQL Profiler to monitor, analyze, and tune your SQL server.

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

Click for larger image

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

SQL Profiler includes a set of templates you can begin using right away. After becoming fluent at working with SQL Profiler, you can create your own templates or modify existing ones. Profiler offers the following 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 have introduced the basics of the SQL Profiler, let's look at how you can use the utility. You can open SQL Profiler from the Start menu by choosing Programs | Microsoft SQL Server | Profiler. The Profiler window opens but has a blank interface. 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.

Click for larger image

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).

Click for larger image

Figure C.

Next, 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.

Click for larger image

Figure J.

Click for larger image

Figure K.

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: Existing 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:

Open Profiler from the Start menu.
Open the Trace that you saved to a file or a table.
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 do not have to single-step through the entire trace or file.

You should now have some common knowledge on how to work with the SQL Profiler. I recommend that you experiment with the different templates and create a few of your own. By tracing events and playing back the results, you will be able to quickly identify application and query problems.

» See All Articles by Columnist Steven S. Warren