--Execute in SSMS Window
--E&OE - Tested in SQL 2008 and Above. Other variations of this script might be existing too.
--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.