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

environment variable TMP is set for the user's account. For example, if the TMP environment variable is
set to %USERPROFILE%\Local Settings\Temp, then the Profiler temporary files will be written to this
folder until the Profiler is closed.

If trace data needs to be collected regularly for other purposes (auditing, workload baseline, performance
tuning, or troubleshooting) then the server-side trace is the most efficient and low-cost approach.

OptionsofConfiguringSQLTraceOutputData
Table 5-3 shows five options where trace output data can be saved.

Table 5-3

Option SQL Trace Profiler Screen Trace Output File Trace Database Table

√

1 SQL Profiler

√ √

2 SQL Profiler

√ √

3 SQL Profiler

√ √ √

4 SQL Profiler

√

5 Server-Side Trace

Here are the options examined more closely:

❑ Option 1: Use SQL Profiler to trace without saving data: The Profiler collects events from an

instance of SQL Server locally (or remotely). Trace output data will be displayed on the SQL
Profiler screen as soon as a configured event class occurs on the connected instance of SQL server.
After a trace is started, trace events will be displayed on the Profiler screen in near real
time. This setup provides an excellent environment to learn SQL Profiler features and
become familiar with traceable events.
However, this option is not an efficient setup for tracing in a production environment with
high database transaction volumes. When a server is under resource stress, a server-side
trace is preferable to SQL Profiler to guarantee the collection of every event without
missing data.
In general, Profiler is an excellent choice for a non-production environment.

❑ Option 2: Use SQL Profiler to trace and save data to a file system: This setup has the same

benefitsanddrawbacksasoption1.Inaddition,option2savestracedatatoaWindowsfile
system. This adds overhead.

❑ Option 3: Use SQL Profiler to trace and save data to a database table: This setup has the same

benefits and drawbacks as option 1. In addition, it saves trace data to a database table for future
data analysis. However, this process adds overhead directly to the monitored SQL Server.

❑ Option 4: Use SQL Profiler to trace and save data to both the file system and the database

table: There might be special circumstances when this redundancy is useful. Otherwise, it is
not advised to use this option.