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?