SQL SERVER – How to Find Stored Procedure Execution Count and Average Elapsed Time?

Every single time when I go for Comprehensive Database Performance Health Check, I get many different questions from the users. However, there are some questions which are very common and I have started to build a list of the such questions so I can share the same with you. Today’s question is about the Stored Procedure Execution Count.

Here is the question from the latest consultancy, which I had performed for a health care organization last week.

How can we know how many stored procedures have executed so far and how many times?

SQL SERVER - How to Find Stored Procedure Execution Count and Average Elapsed Time? AvgExcTimeSP

Well, to be super honest, I have always wondered this personally as well. Hence, I have already created a query for the same purpose.

SELECT DB_NAME(database_id) DatabaseName,
OBJECT_NAME(object_id) ProcedureName,
cached_time, last_execution_time, execution_count,
total_elapsed_time/execution_count AS avg_elapsed_time,
type_desc
FROM sys.dm_exec_procedure_stats
ORDER BY avg_elapsed_time;

As you can see when you run above query, it will return you all the stored procedure ran across database along with their execution count and average elapsed time.

There is one thing you must remember – all the results are there since the last SQL Server restart. So if you restart the SQL Server, your results will be reset to zero.

Hence, it is a good idea that you only trust this results after your server is up and running for quite a few days.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – How to Create Table Variable and Temporary Table?
Next Post
SQL SERVER – How to Create A Global Temporary Table?

Related Posts

1 Comment. Leave new

  • Sotirios Mantziaris (@SMantziaris)
    October 19, 2017 11:59 am

    make the following change in the query to return the name of the sp.
    OBJECT_NAME(object_id,database_id) ProcedureName,

    Reply

Leave a Reply

Menu