Monitoring Stored Procedure Usage

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
APPLY sys.dm_exec_sql_text(cp.plan_handle) st
  AND cp.objtype IN ('Proc', 'Trigger', 'View')
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)

Because SQL 2000 and lower do not support APPLY operator, this query is not going to work. The following error will be returned when the database compatibility level is lower than 90 even the server is 2005 and later.

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ‘.’.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s