CODEWITHSUNDEEP
sqlsql-server
Azeem
Azeem

Reputation: 2924

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

Upvotes: 30

Views: 106068

Answers (6)

Igor Micev
Igor Micev

Reputation: 1652

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.

Upvotes: 10

Illia Ratkevych
Illia Ratkevych

Reputation: 3711

declare @start datetime = getdate()

-- your SQL statements
exec dbo.MyStoredProcedure

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

Upvotes: 11

Widor
Widor

Reputation: 13265

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)

Upvotes: 5

Alex K.
Alex K.

Reputation: 175748

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

Upvotes: 33

sll
sll

Reputation: 62484

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

Upvotes: 19

rahularyansharma
rahularyansharma

Reputation: 10755

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

Upvotes: 1

Related Questions