Find Most Executed Stored Procedures

An important step in optimizing a system is to take a holistic 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.

[cc lang=”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
[/cc]

These execution counts are an aggregate from the last time SQL Server has been restarted.

2 comments
zahid 12 Feb 2014 at 5:43 am

I find the most exutute store procedure from above query..
I want to clear it from memory and start user-count should be zero

Finding Unused Tables And Procedures « 36 Chambers – The Legendary Journeys: Execution to the max! 18 Oct 2012 at 3:05 pm

[…] let’s look at stored procedures which have not been used lately.  I’m adapting code that Derek Dieter wrote to find highly-active stored procedures and doing the opposite:  looking for procedures which are […]

Featured Articles

 Site Author

  • Thanks for visiting!