RPC:Completed event in sql trace

Question

Post reply

RPC:Completed event in sql trace

vsamantha35

SSChampion

Points: 11551
More actions
July 26, 2018 at 4:33 am

#373913

Hi All,
I have a question related to sql trace/extended event trace.
When troubleshooting a stored procedure say, I want to know which sql statement takeing more time within a stored proc.
So, i would create a new sql trace/server side trace/xvent to troubleshoot the issue.
Within stored procedure we have several events like
RPC:Starting
SP:Recompile
SP:starting
SP:StmtStarting
SP:StmtCompleted
SP:Completed
RPC:Completed
Question is, what is the importance of RPC events i.e. RPC:Starting & RPC:Completed ?
We have SP:starting and SP:Completed events then why people consider using RPC:Completed event?
Thanks,
Sam

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 1

if your performance tuning instead of using profiler, run your SP in SSMS and look at the execution plan.

***The first step is always the hardest *******

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 2

If you're capturing query metrics on SQL Server 2012 or greater, instead of using trace, I strongly recommend you use Extended Events. It's much more lightweight and easier to filter.

As to the events you use, RPC is basically the runtime from the moment of the call to completion, which includes execution time and compile time (if any). SP complete is only the execution time. It won't include compiles, so it's not necessarily going to give you all you need. I'd suggest only using SP complete in a more granular set of measures.

Also be aware that capturing individual statements puts more of a load on the system than just capturing the overall call. That's fine if you're filtering the data being captured (which, again, is better done in Extended Events), but could be a considerable load if you're capturing it for everything all the time.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

RPC is remote procedure call. It's the call from the application.
So, if app calls proc 1, which calls proc 2, proc 3 and proc 4, you would have got one RPC completed event, but 4 SP completed events.

SP Completed only has duration, not CPU time or logical reads, which makes it sub-par to use for identifying performance problems, as you only have the duration to work with.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

vsamantha35 SSChampion Points: 11551 More actions · Answer 4

Thanks Grant & Gail. Learnt something new today 🙂