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
CROSS 
APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE DB_NAME(st.dbid) IS NOT NULL 
  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 ‘.’.

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

http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic9819.aspx

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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