CODEWITHSUNDEEP
sqlsql-serversql-execution-plan
Omer
Omer

Reputation: 10264

Execution times of a stored procedure are different

I have a stored procedure and several statements in it. The below is execution time statistics of the procedure:

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 11484 ms, elapsed time = 2277 ms.

(29 row(s) affected)

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times: CPU time = 11484 ms, elapsed time = 2283 ms.

As you can see the total of 4562 ms. But when I look at the status bar I see that it took 2 seconds. I expect that it should be 4 seconds. Why are these two different?

enter image description here

Upvotes: 1

Views: 520

Answers (1)

ughai
ughai

Reputation: 9890

the first wait of 2277 ms is the execution time of a statement within the procedure. the second one 2283 ms is the complete duration of the procedure. so the procedure ran in 2283 ms and not in (2277 + 2283) ms

So If I create a procedure with a wait for 2 seconds like this

CREATE PROCEDURE usp_delay AS BEGIN
WAITFOR DELAY '00:00:02'
END
GO

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

EXEC usp_delay;

You can see the statistics io and time as below:

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms.

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 9 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 2000 ms.

SQL Server Execution Times: CPU time = 0 ms, elapsed time = 2010 ms.

Notice that it has 2 elapsed time >= 2000ms one for the statement WAITFOR DELAY and one for the complete procedure time. this is what you are seeing as well and the procedure runs in 2010 ms and not 4010 ms based on the above sum.

Upvotes: 1

Related Questions