Difference in duration of SP:Completed and sum(SP:StmtCompleted) I have a stored procedure, which sometimes displays a big difference in duration for the overall execution time and the execution time for each of its statements. The procedure consist of 4 statements, each with a maximum run-time of 4ms. But some procedure has Sp:Completed duration of around 1000 ms or more.
The procedure is called very often (at least once every second, or more).
My first idea was about recompile time, but this procedure is rather simple and doesn't need to be recompiled. I run trace to find recompile events relating this procedure and can't catch any record in it.
I want to use MS SQL 2017 if it may help. Every duration which I mention here was obtained from the SQL Server Profiler.
What is the reason for the long procedure execution time?
It's just in how and what they measure. The statement complete measures only that statement. It doesn't measure any wait time while that statement didn't start, etc. The procedure complete measures all sorts of extraneous management, blocks, waits, etc. There's just not going to be a perfect correlation between the two.