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?

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)