Technical Article

Find the most executed stored procedure(s)

,

--Execute in SSMS Window

--E&OE - Tested in SQL 2008 and Above. Other variations of this script might be existing too.

/*###########################################
  Find the most executed stored procedure(s).
 ############################################*/SELECT  DB_NAME(SQTX.DBID) AS [DBNAME] , 
         OBJECT_SCHEMA_NAME(SQTX.OBJECTID,DBID) 
AS [SCHEMA], OBJECT_NAME(SQTX.OBJECTID,DBID) 
AS [STORED PROC]  , MAX(CPLAN.USECOUNTS)  [EXEC COUNT]     
FROM SYS.DM_EXEC_CACHED_PLANS CPLAN  
 CROSS APPLY SYS.DM_EXEC_SQL_TEXT(CPLAN.PLAN_HANDLE) SQTX  
WHERE DB_NAME(SQTX.DBID) IS NOT NULL AND CPLAN.OBJTYPE = 'PROC' 
GROUP BY CPLAN.PLAN_HANDLE ,DB_NAME(SQTX.DBID) ,OBJECT_SCHEMA_NAME(OBJECTID,SQTX.DBID)  ,OBJECT_NAME(OBJECTID,SQTX.DBID)  
ORDER BY MAX(CPLAN.USECOUNTS) DESC 
--E&OE - Other variations of this script might be existing too.

Rate

4.63 (8)

You rated this post out of 5. Change rating

Share

Share

Rate

4.63 (8)

You rated this post out of 5. Change rating