Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Pruning

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: 296 | Views in the last 30 days: 1
 
Related Articles
BLOG

Podcasting

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

ARTICLE

Podcast Announcements

Podcast Feeds

SCRIPT

Nested Stored Procedure

Nested Stored Procedure

FORUM

Stored Procedure

complex stored procedure

FORUM

Store Procedure

Store Procedure

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones