March 1, 2011 at 10:55 am
what is the difference between RPC:Completed and SP:Completed in sqlprofiler.
In profiler the info is like :
RPC:Completed:Occurs when a remote procedure call has been completed.
SP:Completed:Indicates when the stored procedure has completed.
But what is exact difference between these two?
I had searched many blogs for this but I haven't found this.Please help me on this
Thanks,
Vamsy
March 1, 2011 at 12:17 pm
Here's my understanding of it.
If you have a stored procedure that can call another stored procedure, when the sub-proc completes, it will show as a completed procedure, and when the parent procedure completes, that will show as a completed procedure. If the parent proc was called via RPC (which is common), it will also count as a completed RPC call, but the child proc won't.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 1, 2011 at 3:21 pm
The RPC protocol is used when a client calls a procedure on a server. In SQL terms typically when an application server makes a procedure call against the database you will get the RPC Start in profiler. When the stored proc itself actually starts, which may be microseconds later you will get the SP Start event, if you are tracing SP Statements each of those will follow untill you get to the end of the proc.
Once the SP completes you get the next event and only after that, when the server has killed or responded to the RPC call, do you get theRPC Completed event.
If you are loged onto the SQL Server with MS and execute an SP you will only see anSP:Start andSP:Complete event, but under certain conditions (if RPC is being used) calling the same stored proc from your client will create the RPC and SP start events.
Have a look here: http://searchsoa.techtarget.com/definition/Remote-Procedure-Call
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
March 2, 2011 at 10:20 pm
Leo.Miller (3/1/2011)
The RPC protocol is used when a client calls a procedure on a server. In SQL terms typically when an application server makes a procedure call against the database you will get the RPC Start in profiler. When the stored proc itself actually starts, which may be microseconds later you will get the SP Start event, if you are tracing SP Statements each of those will follow untill you get to the end of the proc.Once the SP completes you get the next event and only after that, when the server has killed or responded to the RPC call, do you get theRPC Completed event.
If you are loged onto the SQL Server with MS and execute an SP you will only see anSP:Start andSP:Complete event, but under certain conditions (if RPC is being used) calling the same stored proc from your client will create the RPC and SP start events.
Have a look here: http://searchsoa.techtarget.com/definition/Remote-Procedure-Call
Cheers
Leo i usually capture sp:start and sp:complete if i want to capture ONLY the procs running from the application? Do i need to cpature RPC event also? My understanding was if RPC happens it will show up in sp:start? Please clarify. Thanks
Leo
March 31, 2011 at 8:56 am
Hello all ~
Along these lines ... I was trying to get a feel for our stored procedures that are obsolete so I could delete them. I started a trace on RPC:Completed and SP:Completed but it only took a few minutes to realize I was getting a lot of noise. Is there a way to fine tune the profiler to pick up only user created stored procedures?
I used the RPC:Completed because I want to pick up my Reporting Services reports that use stored procs - this would be the correct use of RPC Completed, is that correct?
I'm an absolute newbie to Profiler - any help would be appreciated!
March 31, 2011 at 8:59 am
You can add a filter on the application name, or anything else you want actually.
It's in the same screen as the events and columns at the bottom right of the form.
March 31, 2011 at 11:04 am
Thank you! I did not even notice the filter button.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply