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:
Script results detailed description:
| Column name | Data type | Description |
|---|---|---|
| DatabaseName | nvarchar(128) | Name of the database where you can find stored procedure. |
| SchemaName | sysname | Name of the schema where you can find stored procedure. |
| StoredProcedure | sysname | Name of stored procedure. |
| ExecutionCount | int | Count how many times was stored procedure executed within SQL Server. |