Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

DMV-6 : How well is my stored procedure doing?

, 18 Apr 2013 CPOL
Rate this:
Please Sign up or sign in to vote.
This DMV allows you to discover a lot of very interesting and important performance information about your cached stored procedures.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

No Biography provided

Comments and Discussions

 
QuestionMy most expensive stored procedure PinmemberBill Ross - Pax EDI22-Apr-13 15:11 
QuestionRe: My most expensive stored procedure PinmemberBill Ross - Pax EDI22-Apr-13 15:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141216.1 | Last Updated 18 Apr 2013
Article Copyright 2013 by Rohitmssqlfun
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid