How to find Stored Procedures execution time in SQL Server?

I have 10 stored procedures as follows: SP1,SP2,.....,SP10 These stored procedures do some stuff. I need to run these procedures as follows: EXECUTE SP1; EXECUTE SP2; ... EXECUTE SP10;

When SQL server finshes to complete execution of these procedures, it gives ten lines showing any row changes caused by all these stored procedures. What i want to do is that after Execution of all stored procedures SQL Serve开发者_StackOverflow社区r also gives in output window the execution time for each Stored Procedures. Can i do this? I am sure that for achieving this task i need to modify stored procedures but i don't have any idea how to do it... Please help me. Thanks.

Assuming management studio or some other environment with an output pane you can;

SET STATISTICS TIME ON
EXEC SP1
EXEC SP2
...
SET STATISTICS TIME OFF

You can use Sql Server Profiler for this purposes it provides a lot of useful info along the each executed query and Stored procedure as well.

MSDN: SQL Profiler Data Columns

SQL Profiler displays the execution time for each event

An other straightforward way:

1. DECLARE 2 datetime variables: start/end
2. SET start = GETDATE()
3. EXEC SP_NAME
4. SET end = GETDATE()
5. Execution time - difference between end and start

declare @start datetime = getdate()

-- your SQL statements
exec dbo.MyStoredProcedure

declare @executionTimeInMilliseconds int = datediff(ms, @start, getdate())

You don't need to modify the SPs code at all. The following query gives you some stats about SPs execution times.

SELECT 
   DB_NAME(database_id) [database],
   OBJECT_NAME(object_id) [stored_procedure],
   cached_time, 
   last_execution_time, 
   execution_count,
   total_elapsed_time/execution_count [avg_elapsed_time],
   [type_desc]
FROM sys.dm_exec_procedure_stats
--WHERE OBJECT_NAME(object_id) IN ('SP1','SP2','SP3','SP4','SP5')
ORDER BY avg_elapsed_time desc;

Just try it.

There are ways to do this using tools such as Sql Server profiler, but a simple way is to run each proc surrounded with a line to print the time:

print convert(varchar, getdate(), 21)
EXEC PROC SP1
print convert(varchar, getdate(), 21)

capture and display execution time

use two parameter of datetime type and set one using getdate() before start of tsql in stored procedure

and set second using getdate() after tsql and finally use datediff function to get the difference