An important step in optimizing a system is to take a universal approach and look at stored procedures that are called very often. These procedures can often be the backbone of a system. Sometimes optimizing these procedures can have a dramatic effect on relieving the physical bottlenecks of the system and improving end-user experience.
The following DMV query shows the execution count of each stored procedure, sorted by the most executed procedures first.
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),st.text
ORDER BY MAX(cp.usecounts) DESC
These execution counts are an aggregate from the last time SQL Server has been restarted.
WikiDBA 
From a performance perspective, your group by statement is terrible. It would have slight impact on this query, but it should be a general practice, especially when you are doing a query to see if a sproc could be impacting performance. This will do the same grouping and IMHO is a cleaner read:
SELECT DatabaseName = DB_NAME(st.dbid),
SchemaName = OBJECT_SCHEMA_NAME(st.objectid,dbid),
StoredProcedure = OBJECT_NAME(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,st.dbid,st.objectid
ORDER BY ExecutionCount DESC
Not sure what benefit this query gives unless your infrastructure is doing an egregious amount of excessive calling of a sproc. Hopefully, you are calling the sproc because you have to call it and you aren’t using this query to detect a bug in your software..
LikeLike
Hi Ken,
Agree with you.
LikeLike
If you are only looking for stored procedures, I wouldn’t even touch sys.dm_exec_cached_plans. I would use sys.dm_exec_procedure_stats instead. That way everything thing you need, and a lot more, is in one table. This is also MUCH quicker on very large system that might have 20,000 items in sys.dm_exec_cached_plans, mainly because you don’t need to reference sys.dm_exec_sql_text.
select DB_NAME(database_id) as [DatabaseName],
OBJECT_SCHEMA_NAME( object_id, database_id) as [SchemaName],
OBJECT_NAME(object_id, database_id) as [StoredProcedure],
execution_count as [ExecutionCount],
*
from sys.dm_exec_procedure_stats
where database_id 32767
order by execution_count desc
LikeLike