SQL Server DMV to get Stored Procedure Usage Stats

If you are working in a SQL Server environment that uses stored procedures for data access, then ii is very useful to get some execution stats around those stored procedures.  What procedures are executing most often?  How much CPU and logical IO has a procedure used?  What is the average time it takes for a procedure to execute.

The following query uses the dm_exec_procedure_stats DMV in SQL Server to do just that.

SELECT sc.name, p.name, st.cached_time,
    st.execution_count, st.total_elapsed_time, st.total_worker_time, st.total_logical_reads ,
 st.total_elapsed_time / st.execution_count as avg_elapsed_time,
 st.total_worker_time / st.execution_count as avg_worker_time,
 st.total_logical_reads / st.execution_count as avg_logical_reads
FROM sys.procedures AS p
INNER JOIN sys.schemas AS sc
  ON p.[schema_id] = sc.schema_id
LEFT OUTER JOIN sys.dm_exec_procedure_stats AS st
  ON p.[object_id] = st.object_id
ORDER BY st.execution_count DESC;

Now it is possible to go down to the query level by looking at the dm_exec_query_stats, and that is also a useful query.  But sometimes, its nice to look at things from the procedure level before diving down to the individual statement level, and that is what this query offers.

Important to note, this view only holds items that are currently in the SQL Cache, so it is not a statistical count since SQL Server was rebooted or anything like that.  It is just what is in the SQL cache, which is usually quite a bit.  If you are looking to see if a procedure is no longer used though, you might want to run this query multiple times at regular intervals over the course of a few days to make sure that the procedure is not getting cached out such that you don't see it in an individual query.