SQL Server Server Side Trace

What is the Server Side Trace?

While the SQL Profiler (using the GUI) is a great tool, it does come at a cost to performance.  To circumvent the resource utilization, we can use the Server side Trace to capture the same data as the SQL Profiler.  The Server Side Trace actually executes the TSQL scripts on the server rather than using the GUI.  And this is a better alternative to the SQL Profiler.

The benefits of using SST allows us to monitor:

·      long-running traces

·      Reduce network bandwidth as the script is executed on the server

·      can automate the script with a job

·      Easy customizable scripts

There are basically four components to this to get this running:

  • sp_trace_create – this procedure creates the trace and has 5 parameters
    • TraceID – the ID of the trace
    • Options – various options that can be set
    • TraceFile – physical file name where you want to write the trace file
    • MaxFileSize – size of the file, before closing and creating subsequent files
    • StopTime – time to stop the trace
  • sp_trace_setevent – this procedure specifies what event to capture and what column to capture
    • TraceID – the ID of the trace
    • EventID – the ID of the event you want to capture
    • ColumnID – the ID of the column you want to capture
    • On – whether you want to turn this event on or off
  • sp_trace_setfilter – this procedure specifies the filters to set.  This determines whether you include or exclude data
    • TraceID – the ID of the trace
    • ColumnID – the ID of the column you want to set the filter on
    • LogicalOperator – specifies whether this is an AND or OR operation
    • ComparisonOperator – specify whether the value is equal, greater then, less the, like, etc…
    • Value – the value to use for your comparison
  • sp_trace_setstatus
    • TraceID – the ID of the trace
    • Status – stop, start or close a trace

Here is the site to review in detail the four sprocs

https://msdn.microsoft.com/en-us/library/ms190362.aspx

To review the SST here are a few TSQL commands:

-- Shows you the path, status, ID of the saved SST
 
SELECT * FROM sys.traces
SELECT * FROM fn_trace_getinfo(default);
 
Exec sp_trace_setstatus 1, 0 -- 0 = stop trace with id 1 
 
Exec sp_trace_setstatus 1, 2 -- 2 = delete trace with id

-- find the properties of the trace

SELECT * FROM sys.traces

Exec sp_trace_setstatus 2, 0 -- 0 = stop trace with id 1 
 
Exec sp_trace_setstatus 2, 2 -- 2 = delete trace with id