Solved

How to find the Avg Stored Procedure execution.

Posted on 2010-08-17
5
341 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 143

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard 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.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 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