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.
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.
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.