SQL SERVER – Execution Time of Stored Procedures

One of the most popular questions often people ask in Comprehensive Database Performance Health Check is how to measure the execution time of the stored procedure when it contains lots of statement inside of it. Well, honestly the solution is very straightforward. Let us discuss today Execution Time of Stored Procedures.

It is very much easy to measure the execution time for the stored procedure. One does not have to add up all the execution time of the stored procedure. Just run the following command before you run the stored procedure and it will give you the final line in the message section with the time taken to run the execution plan for Stored Procedures.

SET STATISTICS TIME ON
EXEC YourSPName

The output will look something similar.

SQL Server Execution Times: CPU time = 500 ms, elapsed time = 1200 ms.
SQL Server Execution Times: CPU time = 100 ms, elapsed time = 150 ms.
SQL Server Execution Times: CPU time = 1500 ms, elapsed time = 2000 ms.
SQL Server Execution Times: CPU time = 2100 ms, elapsed time = 3250 ms.

If your stored procedure has three statements the first three represents the execution time of the individual query. However, the final last line represents the addition or commutative time for all the query statements together.

Well, it is that simple. I hope you like this simple blog post. Please leave a comment with your feedback. I read every single line.

If you liked this blog, please do not forget to subscribe to my YouTube Channel – SQL in Sixty Seconds.

Reference: Pinal Dave (http://blog.SQLAuthority.com)