SQL Profiler is a high-quality tool to figure out
different database issues, like “Which are costliest queries running”, “Which
quires acquired exclusive locks”, “Which indexes are missing” and so
on. But in development environment and on production when solving a problem,
developers like to use SQL Profiler to get exact procedure call which is being
generated by front end application.
Worst practice is, that developers like to use
existing 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, then
selecting STANDARD trace template is not a good choice, as on production
server it affects its performance and even on development server it returns
much more extra information.
Good practice is, if you have not created your own
template then always select TUNING.
It also has some extra information so when you only
need to catch procedure calls generated from your application then click on “Event
Selection” tab and keep only “RPC:Completed” event. You need not to
select “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 get
your 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.
Excellent - That works perfectly - Simple!
ReplyDeleteawesome work man ... works like a charm
ReplyDeleteGreat post!
ReplyDeleteRegards,
UHM
Yes,,Great ,,this article helped a lot !
ReplyDeletethank!
ReplyDeleteVery simple and that's what's needed most of the time.
ReplyDeleteVery good, thanks Aasim!!!
My Pleasure
DeleteOnce I get Profiler set up Ill have a go at this. So basically you just put your SP name as a filter for Test Data %USP_Fact_Table%
ReplyDelete?
You can do this filter on ObjectName, if you have selected from columns list, but as per my example, we have not selected ObjectName column so in this case you can put filter on TextData column like you said %USP_Fact_Table%
DeleteVery straight forward way to trace a stored procedure. I like this.
ReplyDeleteSimple But Excellent Post.......Thanks
ReplyDeleteReally fantastic thanks.....
ReplyDeleteNice.. Wonderful...
ReplyDeleteCan i use Sql server standard version sp1 for SQL Profiler. because it is not showing all option like..i want to figure out deadlock..So please can any one explain .?
ReplyDeleteclear explanation, great post.
ReplyDeleteworks great. Thanks.
ReplyDeleteHow would I retrieve the Stored procedures name all that I read is sp_executesql N
ReplyDeleteThanks!!!!!
ReplyDeleteThank you - exactly what I needed !!! Works like a charm in SQL 2012
ReplyDelete10 points!! tks!!
ReplyDeletehow to remove filter
ReplyDeleteVery simple and useful, thanks!
ReplyDeleteGreat tips!
ReplyDeleteThanks for the information .
ReplyDeletehow can you trace multiple sprocs?
ReplyDeleteThanks!!
ReplyDeleteThanks!!
ReplyDelete