DMV-6: How Well Is My Stored Procedure Doing?

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 con­tains 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 cor­responding 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:
   1. SQL_STORED_PROCEDURE
   2. CLR_STORED_PROCEDURE
   3. EXTENDED_STORED_PROCEDURE