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