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.

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

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!