Collecting Server Side Profiler Traces

Posted on by

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.

Useful Articles

283790 INF: How to Create a SQL Server 2000 Trace

283786 How to monitor SQL Server 2000 traces

9 thoughts on “Collecting Server Side Profiler Traces”

  1. Pingback: How to create a Profiler Trace during Server startup « Troubleshootingsql's Blog

  2. Wonderful article. Helped me greatly in setting up trace and starting automatically after a server is down.

    Like

  4. I try to start my trace automatically after a server is down by turning my SP on with below command.

    sp_procoption @ProcName = ‘MySP’,
    @OptionName = ‘startup’,
    @OptionValue = ‘on’

    The problem is that I don’t know the proc name.

    Like

  5. You would need to find the name of the Stored Procedure which you created. If you know under which database you created the stored procedure, then run the following T-SQL statement to find out all the Stored Procedures that were created recently. The T-SQL query is:

    select name,create_date
    from sys.objects
    where is_ms_shipped 1
    and type =’P’
    order by create_date desc

    Once you have the Stored Procedure names, you can get the stored procedure definition using sp_helptext ”

    Like

  6. –I ran this
    use master
    go

    SELECT name,create_date
    FROM sys.objects
    WHERE is_ms_shipped 1
    AND type = ‘P’
    ORDER BY create_date desc

    — got this error
    Msg 102, Level 15, State 1, Line 4
    Incorrect syntax near ‘1’.

    Like

  8. WordPress is removing the NOT EQUAL TO sign:
    SELECT name,create_date
    FROM sys.objects
    WHERE is_ms_shipped 1
    AND type = ‘P’
    ORDER BY create_date desc

    Alternate query:
    SELECT name,create_date
    FROM sys.objects
    WHERE type = ‘P’
    ORDER BY create_date desc

    Like

  9. Pingback: Easing in the trace FILTER for SQLDIAG « TroubleshootingSQL

Comments are closed.