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'<save location of trace file>’, @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 ,0
6. To close the specified trace and delete its definition from the server us sp_trace_setstatus ,2
The events that you had configured while in the GUI would be present in the generated script and will be set using the Stored Prodecure sp_trace_setevent.
Remember that server side traces shouldn’t be writing to the same disk on which the SQL files are residing on and your profiler traces shouldn’t be writing to network drives/mapped drives or any other UNC shares. The destination file location should be a local disk. These are some recommendations that you need to keep in mind when configuring a server side profiler trace.
283790 INF: How to Create a SQL Server 2000 Trace
283786 How to monitor SQL Server 2000 traces