SQL Profiler Scenarios

Typically, you use SQL Profiler to:

• Find the worst-performing queries

  For example, you can create a trace that captures events relating to TSQL and Stored Procedure event classes, specifically RPC:Completed and SQL:BatchCompleted. Include all data columns in the trace, group by Duration, and specify event criteria. For example, if you specify that the Duration of the event must be at least 1,000 milliseconds, you can eliminate 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.

• Identify the cause of a deadlock

  For example, you can create a trace that captures events relating to TSQL and Stored Procedure 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.

  To view the connections involved in a deadlock, do one of the following:

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

  For example, 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 ClientProcessID. 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.

• Audit Microsoft® SQL Server™ activity

You can audit activity in SQL Server using SQL Profiler. For example, if the security administrator always needs to know who is logged in to the server, you can create a SQL Profiler trace that provides a complete view of users who have logged in or out of the server. This information can then be used for legal purposes to document activity and for technical purposes to track security policy violations.

To set up a SQL Profiler trace that tracks users who have logged in or out of the server, do the following:

1. Create a trace, selecting Audit Login Event.
   
   
2. To return the appropriate information, specify the following data columns:

   EventClass (selected by default)

   EventSubClass

   LoginSID

   LoginName

• Monitor Transact-SQL activity per user.

  You can create a trace that captures events relating to the Sessions event class, ExistingConnection, and TSQL event classes. Include all data columns in the trace, do not specify any event criteria, and group the captured events by DBUserName.

See Also

Locks Event Category

Sessions Event Category

Stored Procedures Event Category

TSQL Event Category