Finding Problem Queries with SQL Profiler - Professional SQL Server 2005 Performance Tuning - page 98

Table 5-2

Scenario Suggested Event Classes What To Do

Monitoring
long-running
queries by
execution time

RPC:Completed
SQL:BatchCompleted

Capture all completed stored procedure calls
and Transact-SQL statements submitted to SQL
Server by clients.

Include all data columns in the trace, group by
Duration, and specify event criteria. For
example, specify that the Duration of the event
must be at least 500 milliseconds; you can
eliminate short-running events from the trace.
The Duration minimum value can be increased
as required.

Available template: TSQL_Duration

General
Performance
Tuning

RPC:Completed
SP:StmtCompleted
SQL:BatchCompleted
Showplan XML

Capture information about stored procedures
and Transact-SQL batch execution.

Trace output can be used as a workload to
Database Engine Tuning Advisor tool.

Available template: Tuning

Identify the cause
of a deadlock

RPC:Starting SQL:
BatchStarting Lock:Deadlock
Lock:Deadlock Chain
Deadlock Graph

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.

Blocking Blocked Process Report Include all data columns.

Audit login
activity

Audit Login Event Sessions Output data can be used for legal purposes to

document activity and for technical purposes to
track security policy violations.

Benchmark or
replay a trace
workload

CursorClose
CursorExecute
CursorOpen
CursorPrepare
CursorUnprepare
Audit Login
Audit Logout
Existing Connection
RPC Output Parameter
RPC:Completed
RPC:Starting
Exec Prepared SQL
Prepare SQL
SQL:BatchCompleted
SQL:BatchStarting

Use to perform iterative tuning, such as
benchmark testing.

If output data will be used for a trace replay,
capturing detailed information about
Transact-SQL statements will be required.

Available template: TSQL_Replay