stored procedure to count the execution frequency of stored procedures and views

Rebooting the server will reset the counts in the DMV.
             

CREATE PROCEDURE procExecutionFrequency @Database sysname

AS

BEGIN

SELECT

      cp.objtype 'ObjectType',

      min(DB_NAME(st.dbid)) +'.'

      +OBJECT_SCHEMA_NAME(st.objectid,dbid) +'.'

      +OBJECT_NAME(st.objectid,dbid) 'ObjectName'

      ,max(cp.usecounts) 'ExecutionFrequency'

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

WHERE

  cp.objtype in ( 'proc', 'view')

  AND DB_NAME(st.dbid) = @Database

GROUP BY

      cp.objtype,

      cp.plan_handle,

      OBJECT_SCHEMA_NAME(objectid,st.dbid),

      OBJECT_NAME(objectid,st.dbid)

ORDER BY ObjectType, ExecutionFrequency desc

END

GO

             

EXEC procExecutionFrequency 'AdventureWorks'

GO