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

Server-Side Trace Code Walk-Through
What are the benefits of getting familiar with the syntax and structure of generated code? A server-side
trace script generated by SQL Profiler is efficient, consistent, easy to maintain, and reusable. In the earlier
section, ''Using Profiler to Generate Server-Side Trace Script,'' you used SQL Profiler to generate the
server-side trace script and saved it to a file. Now you will open the file and examine the details of
the script.
The following is the entire code listing. The script only provides limited comments; fortunately, it is easy
to read. The script can be divided into the following sections: declaration and initialization, create a trace,
set trace events, set filters for trace events, start the trace, and display the trace. In the next section, you
will examine all the details.
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
declare @DateTime datetime
set @DateTime = '2007-05-14 09:15:00.000'
set @maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c: \ MyFolder \ MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output,
2,
N'c: \ temp \ myServerSideTraceOutput_CostlyQueries',
@maxfilesize,
@Datetime,
10
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on