Viewing and Analyzing Traces

Viewing and Analyzing Traces

Use SQL Profiler to view captured event data in a trace. SQL Profiler displays data based on defined trace properties. One way to analyze Microsoft® SQL Server™ data is to copy the data to another program, such as SQL 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 database name when using the wizard, the captured data can be analyzed against a different server and database. For more information, see Index Tuning Wizard.

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

Note  SQL 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 Profiler display can be configured with customized font, font size, preview lines, and client buffer size to assist in trace analysis.

Analyzing Data to Troubleshoot

Using SQL Profiler, you can troubleshoot data, 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.

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

Here are the basic steps for finding and grouping events in a trace:

1. Create your trace.
   • When defining the trace, capture the Event Class, ClientProcessID, 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.
2. 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® Windows 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 Profiler to help you find values in the trace). Note the values in the ClientProcessID and Start Time data columns of the desired events.
3. Display the events in context.
   • Display the trace data column properties, and group by ClientProcessID instead of Event Class.
     
     
   • Expand the nodes of each client process 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 client process ID. For example, the Deadlock and Deadlock Chain events, captured within the trace, will be immediately after the SQL:BatchStarting events within the expanded client process 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 ClientProcessID, Application Name, or another event class to view related activity in chronological order.

To view a saved trace