We all are used to sys.dm_exec_query_stats which gives us useful information for queries running on the server.
Similarly in SQL 2008 we can get details for procedures. For this we will have to use sys.dm_exec_procedure_stats
It is supported in SQL 2008 and higher versions.
Its provides aggregated results for cached procedures since last the procedure was cached.If procedure is recompiled every time then its result will not have aggregated data.
Lets have some look into it.
SELECT TOP 10 *
SELECT Db_name(database_id) databaes_name,
total_physical_reads / execution_count avg_physical_reads,
total_logical_reads / execution_count avg_logical_reads,
total_elapsed_time / execution_count avg_elapsed_time
FROM sys.procedures p
JOIN sys.dm_exec_procedure_stats d
ON p.object_id = d.object_id
WHERE d.database_id = Db_id()