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