Printed 2017/08/20 12:40AM

SQL Server : Setting up Server Side Trace

By nelsonaloor, 2012/10/02

For a DBA, SQL profiler is a great tool to trouble shoot performance issues. It is a pain to configure a  trace and analyse it using the profiler GUI . I usually use server side trace which can be configured by running set of scripts. Server side traces are much lighter as it does not need to transfer data over the network.In this post, let us discuss about configuring server side trace.

There are three steps in creating server side trace. 
  1. Defining a trace.
  2. Defining the events and columns to captured.
  3. Defining the filter condition.
  4. Start the trace.
Please find below the script used to configure a server side trace.Refer BOL for more option about adding events,columns and filter condition.

     DECLARE @OutputFileName NVARCHAR(256)
   SET @MaxFileSize = 1024

--Replace The H:\MyTraces with a valid folder in your environment
SET @OutputFileName = 'D:\MyTraces\FileTrace' +     CONVERT(VARCHAR(20), GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20), GETDATE(),108),':','')

--sp_trace_create @traceid,@options,@tracefile,@maxfilesize,@stoptime ,@filecount 

   EXEC @rc = sp_trace_create @TraceID OUTPUT, 2, @OutputFileName, @MaxFileSize, NULL,5
DECLARE @Status bit

   SET @Status = 1
--sp_trace_setevent @traceid ,@eventid ,@columnid,@on 

   --RPC:Completed event

   EXEC sp_trace_setevent @TraceID, 10, 16, @Status 
   EXEC sp_trace_setevent @TraceID, 10, 1,  @Status
   EXEC sp_trace_setevent @TraceID, 10, 17, @Status
   EXEC sp_trace_setevent @TraceID1014@Status
   EXEC sp_trace_setevent @TraceID, 10, 18, @Status
   EXEC sp_trace_setevent @TraceID, 10, 12, @Status
   EXEC sp_trace_setevent @TraceID, 10, 13, @Status
   EXEC sp_trace_setevent @TraceID, 10, 8,  @Status
   EXEC sp_trace_setevent @TraceID, 10, 10, @Status
   EXEC sp_trace_setevent @TraceID, 10, 11, @Status
   EXEC sp_trace_setevent @TraceID, 10, 35, @Status

   --SQL:BatchCompleted event

   EXEC sp_trace_setevent @TraceID, 12, 16, @Status
   EXEC sp_trace_setevent @TraceID, 12, 1,  @Status
   EXEC sp_trace_setevent @TraceID, 12, 17, @Status
   EXEC sp_trace_setevent @TraceID, 12, 14, @Status
   EXEC sp_trace_setevent @TraceID, 12, 18, @Status
   EXEC sp_trace_setevent @TraceID, 12, 12, @Status
   EXEC sp_trace_setevent @TraceID, 12, 13, @Status
   EXEC sp_trace_setevent @TraceID, 12, 8,  @Status
   EXEC sp_trace_setevent @TraceID, 12, 10, @Status
   EXEC sp_trace_setevent @TraceID, 12, 11, @Status
   EXEC sp_trace_setevent @TraceID, 12, 35, @Status


    STEP 3 : DEFINING THE Filter condition
   --sp_trace_setfilter @traceid ,@columnid,@logical_operator,@comparison_operator,@value
   EXEC sp_trace_setfilter @TraceID,8,0,0,N'MyAppServer'  --Hostname
   EXEC sp_trace_setfilter @TraceID,35,0,0,N'MyDB' --Database name
   EXEC sp_trace_setfilter @TraceID,11,0,0,N'MyAppUser' --SQL login
    STEP 4 : Start the trace
   EXEC sp_trace_setstatus @TraceID, 1
     Display the trace Id and traceFilename
   SELECT @TraceID,@OutputFileName

Now the trace is running and you can verify the currently running trace using the below query

SELECT * FROM ::fn_trace_getinfo(NULL)

Once it ran for the desired time , you can stop the trace using the below script

   --sp_trace_setstatus [ @traceid = ] trace_id , [ @status = ] status
DECLARE @traceid INT

    DECLARE @status INT
    SET @traceid =2
    SET @status =0
    EXEC sp_trace_setstatus @traceid,@status
    SET @status =2
    EXEC sp_trace_setstatus @traceid,@status

To view the content of the trace file. If you have added more column in the trace , add that column in the below select statement also.You can insert the output into a table for further analysis of the trace.

TextData, Duration/1000, Reads, Writes, CPU, StartTime,HostName,ApplicationName,LoginName,DatabaseName
FROM fn_trace_gettable('D:\MyTraces\FileTrace20120929023408.trc',1)
Below query will help us to list the events and columns capturing as part of a trace.

t.ColumnID AS Event_Description AS Column_DescriptionFROM ::fn_trace_geteventinfo(2) t   --Change the trace id to appropriate one 
JOIN sys.trace_events e ON t.eventID = e.trace_event_id
JOIN sys.trace_columns c ON t.columnid = c.trace_column_id

If you liked this post, do like my page on FaceBook

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.