Don’t waste your time in anger, regrets, worries, and grudges. Life is too short to be unhappy.
One of the most popular and important questions that people often ask in complex database performance checks is how to measure the execution time of a stored procedure when it contains many statements. Well, honestly, the solution is very straightforward. Today we will discuss the execution time of stored procedures.
It is very easy to measure the execution time of a stored procedure. It is not necessary to add up the entire execution time of the stored procedure. Before running the stored procedure, just run the following command and you will see the last line in the message section with the time required to run the execution plan for the stored procedures.
SET STATISTICS TIME ON
EXEC YourSPName
The output will look like this.
SQL Server Execution Times: CPU time = 450 ms, Elapsed time = 1150 ms.
SQL Server Execution Times: CPU Time = 50 ms, Elapsed Time = 100 ms.
SQL Server Execution Times: CPU time = 1450 ms, Elapsed time = 1950 ms.
SQL Server Execution Times: CPU time = 2150 ms, Elapsed time = 3250 ms.
If your stored procedure has three statements, the first three represent the execution time of a single query. However, the last line represents the addition or commutative time for all the query statements together.
I hope you like this.
Thanks.