sys.dm_exec_procedure_stats DMV (Dynamic Management View), described by BOL as follows : http://msdn.microsoft.com/en-us/library/cc280701.aspx
Returns aggregate performance statistics for cached stored procedures. The view contains one row per stored procedure, 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. At that time, a Performance Statistics SQL trace event is raised similar to sys.dm_exec_query_stats.
This DMV is new to SQL Server 2008 so you can use it only in SQL Server 2008 onwards. You can get similar data out of sys.dm_exec_cached_plans, which will work on SQL Server 2005. This DMV allows you to discover a lot of very interesting and important performance information about your cached stored procedures.
Query 1 : Details of cached procedures
SELECT CASE WHEN DATABASE_ID = 32767 THEN ‘RESOURCE’ ELSE DB_NAME(DATABASE_ID)END AS DBNAME
,OBJECT_SCHEMA_NAME(OBJECT_ID,DATABASE_ID) AS [SCHEMA_NAME]
,OBJECT_NAME(OBJECT_ID,DATABASE_ID)AS [OBJECT_NAME]
,*
FROM SYS.DM_EXEC_PROCEDURE_STATS
Query 2 : Details of procedure with total & average CPU, logical reads , logical writes & physical reads
SELECT CASE WHEN DATABASE_ID = 32767 THEN ‘RESOURCE’ ELSE DB_NAME(DATABASE_ID)END AS DBNAME
,OBJECT_SCHEMA_NAME(OBJECT_ID,DATABASE_ID) AS [SCHEMA_NAME]
,OBJECT_NAME(OBJECT_ID,DATABASE_ID)AS [OBJECT_NAME]
,CACHED_TIME
,LAST_EXECUTION_TIME
,EXECUTION_COUNT
,TOTAL_WORKER_TIME / EXECUTION_COUNT AS AVG_CPU
,TOTAL_ELAPSED_TIME / EXECUTION_COUNT AS AVG_ELAPSED
,TOTAL_LOGICAL_READS
,TOTAL_LOGICAL_READS / EXECUTION_COUNT AS AVG_LOGICAL_READS
,TOTAL_LOGICAL_WRITES
,TOTAL_LOGICAL_WRITES / EXECUTION_COUNT AS AVG_LOGICAL_WRITES
,TOTAL_PHYSICAL_READS
,TOTAL_PHYSICAL_READS / EXECUTION_COUNT AS AVG_PHYSICAL_READS
FROM SYS.DM_EXEC_PROCEDURE_STATS
ORDER BY AVG_LOGICAL_READS DESC
Remarks
1. User required VIEW SERVER STATE permission on the server.
2. This DMV will capture the details of 3 objects types :
a. SQL_STORED_PROCEDURE
b. CLR_STORED_PROCEDURE
c. EXTENDED_STORED_PROCEDURE
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
