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

48b2-9901-b4091f703729'
AND Duration > = 2000000
AND CPU > =50

Once event classes and data columns are chosen and the data columns are applied, the next step is to start
the trace. Again, a stored procedure is used. The sp_trace_setstatus procedure modifies the current
state of the specified trace.

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

❑ @TraceID: It is the ID of the trace to be modified.

❑ 1: It specifies the action to the trace. The value 1 means to start the specified trace.

The last select statement is used to display the trace ID number.

-- display trace id for future references
select TraceID=@TraceID
goto finish

error:
select ErrorCode=@rc

finish:
go

To list all the traces on an instance of SQL Server, you can use the either of following two queries:

select * from ::fn_trace_getinfo(null)
select * from sys.traces

In a SQL Server 2005 instance, the SQL default trace has an ID of the number 1. The default trace provides
troubleshooting assistance to database administrators by ensuring that they have the log data necessary
to diagnose problems (see SQL Books Online for details). In SQL Server 2000, a similar trace is called
Blackbox trace.

If SQL Profiler is used to generate a server-side trace for two different scenarios and the generated codes
are compared, some commonalities will appear in the code logic and structures. There are many ways to
customize previously generated codes to make them more reusable and meet your specific needs.

Best Practice
In a production environment, use server-side traces to collect data. A server-side
trace consumes fewer system resources than a Profiler trace. Develop a set of reusable
server-side traces if you can.