Created
October 21, 2015 11:51
-
-
Save MarshalOfficial/4df5328f5b7d60ba24c5 to your computer and use it in GitHub Desktop.
Find the most executed stored procedure(s)(Sp Exec Counts)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/*########################################### | |
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment