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)

John Waclawski (4/19/2010)

---

I tried this but received the following error when I ran it:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'.

Which is referencing this line: CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

I'm still debugging it but thought I'd throw this out there.