0

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?

0
1

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.

-1

You should enable below setting which will show you where the time is being consumed and also enable the show plan for the query.

set statistics time on

This will help you to track weather query taking time in parsing or execution the statement and with the help of execution plan you can easily tune the query.

Not the answer you're looking for? Browse other questions tagged or ask your own question.