Find procedures that haven't been called in <n> days

We are deleting old stored procedures and tables.

How can I know what procedures haven't been called recently?

dm_exec_procedure_stats and dm_exec_query_stats aren't reliable, since they only return procedures in the plan cache.

If sys.dm_exec_procedure_stats is not reliable for you (probably more because the information doesn't survive restarts than anything to do with the plan cache), SQL Server doesn't keep track of this in any other way.

The only way to do this would be to add logging to your stored procedures (or to the app that calls them, if that is feasible and inclusive enough), or to run a very targeted server-side trace perpetually and review the trace.

Also note that just because a procedure hasn't been called in a week doesn't mean it won't be called tomorrow. You could have reporting procedures that are only called monthly or annually or some obscure operation that doesn't happen very often. Deleting that stored procedure could be disastrous days or weeks from now, potentially beyond any backup you have at the time (and assuming you aren't following best practices and storing your stored procedures in source control).

The safest way, IMHO, is to rename stored procedures (maybe with a zzz_ prefix so they sort to the bottom of any lists) that you've already identified through other means as potential candidates of being "too old" - then at least when you do that to one inadvertently and something breaks, it is easy enough to rename it again, restoring functionality without having to scrounge for old code in backups. Only delete the procedures when a full business cycle has passed and nobody has complained.

StackExchange Database Administrators Q#43875, answer score: 12