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