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),
          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.dbidIS 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.usecountsDESC

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

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

    Like

  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

    Like

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s