question

Mrs_Fatherjack avatar image
Mrs_Fatherjack asked

SQL Profiler - Missing Duration

I wonder if anyone can help me. I'm trying to tune a stored procedure that calls many stored procedures. I'm using SQL 2008 R2. I've put a trace on and the duration of each individual stored procedure does not equate to total of all the completed steps in the trace file. I have double checked that all code is being hit and that's fine. Can anyone advise how I can find my missing time? Many thanks I am hoping to do this in Extended Events and am attending training on that in the next 2 weeks.
sql-server-2008-r2sql profiler
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image
Blackhawk-17 answered
Is the time greater or less than the total? Some events overlap and some waits occur outside of the wrapped SPs, either of which can skew results.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

The total duration of the stored procedure is greater than the sum of all the durations in within that stored procedure. How can I find out what is causing the waits?
0 Likes 0 ·
You need to read between the lines and see what other events are on the system overlapping yours. Zoom out a little.
0 Likes 0 ·
KenJ avatar image
KenJ answered
There may be events taking place that aren't in the profiler session - are you tracing performance statistics (query plan generation) and other events that may have duration? Are the times close? I don't think every event has a duration associated with it and could imagine that enough of these could cause your total duration to differ slightly from the sum of the individual durations.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I've tried it with every event that can provide a duration on, the duration retuns about 30% of the total duration so lots of time missing and in this instance every millisecond counts.
0 Likes 0 ·
Just to say aswell, the whole thing runs in about 2 seconds but I've got to try to shave as much time off as I can.
0 Likes 0 ·
Does the calling stored procedure do much besides call the other stored procedures? DDL, control flow logic, etc? These times are probably already accounted for - just trying to help reach for straws :)
0 Likes 0 ·
ThomasRushton avatar image
ThomasRushton answered
This discrepancy - is it occurring when you run the same SP / script many times back-to-back, or is it just when you run it the once? I'm wondering if it's some sort of accumulating rounding error...
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What sample size of runs do you have?
0 Likes 0 ·
tanglesoft avatar image
tanglesoft answered
You could add in SP:StmtStarting and SP:StmtCompleted to see if the finer granularity gives any other clues to the overall performance. Also try SP:Recompile although I believe this is including in the execution time. Try collecting client statistics for the query in SSMS with the 'no resultset returned' switched on. Run it a few times to see what variance you get in execution times.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.