Tracking stored procedure usage
You can look in the plan cache to get a pretty good idea of Stored Procedure usage. Take this query, for instance:
select
db_name(st.dbid) as database_name,
object_name(st.objectid) as name,
p.size_in_bytes / 1024 as size_in_kb,
p.usecounts,
st.text
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) st
where p.objtype = 'proc'
and st.dbid = db_id('SomeDatabase')
order by p.usecounts desc
This will give you the usecounts
of the stored procedures that are cached pertaining to SomeDB
.
Note: the plan cache contains the execution plans. This retention of these plans has many factors involved. Whereas this will give you a good idea of what is being used and how often, it's definitely not the running total of stored procedures and how often/when they were executed.
BOL Reference about the Plan Cache
You can have a look at this as well as it contains info of last_execution_time
of every stored procedure.
SELECT DB_NAME(database_id)
,OBJECT_NAME(object_id,database_id)
,cached_time
,last_execution_time
,execution_count
FROM sys.dm_exec_procedure_stats