Server Side Trace/Profiling Scripts

Published on
5,473 Points
Last Modified:
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

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.


Author Comment

there are certain Comments on the review...
1. it doesn't fit the definition of an article --- why...? the Article deals with Profiling Sql Server Silently.
2. Hope u does not have the patience to view the attached File.....
3. Is the zone I selected is not Apt...? Its there in the Sql Server ....
4. I categorized it under Tips/Tricks This is a Tips and Trick to Silently Profile Sql Server....

Expert Comment

Why is this listed under "Deeper Linux Programming Learning" when it is a MS article?

Administrative Comment

by:Eric AKA Netminder
Wild guess: Probably because there aren't that many Linux articles, so the algorithm goes toward the root -- which would be Operating Systems.

But don't take that as gospel.

Page Editor

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month