SP:stmComplete Time does not add up with stored procedure complete time

Last Post 10 Jun 2010 11:18 AM by Liem. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Resolved
Liem
New Member
New Member

--
08 Jun 2010 04:41 PM
Hi!

I need help to figure out a problem.

I profiled a stored procedure and see that the time that took to execute the stored procedure is greater than the total time of each individual statements in the stored procedure add together.  Is there a way to find out where is the wait?

For example: 3 statements in a stored procedure

statement 1 - 10 ms
statement 2 - 20 ms
statement 3 - 25 ms

I would expect time take to execute that SP is 55 ms, but my profile shows is  more that 55 ms.

Thanks,
rm
New Member
New Member

--
09 Jun 2010 07:44 AM
Whole duration? Did you add connection time and any wait time?
Liem
New Member
New Member

--
09 Jun 2010 09:12 AM

Yes it was the whole duration.  I have only 2 checks, RPC:Completed and Stmt:Completed.  I saved the profile into a table.  I only see 2 "EventClass", 45 and 10.  What do I need to check to see the wait time and connection time?

gunneyk
New Member
New Member

--
09 Jun 2010 07:49 PM
You probably have compile time in the overall sp that you are not seeing with the statement level. Unless this time is excessive its probalby not worth bothing with.
Liem
New Member
New Member

--
10 Jun 2010 11:18 AM
Thanks for your help. I think I need to figure it out. We have a regression test and it used to take 9 hours, but now it takes 14 hours. We have some new tables. I just want to narrow down and find out if because of add and writing data to those table was the reason that it caused more time


Acceptable Use Policy
---