Besides using SQL Server Profiler, is there any way to track which stored procedures are being used, or at least when they were last executed?
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
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.
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