Scenarios for Using SQL Server Profiler
The first step in using SQL Server Profiler is to identify your reasons for monitoring an instance of SQL Server. This topic discusses the typical scenarios where you can use SQL Server Profiler to gather trace information.
Typical scenarios for using SQL Server Profiler include:
- Find the worst-performing queries.
For example, you can create a trace that captures events that relate to the TSQL and Stored Procedure event classes (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 10000 micro-seconds, you can eliminate events of shorter duration 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 criterion. - Identify the cause of a deadlock.
For example, you can create a trace that captures events that relate to TSQL and Stored Procedure event classes (RPC:Starting and SQL:BatchStarting) and Locks event classes (Deadlock graph, Lock:Deadlock or 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 criterion. If you specify the Deadlock graph event class, SQL Server Profiler produces a graphical representation of the deadlock. For more information,see Analyzing Deadlocks with SQL Server Profiler.
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 Server 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 that are causing the deadlock. If you wish to save specific deadlock graph data to a file, right-click the deadlock event and select Extract Event Data.
- Monitor stored procedure performance.
For example, you can create a trace that captures events that relate to Stored Procedure event classes (SP:Completed, SP:Starting, SP:StmtCompleted and SP:StmtStarting), and TSQL event classes (SQL:BatchStarting and SQL:BatchCompleted). Include all necessary data columns in the trace and group by ClientProcessID. If you want to monitor only one database at a time, use the Database ID filter to specify a value for the Database ID event criterion. Similarly, if you want to monitor only one stored procedure at a time, use the Object ID filter to specify a value for the Object ID event criterion. - Audit SQL Server activity.
For example, if the security administrator always needs to know which users are logged on to the server, a SQL Server Profiler trace can provide a complete record of users that have logged on or off the server. This information can then be used for legal or technical purposes.
Create a trace, selecting the Audit Login event. To return the appropriate information, specify the following data columns: EventClass (selected by default), EventSubClass, LoginSID, LoginName. - Monitoring Transact-SQL activity per user.
You can create a trace that captures events relating to the Sessions, 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. - Collect a representative sample of events for stress testing.
SQL Server Profiler provides a predefined TSQL_Replay template that can be used for iterative tuning, such as benchmark testing. - Collect a sample of events for tuning the physical database design by using Database Engine Tuning Advisor.
SQL Server Profiler provides a predefined Tuning template that gathers the appropriate Transact-SQL events in the trace output so it can be used as a workload for Database Engine Tuning Advisor.
See Also
Reference
SQL Server Event Class Reference
Concepts
Detecting and Ending Deadlocks
Other Resources
Tuning the Physical Database Design