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?

  • 4
    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, 2012 at 18:50

2 Answers 2


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

    db_name(st.dbid) as database_name,
    object_name(st.objectid) as name,
    p.size_in_bytes / 1024 as size_in_kb,
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

  • 5
    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, 2012 at 19:07
  • 1
    @HLGEM Precisely. I've tried to make that point clear in my answer. Apr 13, 2012 at 19:25
  • Is it possible doing this for a specific datetime range? (e.g. last day/month, etc) ?
    – Jose Parra
    Jun 19, 2018 at 1:29
  • Note that this query will not return a row for a stored procedure that has been altered and not executed after.
    – Axel2D
    May 20, 2019 at 14:52

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)
FROM sys.dm_exec_procedure_stats
  • There is on caveat to this tho. These stats are only kept since the last database execution. I am busy writing a job that will archive it to a table once a day. May 28, 2018 at 9:55
  • @earthling42, did you ever write a job that archived it to a table daily?
    – Alex Chung
    Nov 8, 2019 at 14:56
  • @Alex Chung - unfortunately never got around to that. Moved onto non MS-SQL projects shortly after the date of this post. Shouldn't be to hard tho? I'd be happy to try to work it out with you if you are interested. Nov 12, 2019 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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