SQL Server - Execution count of stored procedures

Check what stored procedures are loading your SQL Server the most. Nice and simple analytical query to check which stored procedures are running most times. Its like small procedures competition results. You can benefit from this information also when you want to find execution count of some exact stored procedure. Just follow the tip below.

TIP: Uncomment code on line 9 to filter only subset of procedures given by string wildcard.

SELECT  
	DB_NAME(st.dbid) DatabaseName,
	OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName,
	OBJECT_NAME(st.objectid,dbid) StoredProcedure,
	MAX(cp.usecounts) ExecutionCount
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'
--	AND OBJECT_NAME(st.objectid, dbid) LIKE 'uspGetEmployeeManagers'
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

Results

Script results screen:

Results screen

Script results detailed description:

Column nameData typeDescription
DatabaseNamenvarchar(128)Name of the database where you can find stored procedure.
SchemaNamesysnameName of the schema where you can find stored procedure.
StoredProceduresysnameName of stored procedure.
ExecutionCountintCount how many times was stored procedure executed within SQL Server.

Leave a Reply

Your email address will not be published. Required fields are marked *