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.

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.

share|improve this question
    
Have you tried searching your source code for each procedure name? –  Jon Seigel Jun 5 '13 at 15:28
2  
@JonSeigel that won't really help; a procedure could be in source code in a method that users don't happen to call very often. You won't be able to tell that from the source code. –  Aaron Bertrand Jun 5 '13 at 16:05
2  
@Aaron: Rarely != never, although the question says "recently," but perhaps on the assumption of a solution. If a given stored procedure name is never found in the source code, that helps quite a bit. –  Jon Seigel Jun 5 '13 at 16:18
1  
@JonSeigel that is true, assuming nobody ever calls any stored procedures manually. Still not enough information is known for that to be assumed, and even if it is assumed, I don't think I could rely on it in good conscience. –  Aaron Bertrand Jun 5 '13 at 16:23
1  
@Aaron: This all depends greatly on the environment, too, so in general, I agree with you. I think at the very least, if a stored procedure or table name doesn't appear in any code, that's a very good indication the object isn't being used. I suppose a complicating factor would be previous versions of the application if it's something like a vendor application. Again, situational. –  Jon Seigel Jun 5 '13 at 16:29
show 2 more comments

6 Answers

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.

share|improve this answer
    
actually it´s not reliable not because restarts. We doesn´t restart the database for months. If I query this view, it only returns proc last executed today, and I know that some procs wasn´t executed today. MSDN says: "The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached" –  Fujiy Jun 5 '13 at 18:56
1  
If you can only cache plans for procedures that were executed today, then either your server is extremely under-provisioned in terms of memory (either physically or artificially through max server memory), or you have way, way, way too many unique procedures being called and/or their plans are way more complex than they should be. How much memory does your server have? How many plans are cached at any one time? –  Aaron Bertrand Jun 5 '13 at 19:16
    
Note that, if your business is using something similar to a 4-4-5 Calendar, one business cycle could be on the order of 5/6 years, if something is run every leap-year/week. –  Clockwork-Muse Jun 5 '13 at 22:11
add comment

If you can modify the procedures, add a line at the begining of each (this is fairly easy to automate):

exec sp_trace_generateevent 82, N'<procedure__name>';

Using sp_trace_generateevent is fairly benign and does not affect the procedure execution flow/result/outcome. Most importantly it has no interaction with the current transaction. It does not turn a read-only procedure into a data-write one, with all the logging and locking implications. If there is no trace monitoring event 82 the exec call is basically free (no-op).

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 exec call from any procedures you find there, since is proven to be called.

share|improve this answer
    
Since this requires modifying all of the procedures, what advantage does it have over setting up a server-side trace to capture all RPC:Starting events (and just capture database/name)? That after some time you can stop capturing some of the events? Otherwise it seems like more work than just setting up a trace. –  Aaron Bertrand Jun 5 '13 at 19:41
1  
@AaronBertrand: RPC:Starting captures any RPC event, eg: INSERT ... (@var), which is not a procedure call. And does not capture a procedure call embedded in a batch, eg. exec sp_foo 'bar'. Besides correctness, obviously hand-made tracing can eliminate known, often called, procedures and focus just on the ones suspected of never being called. I see many advantages. –  Remus Rusanu Jun 6 '13 at 6:39
    
You can filter on object name to eliminate other non-procedure calls as well as ones you know are used. And include SP:Starting to catch things that are part of a batch. Still think that's much easier. Don't think automating this change to every stored procedure on the system is quite as simple as you make it, plus it eliminates another piece of information you have about the procedures by overriding modify_date. –  Aaron Bertrand Jun 6 '13 at 11:14
add comment

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.

share|improve this answer
add comment

Run a trace on something like:

  • SP:Completed
  • SP:StmtCompleted
  • RPC:Completed
  • SQL:BatchCompleted
  • SQL:StmtCompleted

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.

share|improve this answer
2  
For the trace I'd argue you really only need to capture RPC:Starting. You don't need to capture ad hoc batches or all the additional information that comes with the completed half of the events to identify which stored procedures are actively being used. Also, of course, this will only help identify those procedures going forward. –  Aaron Bertrand Jun 5 '13 at 16:42
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.