Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Besides using SQL Server Profiler, is there any way to track which stored procedures are being used, or at least when they were last executed?

share|improve this question
3  
You can always look at the plan cache. SQL Server doesn't track this stuff forever because metadata quickly gets large and expensive. –  JNK Apr 13 '12 at 18:50

2 Answers 2

up vote 8 down vote accepted

You can look in the plan cache to get a pretty good idea of Stored Procedure usage. Take this query, for instance:

select
    db_name(st.dbid) as database_name,
    object_name(st.objectid) as name,
    p.size_in_bytes / 1024 as size_in_kb,
    p.usecounts,
    st.text
from sys.dm_exec_cached_plans p
cross apply sys.dm_exec_sql_text(p.plan_handle) st
where p.objtype = 'proc'
and st.dbid = db_id('SomeDatabase')
order by p.usecounts desc

This will give you the usecounts of the stored procedures that are cached pertaining to SomeDB.

Note: the plan cache contains the execution plans. This retention of these plans has many factors involved. Whereas this will give you a good idea of what is being used and how often, it's definitely not the running total of stored procedures and how often/when they were executed.

BOL Reference about the Plan Cache

share|improve this answer
4  
Remeber this only tells you the sp has been run fairly recently and gives you no information about procs not in here. Things like procs behind quarterly reports are run infrequenty but are being used. So basically, this just gives you a list to research in the applications that hit your db. –  HLGEM Apr 13 '12 at 19:07
1  
@HLGEM Precisely. I've tried to make that point clear in my answer. –  Thomas Stringer Apr 13 '12 at 19:25

You can have a look at this as well as it contains info of last_execution_time of every stored procedure.

    SELECT DB_NAME(database_id)
    ,OBJECT_NAME(object_id)
    ,cached_time
    ,last_execution_time
    ,execution_count
FROM sys.dm_exec_procedure_stats
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.