SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in


By Steve Jones,

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

Everyday Jones

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.

Overall RSS Feed: or now on iTunes!

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.

Total article views: 300 | Views in the last 30 days: 1
Related Articles

Embrace Stored Procedures

Developers working with SQL Server should learn to embrace stored procedures. They really, really do...



A new video setup is on the way!!!! Actually I'll do a couple podcasts on podcasting over the hol...


Nested Stored Procedure

Nested Stored Procedure


Stored Procedure

complex stored procedure


Store Procedure

Store Procedure