Solved

How to find the Avg Stored Procedure execution.

Posted on 2010-08-17
5
343 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

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…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

617 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