Execution Count of an SP

To get the number of times an SP has been executed run the following TSQL script :

SELECT 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
inner join sys.objects sr
on st.objectid = sr.[object_id]
where DB_NAME(st.dbid) is not null and cp.objtype = 'proc' and sr.[type] = 'p'
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

The query above will list down all the SP with their execution count. If you want the execution count only for a particular SP then add a WHERE condition as stated below :



SELECT 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
inner join sys.objects sr
on st.objectid = sr.[object_id]
where DB_NAME(st.dbid) is not null and cp.objtype = 'proc' and sr.[type] = 'p' and sr.[name] = 'Your_SP_Name'
group by cp.plan_handle, DB_NAME(st.dbid),
OBJECT_SCHEMA_NAME(objectid,st.dbid),
OBJECT_NAME(objectid,st.dbid)

Note:
The execution count given by the above query is from the cache in the SQL server, so it will give the count from the time since last cache clearing took place.