Monitoring Stored Procedure Performance with sys.dm_exec_cached_plans

Other Performance Related Queries for Store Procedures
As with anything it would be nice to measure the performance of your stored procedures.  Having the use count is interesting but it doesn’t tell how much resources are being used or how long the stored procedure takes to run.  So let me show you a couple of performance monitoring TSQL statements for measuring the performance of your stored procedures.

I like to measure the performance of a stored procedure by counting the number of logical I/Os it performs.   Using logical I/Os seems to be a good way to measure how efficient a stored procedure is since I/O normally takes the longest of any operation.  If you can reduce the number of I/Os you normally improve the performance of your SP.  Here is a script that displays the number of logical I/Os required for each stored procedure:

SELECT CASE when dbid = 32767
            then 'Resource'
            else DB_NAME(dbid) end [DB_NAME],
       OBJECT_SCHEMA_NAME(objectid,dbid) AS [SCHEMA_NAME],
       OBJECT_NAME(objectid,dbid)AS [OBJECT_NAME],
       SUM(usecounts) AS [Use_Count],
       SUM(total_logical_reads) AS [total_logical_reads],
       SUM(total_logical_reads) / SUM(usecounts) * 1.0 AS [avg_logical_reads],
       dbid,
       objectid 
FROM sys.dm_exec_cached_plans cp
   CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
   JOIN
   (SELECT SUM(total_logical_reads) AS [total_logical_reads],
           plan_handle 
      FROM sys.dm_exec_query_stats 
      GROUP BY plan_handle) qs
    ON cp.plan_handle = qs.plan_handle
WHERE objtype = 'Proc'
  AND UPPER(
-- remove white space first
            REPLACE(
             REPLACE(
              REPLACE(
               REPLACE(
                REPLACE(
                 REPLACE(
                  REPLACE(text,'       ',' '),
                 '       ',' '),
                '      ',' '),
               '     ', ' '),
              '    ',' '),
             '   ',' '),
            '  ',' ')
            )
       LIKE '%CREATE PROC%'
GROUP BY dbid, objectid
ORDER BY SUM(total_logical_reads) / SUM(usecounts) * 1.0 DESC;

Here I took my query that calculated the use count and then joined it to a summarized rollup of the sys.dm_exec_query_stats DMV to get the logical read code for each stored procedure.  By looking at the avg_logical_reads column you can get a sense for which stored procedures are less efficient than others. 

Another interesting thing to measure is how long a stored procedure takes to run.  By how long they run I mean the amount of time in seconds from the start of the query till the query finishes.  What some might call the elapsed time, or the amount of time a user might wait while the store procedure executes.  Below is a query that calculates the average elapsed time for each stored procedure that SQL Server 2005 tracks:

SELECT CASE when dbid = 32767
            then 'Resource'
            else DB_NAME(dbid) end [DB_NAME],
       OBJECT_SCHEMA_NAME(objectid,dbid) AS [SCHEMA_NAME],
       OBJECT_NAME(objectid,dbid)AS [OBJECT_NAME],
       SUM(usecounts) AS [Use_Count],       
       SUM(total_elapsed_time) AS [total_elapsed_time],
       SUM(total_elapsed_time) / SUM(usecounts) * 1.0 AS [avg_elapsed_time],
       substring(convert(char(23),DATEADD(ms,sum(total_elapsed_time)/1000,0),121),12,23)  total_elapsed_time_ms,
       dbid,
       objectid 
FROM sys.dm_exec_cached_plans cp
   CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) 
   JOIN
   (SELECT SUM(total_elapsed_time) AS [total_elapsed_time],
           plan_handle 
      FROM sys.dm_exec_query_stats 
      GROUP BY plan_handle) qs
    ON cp.plan_handle = qs.plan_handle
WHERE objtype = 'Proc'
  AND UPPER(
-- remove white space first
            REPLACE(
             REPLACE(
              REPLACE(
               REPLACE(
                REPLACE(
                 REPLACE(
                  REPLACE(text,'       ',' '),
                 '       ',' '),
                '      ',' '),
               '     ', ' '),
              '    ',' '),
             '   ',' '),
            '  ',' ')
            )
       LIKE '%CREATE PROC%'
GROUP BY dbid, objectid
ORDER BY SUM(total_elapsed_time) / SUM(usecounts) * 1.0 DESC; 

Conclusion
You should be monitoring all the code that is developed for your server.  By using the code in this article you can identify those stored procedures that use lots of resources.  It is best to perform this monitoring in a development environment while applications are being built.  You can use the techniques presented here to provide feedback to your programmers on how efficient their Store Procedure are.  By giving your programmers some performance statistics you can help them better understand how well their code is running, and promote performance design practices that produce efficient code.