We are deleting old stored procedures and tables.
How can I know what procedures haven't been called recently?
dm_exec_query_stats aren't reliable, since they only return procedures in the plan cache.
One of my clients has this exact same problem, but it's the worst instance of it I've ever seen. Some rogue developer generated thousands of stored procedures (over 6k), most of which are not used.
They now poll sys.dm_exec_cached_plans every 5 minutes, and insert into a table for tracking. Only stored procedure names that don't already exist in the table are inserted.
As others have mentioned, going through quarterly/annual business cycles is highly recommended.
Knowing what has been called recently only helps for frequently called things and many objects in a complex database are not called that often but are still needed. I know no simple way to identify what isn't being used.
What I would do is start up Profiler on my dev or qa box and then take every application that hits it and run through the functionality. (if you have a formal QA, a good set of regression tests would help this). I would set up my trace to write to a table. Now at least you know what procs the applications call and can eliminate them from the list.
Make sure every job on the prod server has an equivalent job on your test server and run them. That should find some more.
By now your list of potential sps is much smaller.
Your list of active tables should include only those mentioned in one of the procs and tables you know you need like audit tables. YoOu can create a list of potentials for eliminating from there.
Now once you have the list of potentials to eliminate, you will probaly see some fairly obvious ones like usp_my_proc_Old (when you have a USP_My_proc in the db). Those are my first candidates to eliminate. Tables with no data are another set of obvious ones at this point. Tables/procs that clearly refer to a functionality you know has been eliminated would be the next ones. Suppose you recently replaced the functionality for storing survey results with a new design. You may want to keep the table (You may need the data) but the procs that call that table are probably all out of date and can go.
Depending on your legal constraints, you may not want to eliminate any table with data. We have client specific data for clients we no longer have, becasue we are in a regulated industry and are occasionally asked to provide data to auditors and regulators and lawyers. However, you can move these tables to another archive db if you want to clean out your actual production database.
Then start looking at what they do. You can eliminate any proc that will not run especially if one of the tables it references no longer exists. If a table has a datefield, are there any recent dates? If the last time the data field was populated witha date was 2008, that is a good candidates for a table we don't need anymore.
Once you have a list of several potential objects to delete, then send the list around to all of your developers and ask them if they use the table/proc or know what it was for. Do not do this with a huge list of 1000s of objects. Send out no more than 10-20 at a time and try to group them so they are clearly on related topics.
For potentials to eliminate, you can add a logging process to the proc or a logging trigger to the table and set a date when the object will be eliminated if there are no entries by that date.
The problem isn't that DMV's are unreliable, it's that they don't capture the information that you want. Create a job that runs periodically that uses them to capture the information that you want -- run it twice a day if your data is falling off in 24 hours. Given that the DMV's aren't really the intensive, even hourly if you like.
If you can modify the procedures, add a line at the begining of each (this is fairly easy to automate):
Next create a server side trace and capture event 82 (the first user_event). After n days collect the generated traces and aggregate the usage. Make sure your trace writes into a disk with sufficient space and enough IO bandwith. For extra credit you can also inspect the traces periodically and remove
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
Run a trace on something like:
Consider filtering by databases IDs. Once you've compiled enough data you can make your decisions. Be aware of course that a trace will have a performance hit so ensure this hit doesn't cause operational issues.