How to show the duration of SPs called from master SP

Question

How to show the duration of SPs called from master SP

Roust_m

SSCoach

Points: 17410
More actions
September 30, 2009 at 12:23 am

#136317

Hi,
Just wondering, if I have and SP, which calls other SPs, would it be possible to see the duration of those SPs in a profiler trace? Which events should I choose to show any function calls from that master SP?
Thanks.

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

For the stored procedures, the event SP:Completed. Not sure offhand about the functions.

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

Silverfox SSC-Insane Points: 23150 More actions · Answer 2

I have heard that UDF's; while not technically stored procedures will get traced with the statement level events, such as SP:StmtStarting

--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]

Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]

Roust_m SSCoach Points: 17410 More actions · Answer 3

But SP:StmtStarting does not show duration. I guess I should be using SP:StmtCompleted instead.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Silverfox (9/30/2009)
I have heard that UDF's; while not technically stored procedures will get traced with the statement level events, such as SP:StmtStarting

Yup, but so will every other statement in the stored procedures, making for a very intensive, very large trace. It might be the only way, but it's not something I'd recommend against a busy production server just because of the volume of events. Against dev/test could be OK. Still the volume of events will mean a massive amount of trace to analyse.

I can't recall if there's any event that records the function's execution as a whole without tracing at the statement level.

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

Vishal Singh SSCertifiable Points: 5830 More actions · Answer 5

I dont know I would it sounds here but I was just thinking about it (still to do practically).

Why not to scan the SQL Server 2005 default trace from the main SP itself?

-Forum Etiquette: How to post Performance Problems[/url]

-Forum Etiquette: How to post data/code to get the best help [/url]