T-SQL How To Get the Execution Count Of a Stored Procedure
How to Get the Execution Count of a Stored Procedure ?
This Query will get the top 100 stored procedure by max execution time.
This is good indicator for developer to determine heavily used procedure.
This can help developer to know if his code needs to replaced or Maintained,
and for SQL admin to but indexes or try to write this procedure In such way to get max performance.
SELECT top 100 DB_NAME(st.dbid) DBName
,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
,OBJECT_NAME(st.objectid,dbid) StoredProcedure
,MAX(cp.usecounts) Execution_count
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)
ORDER BY MAX(cp.usecounts) DESC
,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
,OBJECT_NAME(st.objectid,dbid) StoredProcedure
,MAX(cp.usecounts) Execution_count
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)
ORDER BY MAX(cp.usecounts) DESC
Note That:
This T-SQL Query will apply only from last restart or last “DBCC FREEPROCCACHE” command was run
My name is Mahmoud El Mansy I'm C# Senior solution developer .
I have been surfing online more than 3 hours today, yet I never found any fascinating article like yours. It is beautiful price sufficient for me. In my view, if all site owners and bloggers made good content material as you probably did, the web shall be much more useful than ever before.