[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


Server Side Trace/Profiling Scripts

Published on
5,498 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

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Join & Write a Comment

Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month