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)

Share

Share

Rate

4.63 (8)