Viewing and Analyzing Traces

Use SQL Server Profiler to view captured event data in a trace. SQL Server Profiler displays data based on defined trace properties. Each line in the trace capture data describes an event in Microsoft® SQL Server™. One way to analyze SQL Server data is to copy the data to another program, such as SQL Server Query Analyzer or the Index Tuning Wizard. The Index Tuning Wizard can use a trace file that contains SQL batch and remote procedure call(RPC) events (and Text data columns). By specifying a server and/or database name when using the wizard, the captured data can be analyzed against a different server and/or database. For more information, see Index Tuning Wizard.

When a trace is opened using SQL Server Profiler, it is not necessary for the trace file to have the .trc file extension if the file was created by either SQL Server Profiler or the Profiler extended stored procedures.

Note SQL Server Profiler can also read SQL Trace .log files and generic SQL script files. When opening a SQL Trace .log file that does not have a .log file extension, for example trace.txt, specify SQLTrace_Log as the file format.

The SQL Server Profiler display can be configured with customized font, font size, preview lines, and client buffer size to assist in trace analysis.

Analyzing Data to Find Hotspots

Using SQL Server Profiler, hotspots, such as queries that perform poorly or have exceptionally high numbers of logical reads, can be found by grouping traces or trace files by the Duration, CPU, Reads, or Writes data columns. For example, to find out which objects are frequently opened, create a trace that captures Object:Opened events. Group the event data by Event Class, Server Name, Database ID, and Object ID to display counts of the Object:Opened events for each database object.

Additional information can be found by saving traces to tables and using Transact-SQL to query the event data. For example, to determine which SQL:BatchCompleted events had excessive wait time, execute:

SELECT TextData, Duration, CPU

FROM trace_table_name

WHERE EventClass = 12 -- SQL:BatchCompleted events

AND CPU < (.4 * Duration)

Displaying Object Names When Viewing Traces

If you capture the Server Name and Database ID data columns in your trace, SQL Server Profiler displays the object name instead of the object ID (for example, Orders instead of the number 165575628). Similarly, if you capture the Server Name, Database ID, and Object ID, SQL Server Profiler displays the index name instead of the index ID.

If you choose to group by the Object ID data column, group by the Server Name and Database ID data columns first, and then Object ID. Similarly, if you choose to group by the Index ID data column, group by the Server Name, Database ID, and Object ID data columns first, and then Index ID. You need to group in this way because object and index IDs are not unique between servers and databases (and objects for index IDs).

Finding Specific Events Within a Trace

The following example can be used to find and group events in a trace.

Create your trace normally.
- When defining the trace, capture the Event Class, Connection ID, and Start Time data columns in addition to any other data columns you want to capture.
- Group the captured data by the Event Class data column, and capture the trace to a file or table.
Find the target events.
- Open the trace file or table, and expand the node of the desired event class, for example, Deadlock Chain. (The file can be opened for viewing while the trace is writing to it unless the trace is located on a computer running Microsoft Windows® 95/98. Use the Refresh command in the View menu to display the new rows.)
- Search through the trace until you find the events for which you are looking (you can use the Find option on the Edit menu of SQL Server Profiler to help you find values in the trace). Note the values in the Connection ID and Start Time data columns of the desired events.
Display the events in context.
- Display the trace data column properties, and group by Connection ID instead of Event Class.
- Expand the nodes of each connection ID you want to view. Search through the trace manually, or use the Find option until you find the previously noted Start Time values of the target events. The events are displayed in chronological order with the other events that belong to each selected connection ID. For example, the Deadlock and Deadlock Chain events, captured within the trace, will be immediately after the SQL:BatchStarting events within the expanded connection ID.

The same technique can be used to find events grouped by Server Name, Database ID, and Object ID. Once you have found the events for which you are looking, group by Connection ID, Application Name, or another event class to view related activity in chronological order.

To view a trace

To view filter information

To open a trace file

Note If a trace file is located on a computer running Microsoft Windows 95/98, the trace file cannot be opened by SQL Server Profiler while the file is also being used to capture events. Additionally:

SQL Server Profiler, running on Windows NT, cannot open trace or script files located on a Windows 95/98 shared directory.
SQL Server Profiler can incur problems when accessing trace or script files located on remote computers if those files subsequently become unavailable.

To open a trace table