SQL Server Performance

Procedure Usage Stats

Discussion in 'Getting Started' started by lcerni, Jan 28, 2010.

  1. lcerni New Member

    Is there a dynamic view or system table that collects information on whether a procedure has been used or not?
    For example, for indexes there is sys.dm_db_index_usage_stats. Is there something out there on the usage of procedures?
    Before I drop a procedure that someone claims is not being used I want to verify if what they are telling me is accurate.
  2. Luis Martin Moderator

    As far I know, no.
    But, if you think some procedure is no used, you can add one line to write something in one table each time that procedure is called.
  3. lcerni New Member

    I found this query from http://www.databasejournal.com/feat...3687186/Monitoring-Stored-Procedure-Usage.htm
    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)
    Disclaimer... It is using the dm views thus it only has history since the last reboot.


  4. Luis Martin Moderator

    Good catch!!
  5. FrankKalis Moderator

    So, just in case someone comes across this thread later on. This technique is only available in SQL Server 2005 and above. Knowing that there are still a lot of SQL Server installations around.
  6. Luis Martin Moderator

    [quote user="FrankKalis"]
    So, just in case someone comes across this thread later on. This technique is only available in SQL Server 2005 and above. Knowing that there are still a lot of SQL Server installations around.
    [/quote]
    Good catch also.[:p]

Share This Page