927
Chapter 38: Using Profi ler and SQL Trace
38
Using SQL Trace
SQL Server Profiler is usually used interactively, and for ad hoc data gathering, this is
probably sufficient. However, running Profiler on a heavy transaction server can lead to
problems:■ (^) If Profiler can’t keep up, some events will be dropped. This frequently happens on
heavy transaction servers with Profiler.
■ (^) There’s a measurable performance impact on the server when running Profiler. The
heavier the transaction traffic on the server, the greater the percentage of perfor-
mance impact from Profiler.
■ (^) The workstation gathering the events may run out of memory.
The solution is to run the SQL Trace directly on the server without collecting the data using
the Profiler UI.
SQL Traces are started by the sp_trace_create system stored procedure. When the trace
exists, events are added to it using sp_tracesetevent.
Although you can code stored procedures to configure SQL Traces, the most common
method is to define the trace in Profiler and then script the trace to run on the server.
After the trace is set up and tested in SQL Server Profiler, select File ➪ Export ➪ Script
Trace Definition ➪ For SQL Server 2005–2012 to generate a T-SQL script that launches the
server-side trace.
Best Practice
For production systems running server-side traces, writing to a fi le is the best way to collect perfor-
mance data with the least overhead to the server.
To view all the traces currently running in the server, query sys.traces:
SELECT id, path, start_time, last_event_time,
event_count, dropped_event_count
FROM sys.traces t
Result (abbreviated to fit):
id start_time last_event_time event_count dropped_event_count
1 2011-08-27 03:07:49 2011-08-27 22:49:22 2770 NULL
2 2011-08-27 22:27:20 NULL 0 0
c38.indd 927c38.indd 927 7/31/2012 10:04:20 AM7/31/2012 10:04:20 AM
http://www.it-ebooks.info