Server Side Trace/Profiling Scripts

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

sp_trace_create (Transact-SQL)     --> Creates a trace definition. The new trace will be in a stopped state.
sp_trace_setevent (Transact-SQL) --> Adds an event class or data column to a trace, or removes one from it.
sp_trace_setfilter (Transact-SQL)   --> Applies a new or modified filter to a trace.
sp_trace_setstatus (Transact-SQL)--> Starts, stops, or closes a trace.
sp_trace_generateevent(Transact-SQL) --> Creates a user-defined event.

We can make use of these system functions to query the trace info:

fn_trace_gettable  --> Returns the trace information in table format.            
fn_trace_geteventinfo (Transact-SQL)  --> Returns information about events included in a trace.
fn_trace_getinfo (Transact-SQL) --> Returns information about a specified trsace or all existing traces.
fn_trace_getfilterinfo (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
http://msdn.microsoft.com/en-us/library/ms191006(SQL.90).aspx

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.

regards
vinurajr