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

❑ Option 5: Use server-side trace and save data to a file system: If monitoring real-time events is

not required, a server-side trace provides the most efficient way to collect events for database
performance tuning and troubleshooting. Server-side trace handles trace output data to a file
using buffered I/O.
A server-side trace can be executed manually or automatically from a scheduled SQL
batch job.

ImpactsonWheretoStartProfilerandFileOptions
SQL Profiler can be connected to a monitored server locally or remotely. Where to start Profiler Trace and
how to save the results will be determined by the following two important factors:

❑ SQL Profiler overhead impact on the monitored SQL Server

❑ Missing trace events' impact on trace data collection

SQL Profiler is nothing more than a GUI front end that invokes a set of T-SQL functions and system
procedures collectively. When you start SQL Profiler, you start a Windows client application with
the process name PROFILER90.exe . The overhead of each running PROFILER90.exe can be measured
by the System Performance monitor. To minimize Profiler trace overhead on the SQL Server to be
monitored, start your Profiler from a remote server (or workstation).

To ensure the collection of all trace events without missing data, select the option Server Processes Trace
Data. (Later, in the Setup section, you will see in Figure 5-15 a check box named Server Processes
Trace Data located in the middle section of the Trace Properties screen.) This option requires you to
create a file (or rollover files) on the SQL Server that is being monitored. This way, even when the server
processes trace data under stressful conditions, no events will be skipped. However, server performance
may be affected.

Table 5-4 shows common configurations, pros, and cons.

SelectingaDataCollectionTimeframe
Since collecting event data adds overhead to the system, targeting a specific timeframe for a clearly
defined purpose will help to minimize system overhead.

For troubleshooting scenarios, if you have a known timeframe when the performance issue occurred, you
can target that specific timeframe. Otherwise, try short trace intervals.

In collecting event data for baseline purposes, consider the purpose and the frequency. Be specific.
Table 5-5 provides examples.

DurationColumnConfiguration
In the previous versions of SQL Profiler, the Duration column was measured in milliseconds regardless
of whether the duration data was displayed on the Profiler screen, saved to an output file, or saved to a
database table. However, in SQL 2005 Profiler, if a trace is saved to either a file or a database table, this
column is defaulted to microseconds (one millionth, or 10 − 6 , of a second). It is important to recognize
that in SQL Server 2005, while saving the trace output file, the measurement of the Duration column is
microseconds and not milliseconds (as it was with previous versions). Without knowing about this new
feature, your analysis for the Duration data may potentially be inaccurate.