How to find the Avg Stored Procedure execution.

how to find the average execution time of stored procedures during yesterday.
I would need a script that would list the number of executions and average time of execution of all the procedures.
LVL 8
dba2dbaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
unless you log, in the procedure itself, the start/stop times, your only method would be to run a trace, and do the calculations from there ...
0
 
rajvjaCommented:
Hi,

  Create a table with SP_Name, Start, End, Date

In the SPROC, at the begining store the current time in the table and also at the end of SPROC
0
 
zimbixCommented:
Could you interface with Sysinternals Process Explorer?
0
 
PrakashRaoBSConnect With a Mentor Commented:
If you are using SQL Server 2008 the Following query will work,
select 
OBJECT_NAME(object_id) as 'Procedure Name',
execution_count,
min_elapsed_time,
max_elapsed_time 
from sys.dm_exec_procedure_stats
where DB_NAME(database_id) = ' DatabaseName '

Open in new window

0
 
PrakashRaoBSCommented:
If you are using SQL server 2005, then the following query will work,
select execution_count,
min_worker_time,
max_worker_time,
min_elapsed_time,
max_elapsed_time, text from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle)
where text like '%procedure name%' and
text not like '%sys.dm_exec_query_stats%'

Open in new window

0
All Courses

From novice to tech pro — start learning today.