dma-669038 (4/15/2010)

---

Great article but in large environments you dont get to run/profile all stored procs at one shot(many times the application will not even call some procs depending on how people are using it, or testing it). We typically go by below DMV based query - it has some limitations, only gives what is in cache since last reboot, but depending on your environment it might work well. We got it from Greg Larsen's article

http://www.databasejournal.com/features/mssql/article.php/3687186/Monitoring-Stored-Procedure-Usage.htm

--Stored procedure usage since last reboot

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

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)