Skip to content

Instantly share code, notes, and snippets.

@rodolfofadino

rodolfofadino/FindMostExecutedStoredProcedures.sql

Created October 9, 2017 18:38
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save rodolfofadino/4240ffc0df9afbc0d6b3e8f1773aede9 to your computer and use it in GitHub Desktop.
Save rodolfofadino/4240ffc0df9afbc0d6b3e8f1773aede9 to your computer and use it in GitHub Desktop.
Download ZIP
Find Most Executed Stored Procedures
Raw
FindMostExecutedStoredProcedures.sql
SELECT
DatabaseName = DB_NAME(st.dbid)
,SchemaName = OBJECT_SCHEMA_NAME(st.objectid,dbid)
,StoredProcedure = OBJECT_NAME(st.objectid,dbid)
,ExecutionCount = MAX(cp.usecounts)
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE DB_NAME(st.dbid) IS NOT NULL
AND cp.objtype = 'proc'
GROUP BY
cp.plan_handle
,DB_NAME(st.dbid)
,OBJECT_SCHEMA_NAME(objectid,st.dbid)
,OBJECT_NAME(objectid,st.dbid)
ORDER BY MAX(cp.usecounts) DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment