Find Most Executed Stored Procedures

Posted: December 3, 2013 by Virendra Yaduvanshi in Database Administrator
Tags: , , , ,

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),
FROM sys.dm_exec_cached_plans cp
APPLY sys.dm_exec_sql_text(cp.plan_handle) ST
GROUP BY cp.plan_handle,DB_NAME(st.dbid),OBJECT_SCHEMA_NAME(objectid,st.dbid),OBJECT_NAME(objectid,st.dbid),st.text
BY MAX(cp.usecountsDESC

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

  1. Ken says:

    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..


  2. Stephen Mills says:

    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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s