Posts Tagged ‘sys.dm_exec_cached_plans’

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.