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

SQL Trace Options and Considerations
SQL Server provides many options to choose from when you trace database events. Some of these
are beneficial for specific use case scenarios. Using a trace in a production environment is sensitive to
how much system resources the trace will consume. The following sections discuss and compare some
of the trace options available. Understanding these options will help you develop and optimize your
trace solutions.

SQLProfilerandServer-SideTrace
There are two ways to collect event data. The most common way is to use a SQL Profiler trace; the second
way is to use a server-side trace. Understanding the differences between the two and applying them to
suitable environments can effectively reduce overhead and increase the quality of data collection.

❑ Overhead: SQL Profiler is a client Windows application that has a graphical user interface. When

Profiler starts, it internally executes a set of system-stored procedures to create traces. After a
trace event occurs, Profiler takes the event from the SQL Trace queue and displays the
results of it.

❑ Server-side trace does not have a display feature; it moves events from the queue to file using

buffered I/O. Since server-side trace does not require a display feature, it is the most efficient
and low-cost way to collect SQL event data.

❑ Ease of use: SQL Profiler provides a user-friendly and real-time monitoring environment. To use

the server-side trace, a written application or script is required. The server-side trace is not as
easy as Profiler is.

❑ Efficiency: On a busy server, Profiler cannot guarantee the collection of every single event unless

you save the data to a file on the server side and enable the server processes trace data option.
Since the server-side trace does not display events during the data collection, it is more efficient.

❑ Reusability: The reusable parts of SQL Profiler are the pre-defined templates and user-defined

templates. The server-side trace offers only a programming environment to create customized
reusable scripts.

❑ Schedule to start and stop: SQL Profiler command utility ( Profiler90.exe ) has a feature to set

a stop time for a Profiler trace; however, Microsoft documentation of the utility mentions that
Profiler90.exe is not intended for scripting traces. The server-side trace can be scheduled to
start and stop by using the system-stored production sp_trace_setstatus.

SQL Server 2005 provides a system view sys.traces that maintains detailed data for each live trace. In the
following code example, the script pulls information from the sys.traces.

USE MASTER
GO
SELECT
ID, -- Trace ID

STATUS, -- Trace Status, 0 = stopped, 1 = running

IS_ROWSET, -- 1 = rowset trace.

BUFFER_COUNT, -- Number of in-memory buffers used by the trace.

BUFFER_SIZE, -- Size of each buffer (KB).

EVENT_COUNT, -- Total number of events that occurred.

PATH, -- Path of the trace file. Null means rowset trace.

MAX_SIZE -- Maximum trace file size limit in megabytes (MB).