Server Side Trace/Profiling Scripts

Published:
Updated:
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
0
2,659 Views

Comments (3)

Author

Commented:
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....

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

Administrative

Commented:
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.

ericpete
Page Editor

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community