Wednesday, February 1, 2012

Top Stored Procedures by Execution Count

In this script I want to show you how you can find the procedures that have been executed the most times on your instance. I always use this script to see if I can make the most executed stored procedures perform better.


SELECT TOP 100
               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 [Execution Count]
   FROM    sys.dm_exec_cached_plans cp
  CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
 WHERE   objtype = 'Proc'
      AND CASE
                     WHEN dbid = 32767
                       THEN 'Resource'
                       ELSE DB_NAME(dbid)
               END NOT IN ('Resource','msdb','master')
     AND UPPER(
                 REPLACE(
                   REPLACE(
                     REPLACE(
                       REPLACE(
                         REPLACE(
                           REPLACE(
                             REPLACE(text , '       ', ' ') , '       ', ' '), '      ', ' '),'     ', ' '), '    ', ' '), '   ', ' '), ' ', ' '))
              NOT LIKE '%CREATE FUNC%'
GROUP BY dbid
                  , objectid
                  , cacheobjtype
ORDER BY SUM(usecounts) DESC ;

No comments:

Post a Comment