Solved

How to find the Avg Stored Procedure execution.

Posted on 2010-08-17
5
339 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:dba2dba
5 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 33453879
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
 
LVL 11

Expert Comment

by:rajvja
ID: 33453888
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
 
LVL 1

Expert Comment

by:zimbix
ID: 33453948
Could you interface with Sysinternals Process Explorer?
0
 
LVL 3

Assisted Solution

by:PrakashRaoBS
PrakashRaoBS earned 250 total points
ID: 33455980
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
 
LVL 3

Expert Comment

by:PrakashRaoBS
ID: 33456118
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

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question