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.
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.
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?
You need to read between the lines and see what other events are on the system overlapping yours. Zoom out a little.
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.
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.
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.
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...
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.
14 People are following this question.
