Common SQL Server Profiler Scenarios

The following scenarios describe monitoring tasks that are commonly performed using SQL Server Profiler:

• Monitoring Transact-SQL activity per user

  You can create a trace that captures events relating to Sessions event classes (Connect, Disconnect, and ExistingConnection) and TSQL event classes. Include all data columns in the trace, do not specify any event criteria, and group the captured events by SQL User Name.

  Use the Trace Transact-SQL activity by user option in the Create Trace Wizard to automatically create this trace definition.

• Finding the worst-performing queries

  You can create a trace that captures events relating to TSQL event classes, specifically RPC:Completed and SQL:BatchCompleted. Include all data columns in the trace, group by Duration, and specify event criteria, for example, that the Duration of the event must be at least 1,000 milliseconds. This event criteria eliminates short-running events from the trace. The Duration minimum value can be increased as required. If you want to monitor only one database at a time, specify a value for the Database ID event criteria.

  Use the Find the worst performing queries option in the Create Trace Wizard to automatically create this trace definition.

• Identifying the cause of a deadlock

  You can create a trace that captures events relating to TSQL event classes (RPC:Starting and SQL:BatchStarting) and Locks event classes (Lock:Deadlock and Lock:Deadlock Chain). Include all data columns in the trace and group by Event Class. If you want to monitor only one database at a time, specify a value for the Database ID event criteria.

  Use the Identify the cause of a deadlock option in the Create Trace Wizard to automatically create this trace definition.

  To view the connections involved in a deadlock, either:

  • Open the trace containing the captured data, group the data by Connection ID, and expand both connections involved in the deadlock.
  • Save the captured data to a trace file, and open the trace file twice using SQL Server Profiler to make the file visible in two separate windows. Group the captured data by Connection ID and then expand the connection ID involved in the deadlock; each deadlocked connection is in a separate window. Tile the windows to view the events that cause the deadlock.
• Monitoring stored procedure performance

  You can create a trace that captures events relating to Stored Procedures event classes (SP:Completed, SP:Starting, SP:StmtCompleted and SP:StmtStarting), and TSQL event classes (SQL:BatchStarting and SQL:BatchCompleted). Include all data columns in the trace, and group by Connection ID. If you want to monitor only one database at a time, specify a value for the Database ID event criteria. Similarly, if you want to monitor only one stored procedure at a time, specify a value for the Object ID event criteria.

  Use the Profile the performance of a stored procedure option in the Create Trace Wizard to automatically create this trace definition.

Generally, when monitoring Microsoft® SQL Server™ using SQL Server Profiler, only monitor the event classes in which you are interested. Monitoring too many event classes adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when monitoring over a long period of time.

To create a trace using the Create Trace Wizard

See Also