What is the Server Side Trace In SQL Server?

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 Server Side Trace 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

The Server Side SQL Trace uses four stored procedures to execute the trace.  They are:-

sp_trace_create

sp_trace_setevent

sp_trace_setfilter

sp_trace_setstatus

 

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

 

Leave a Reply

Your email address will not be published. Required fields are marked *