How to find Stored Procedures execution time in SQL Server?

Question

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 Server 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.

Answer 1

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

Answer 2

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

Answer 3

declare @start datetime = getdate()

-- your SQL statements
exec dbo.MyStoredProcedure

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