One of the questions that I often see asked in the forums is about which stored procedures and tables haven't been accessed in a long time. Six months seems to be a nice round number that is listed often and for whatever reason SQL Server 2000 is usually mentioned.
The standard answer is there is no good way of knowing this, set up a trace, track the usage, and similar suggestions. As I was looking over one of these posts the other day, I stopped and decided to question the poster. Why do you want to trim stored procedures? I can understand removing, or archiving data in tables that might not be used, but even then, if they're not really large, is it worth the effort? Do you need to try and clean things up?
So for this Friday, I'm asking a poll
How much pruning of stored procedures should you do?
If you have hundreds of stored procedures, and you suspect you're only using dozens, or a fraction of what's there, is it worth removing them? After all, the disk space taken up by most stored procedures is a tiny amount of disk space. They don't use up memory if they're not being called, and it takes up time to determine which ones aren't being used.
Time is a DBA's very valuable commodity, especially as we are constantly being asked to do more with less every year. I know that having lots of extra stored procedures listed in SSMS is annoying, and it offends most highly organized personalities. But does it really hurt? Is it worth taking time to clean up?
And can you even do a great job of cleaning things up? Even if you run a trace for six months and find that a procedure has never been called, does that mean it should be removed? What if that proc is only called once a year?
I've tried to clean things up in the past, usually when I have some downtime, but I think it's mostly been due to my desire for a more organized system than I was really benefiting my employer in any way. So let me know if you feel the same way or if you have some valid reasons for spending time on these types of tasks.
Steve Jones
The Voice of the DBA Podcasts
The podcast feeds are now available at sqlservercentral.mevio.com to get better bandwidth and maybe a little more exposure :). Comments are definitely appreciated and wanted, and you can get feeds from there.
or now on iTunes!
- Windows Media Podcast - 27.5MB WMV
- iPod Video Podcast - 22.5MB MP4
- MP3 Audio Podcast - 4.6MB
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.
