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.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),st.text
ORDER BY MAX(cp.usecounts) DESC
These execution counts are an aggregate from the last time SQL Server has been restarted.
WikiDBA 