Microsoft SQL Server 2012 Bible

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 performance 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 file is the best way to collect performance 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, ...