Stored Procedure Executions Count

Question

Dear All,

I'm running the following Stored Procedure to determine how many times a Stored Procedure has been run, but is there any way of filtering it out which Date it Started from please?

SELECT DB_NAME(st.dbid) DBName
      ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
      ,OBJECT_NAME(st.objectid,dbid) StoredProcedure
      ,max(cp.usecounts) Execution_count
 FROM sys.dm_exec_cached_plans cp
         CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'   
   group by cp.plan_handle,
   DB_NAME(st.dbid),
            OBJECT_SCHEMA_NAME(objectid,st.dbid),
   OBJECT_NAME(objectid,st.dbid)
 order by max(cp.usecounts) DESC

Thank you in advance!

Answer 1

Hi ti2,

Please refer to the following query:

WITH cte 
     AS (SELECT DB_NAME(st.dbid)                      DBName, 
                OBJECT_SCHEMA_NAME(st.objectid, dbid) SchemaName, 
                OBJECT_NAME(st.objectid, dbid)        StoredProcedure, 
                cp.plan_handle, 
                MAX(cp.usecounts)                     Execution_count 
         FROM   sys.dm_exec_cached_plans cp 
                CROSS APPLY sys.DM_EXEC_SQL_TEXT(cp.plan_handle) st 
         WHERE  DB_NAME(st.dbid) IS NOT NULL 
                AND cp.objtype = 'proc' 
         GROUP  BY cp.plan_handle, 
                   DB_NAME(st.dbid), 
                   OBJECT_SCHEMA_NAME(objectid, st.dbid), 
                   OBJECT_NAME(objectid, st.dbid), 
                   plan_handle) 
SELECT c.*, 
       qs.creation_time 
FROM   sys.dm_exec_query_stats qs 
       INNER JOIN cte c 
         ON qs.plan_handle = c.plan_handle  

 
Reference:

sys.dm_exec_query_stats (Transact-SQL)

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Get or Request Code Sample from Microsoft
If you have any feedback, please tell us.

Answer 2

Thank you for your help!