Working with the SQL Profiler - Page 3

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.

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

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.

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