SQL Server Side Trace is a technique of Profiling SQL Server Events Silently (i.e without Using the Profiling Tool). Running a visual tool in production increases overhead, but we can develop server side Trace using Sql Server Profiler itself.
We can develop server side Trace using Sql Server Profiler itself by following these steps:
A) Open Profiler then define the Events and set the Filters you want to track. Once the Trace is ready
B) Start run the trace. While Trace in Running
C) Go to File Menu "Export->Script Trace Defenition->" then select the script compatable for 2000/2005
You will get a save prompt; save the Generated Trace Code. Once it is saved you can execute from Query analyzer/ Sql Server Management Studio
If you look into the code that generated from Profiler (File attached) you will see different System Stored Procedures such as
(Transact-SQL) --> Creates a trace definition. The new trace will be in a stopped state.
(Transact-SQL) --> Adds an event class or data column to a trace, or removes one from it.
(Transact-SQL) --> Applies a new or modified filter to a trace.
(Transact-SQL)--> Starts, stops, or closes a trace.
(Transact-SQL) --> Creates a user-defined event.
We can make use of these system functions to query the trace info:
--> Returns the trace information in table format.
(Transact-SQL) --> Returns information about events included in a trace.
(Transact-SQL) --> Returns information about a specified trsace or all existing traces.
(Transact-SQL) --> Returns information about filters applied to a trace.
See the attached file for more instructions.
All the system Stored Procedures Are Explained More In MSDN
So with the help of this script, profiling overhead over visually running and displaying the events will get reduced. The only thing is that we need is to stop the Trace and view it.