How to Get the Execution Count of a Stored Procedure ?
This Query will get the top 100 stored procedure by max execution time.
This is good indicator for developer to determine heavily used procedure.
This can help developer to know if his code needs to replaced or Maintained,
and for SQL admin to but indexes or try to write this procedure In such way to get max performance.


SELECT top 100 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


Note That:
This T-SQL Query will apply only from last restart or last “DBCC FREEPROCCACHE” command was run