Using SQL Server Profiler, you can troubleshoot data by grouping traces or trace files by the Duration, CPU, Reads, or Writes data columns. Examples of data you might troubleshoot are queries that perform poorly or that have exceptionally high numbers of logical read operations.

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 the following:

SELECT  TextData, Duration, CPU
FROM    trace_table_name
WHERE   EventClass = 12 -- SQL:BatchCompleted events
AND     CPU < (Duration * 1000)

Note:

Beginning with SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second). In SQL Server 2000, the server reported both duration and CPU time in milliseconds. In SQL Server 2005 and later, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.

If you wish to display the name of an object rather than the object identifier (Object ID), you must capture the Server Name and Database ID data columns along with the Object Name data column.

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

To find and group events in a trace, follow these steps:

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. For more information, see How to: Create a Trace (SQL Server Profiler).
     
   • Group the captured data by the Event Class data column, and capture the trace to a file or table. To group the captured data, click Organize Columns on the Events Selection tab of the Trace Properties dialog box. For more information, see How to: Organize Columns Displayed in a Trace (SQL Server Profiler).
     
   • Start the trace and stop it after the appropriate time has passed or number of events have been captured.
     
2. Find the target events.
   
   • Open the trace file or table, and expand the node of the desired event class; for example, Deadlock Chain. For more information, see How to: Open a Trace File (SQL Server Profiler) or How to: Open a Trace Table (SQL Server Profiler).
     
   • Search through the trace data until you find the events for which you are looking (use the Find command on the Edit menu of SQL Server Profiler to help you find values in the trace). Note the values in the ClientProcessID and Start Time data columns of the events you trace.
     
3. Display the events in context.
   
   • Display the trace properties, and group by the ClientProcessID data column rather than by the Event Class data column.
     
   • Expand the nodes of each client process ID you want to view. Search through the trace manually, or use Find 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, appear immediately after the SQL:BatchStarting events within the expanded client process ID.
     

The same technique can be used to find any grouped events. Once you have found the events you seek, group them by ClientProcessID, Application Name, or another event class to view related activity in chronological order.

Tasks

Other Resources

Help and Information