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

Table 5-1

Event Number Event Name Description

0-9 Reserved

10 RPC:Completed Occurs when a remote procedure call (RPC) has completed.

11 RPC:Starting Occurs when an RPC has started.

12 SQL:BatchCompleted Occurs when a Transact-SQL batch has completed.

13 SQL:BatchStarting Occurs when a Transact-SQL batch has started.

14 Audit Login Occurs when a user successfully logs in to SQL Server.

15 Audit Logout Occurs when a user logs out of SQL Server.

❑ Trace file: A trace file is an output file created by SQL Trace. Either SQL Profiler or a server-

side trace script can create this file.

❑ Trace table: A trace table refers to a database table into which the trace data will be saved. From

SQL Profiler, you can configure trace data to be saved into a specific database table.

❑ Filter: When creating a trace or template, you must define criteria to filter the data collected by

the event. To keep traces from becoming too large, filter them so that only a subset of the event
data is collected. For example, filter user names to a specific group of users, thereby reducing
the output data. If a filter is not set, all events of the selected event classes are returned in the
trace output (file, table, or screen).

❑ Template: A template defines the default configuration for a trace. The template includes the

event classes that need to be monitored by SQL Server Profiler. For example, create a template
that specifies which events, data columns, and filters to use. A template is not executed, but
rather it is saved as a file with a .tdf extension. Once saved, the template controls the trace data
that is captured when a trace based on the template is launched.

❑ Common trace scenarios: Table 5-2 contains common trace scenarios with their suggested

event classes.

Best Practice
Try to minimize the system impact by selecting only the trace events needed. A catch-all
trace will slow down the overall system performance, consume unnecessary disk space,
and add complexity to the task that processes the trace output data.
If you are working with another party and are instructed to collect a large set of trace
data, be sure to get an estimate on the overall system impact first.