New DMV for Cached Stored Procedures (SQL 2008 – 2012)

SQL Server Administration: (SQL 2008 – SQL 2012) Use the new dynamic management view sys.dm_exec_procedure_stats to view performance statistics about cached stored procedures. This dmv will only show information on the stored procedures that are still in the cache. If a stored procedure is aged out or removed from the cache then performance info will not be in the view. For more information check out Books Online or http://msdn.microsoft.com/en-us/library/cc280701(v=sql.110).aspx

SELECT TOP 10 d.object_id, d.database_id,
OBJECT_NAME(object_id, database_id) ‘proc name’,
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;