When we want to monitor the kind of queries that hit across your SQL Server, most of us set up a Profiler Trace. But what most of us are not aware of is that there are two ways to setup a Profiler trace:
1. A GUI based Profiler trace
2. A Server side Profiler trace
A GUI based profiler trace causes a performance impact on the server as high as 25% depending on the events being captured. So if you are monitoring SQL Server with the help of profiler traces on a long term basis then it would always be advisable to setup a server side profiler trace.
Steps to setup a Server side Profiler Trace
1. Create a Profiler Trace using the GUI and then script out the trace to a file called say sql_trace.sql. Open the SQL_TRACE.SQL file in query analyzer/management studio query window and make the following changes
2. In the line “exec @rc = sp_trace_create @TraceID output, 2, N'', @maxfilesize, NULL”, change the location where you want to store the trace file.
3. Make sure @maxfilesize parameter is set to 350 or a desirable size. (This makes sure that you roll over to the next trace file once the current size hits 350 MB)
4. Execute the script to create the trace. This will generate a Trace ID. The trace information can be obtained from fn_trace_getinfo function
5. To stop the trace, use sp_trace_setstatus
6. To close the specified trace and delete its definition from the server us sp_trace_setstatus
283790 INF: How to Create a SQL Server 2000 Trace
283786 How to monitor SQL Server 2000 traces