Returns aggregate performance statistics for cached stored procedures. The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view. On SQL Server, requires VIEW SERVER STATE permission.
--->> Proc Statistices
SELECT d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name', db_name(database_id) dbname,
d.cached_time, d.last_execution_time, d.total_elapsed_time,
d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
d.last_elapsed_time, d.execution_count
FROM sys.dm_exec_procedure_stats AS d
ORDER BY [total_worker_time] DESC;
Reference
https://www.brentozar.com/archive/2016/02/stored-procedure-cached-time-vs-sql-statement-cached-time/
