Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to find the Avg Stored Procedure execution.

Posted on 2010-08-17
5
Medium Priority
?
347 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 1000 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 1000 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

704 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