SQL Server: Using SQL Profiler to Capture Stored Procedure Call from Application

SQL Profiler is a high-quality tool to figure outdifferent database issues, like “Which are costliest queries running”, “Whichquires acquired exclusive locks”, “Which indexes are missing” and soon. But in development environment and on production when solving a problem,developers like to use SQL Profiler to get exact procedure call which is beinggenerated by front end application.
Worst practice is, that developers like to useexisting built in templates for this purpose and normally use default one i.e. STANDARD. If you are also using SQL Profiler for this procedure call purpose, thenselecting STANDARD trace template is not a good choice, as on productionserver it affects its performance and even on development server it returnsmuch more extra information.
Good practice is, if you have not created your owntemplate then always select TUNING.
 It also has some extra information so when you onlyneed to catch procedure calls generated from your application then click on “EventSelection” tab and keep only “RPC:Completed” event. You need not toselect “Sp: stmt Completed” as you just need to capture “execute procedure calls”and not all the statement inside this procedure. You can also omit “SP:Batch Completed” as we need calls that are only generated from application. If you also need to capture calls from SSMS then you can keep it.

To avoid extra work pressure on server and to getyour required results only, you must also apply filters on “Database Name”and “Text
 Use % sign, just like you use in LIKE operator.

Now run your trace and you will find your required results quickly and clearly, even without putting extra work load on database server.

Posted on October 17, 2011, in SQL Profiler, Sql Server 2005, Sql Server 2008, SQL Server Basics. Bookmark the permalink. 3 Comments.

  1. Excellent – That works perfectly – Simple!

  2. awesome work man … works like a charm

  3. Great post!Regards,UHM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: