February 27, 2009 at 6:58 am
Mark, thats true, but how much more difficult is it to get it from source control? I don't like throwing anything away, even if it isn't actively used anymore, it may make a comeback so we move all this stuff into a different area within source control but I like to keep production as clear as possible.
February 27, 2009 at 7:00 am
Hi Steve,
I got this message in place of the daily SQL Server Central newsletter. Was there a problem with the outgoing message?
FILE QUARANTINED
----------------
The original contents of this file have been replaced with this message because of its characteristics.
File name: "Body of Message"
Virus name: "CorruptedCompressedUuencodeFile"
Thanks,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
February 27, 2009 at 7:21 am
In a common development environment, the available stored procedures and functions are the only things available to the application developers to Select, Insert, Update, and Delete data. It is worthwhile to prune the programmable objects so that developers don't attempt to use things that are obsoloete, incorrect, or never worked properly in the first place. Keeping it pruned would then avoid a number of questions and a great deal of confusion by all. Another reason is if it is found that multiple programmable objects do virtually the same thing, it would be beneficial to drop all but one of them and make all code go through the same procedure dedicated to performing a certain function. So in my opinion, the best reason to keep the programmable objects pruned would be to provide the best support possible to the development teams, who are (in my case) the consumers of the programmable database code.
February 27, 2009 at 7:26 am
I just recently upgraded a database from 2000 to 2008. There was a bit of code that had to be looked at for depreciated and obsolete features. (Granted, the upgrade advisor helps tremendously with this.) Having just the procedures that are actually used will drastically improve on checking out everything.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 27, 2009 at 7:58 am
I'm also in the "only drop the stuff that's never used" camp. the systems i work in are under constant development, so sometimes one approach to a problem will be tried and abandoned, leaving behind unused junk. on the other hand, we don't have the kinds of motivations to clean stuff out that some others have mentioned, so there's no large-scale pruning.
February 27, 2009 at 8:29 am
Obsolete methods (proceudres, views, functions) are a big headache in a system that is evolving because they introduce confusion. Sometimes the business change forces you to modify a design of the database. Without knowing what is used is is hard to properly redesign and in many cases to save time a quick patch is developed which on a long run creates more mess. And keeping stuff under source control does not help much especially if there are many different client apps accessing the same database.
I think the unused code should be clearly marked and the removed. I one case when I had a system that had to be redesigned inside out while still running I created a separate log SP to log when a stored procedure was executed the last time. Anything not called for over a year was first renamed and then after few months removed from the system.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
February 27, 2009 at 8:44 am
I think that this brings up the larger issue of how we manage our systems in general. Remember how downtown New York City was in the 60's and 70's? The way it got cleaned up was by starting to attend to the small things - the graffitti, the minor crimes, etc. This certainly didn't ignore the bigger things, but too often the little things were the rudder that was turning the whole ship.
That has been the approach to how just a couple of us manage our 100+ instances. We make sure each instance is standard, is reporting in to the central monitoring system correctly, that each directory is clean, that each userid and login match and stranded ones are cleaned up, that each backup occurs and is audited, etc, etc. By eliminating the little problems, we find that there are relatively few larger problems. We spend most of our being proactive.
I'm not sure how that directly applies to the cleanup of stored procedures - each situation would be different, but it is a perspective to have in mind.
February 27, 2009 at 8:48 am
Interesting split here. I'm mostly in the camp of not worrying about old stuff. I can see the issues with validating the old procs for upgrades/changes, so perhaps it's worth it in some systems. Most of my work when I'm not sure of where something is being used is to leave it alone.
Not sure what wires got crossed with last Friday's Ed. I think we had one scheduled twice.
The virus scan appears to be a false positive, but we'll take a look at see if there's anything there.
February 27, 2009 at 9:07 am
I know most of the real world isn't like this, but...
Developers need to track dependencies in comments in their procs/funcs..
PERIOD!
When all dependencies are gone, throw the proc in an unused folder until your sure you won't need to rewrite it.
So NO PRUNING by DBA alone. Developers are required, as we know removing procs breaks code.
February 27, 2009 at 9:37 am
I see discussions of this often enough that I think it would worth it for Microsoft to build some capability for this into the database engine.
It wouldn’t have to be on all the time, but it would be a big help to be able to record usage of stored procedures, views, functions, and tables. It would be even more helpful to be able to audit usage of specific objects.
February 27, 2009 at 10:02 am
Twice, I took a custom-built database for a custom-built application and decided to rebuild it from the ground up. Once, because changes in the business would be better served by a different core structure, once because when I originally built it I had made a number of "newbie mistakes" and rebuilding was the best way to remove those and make the database significantly better. Both were valid for the business, in terms of things the application would be able to support, performance, accuracy, efficiency, etc.
Where I started on this was working out which procs were in use, and how to replicate their inputs and outputs with the new table schema. There were hundreds of procs, and I knew some weren't in use, so it would save a lot of time and effort if I didn't include those in the rebuild.
Some couldn't be exactly replicated, and the applications they supported had to be changed. Most could be changed transparently. But first I had to know what was actually in use and what wasn't.
In that case, it made sense and had an actual, measurable ROI.
I can imagine circumstances where doing that kind of thing proactively might be helpful.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 27, 2009 at 10:21 am
Some of us work in very large shops with many servers and 'Production' database instances. When referring to 'Production' instances, especially the really large ones (we have some with 2000+ SPs), it gets to be a nearly impossible task to manage the SPs on these without some kind of last-used metric.
As for 'Development' database instances, once a development project is completed, the instance should be thoroughly cleaned before promotion of program objects to the 'Integration' or 'Acceptance Test' instance. This should prevent unused objects gettng into the 'Production' instance at the beginning. Once the full development cycle is complete, blow away the 'development' instance and recreate it as a copy of the 'production' instance (not including production data of course). This way you enter the maintenance phase of the lifecycle with a clean version.
Having multiple database instances for development staging has nothing to do with whether you are using waterfall or iterative rational or agile PM methodologies. It is just good development discipline.
From the prior posts, some seem to get it, while others don't. It would be great if Microsoft built in a last-used metric that could be checked periodically and either reports produced or emails sent identifying those potentially obsolete SPs. When there are so many objects like this, you absolutely cannot rely on the developers or the application maintenance team to clean up after themselves. However it usually is not a problem for them to review a relatively small list of potentials and identify any obsolete objects from that.
February 27, 2009 at 10:43 am
... Pruning ?
... we don't need no steenking Pruning !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
February 27, 2009 at 11:14 am
I am currently doing a schema change to base tables and subsequently need to update all procedures using the increased column size so I see a great use in being able to know which procs are used an which are not.
February 27, 2009 at 11:24 am
Yes it is re-editorial. 2/20/2009 and 2/27/2009.
As to the topic, I do try to clean unused SP's out, but will usually throw them into some documentable forum like a local WIKI, archive, etc.
It's a lot like my house, there is stuff that I do not use any more, but is not "trash", but that doesn't mean it cannot be stowed away for later use or given to a charity... OK, maybe that doesn't quite equate... the point is, is that most code is good for historical reference, and should be archived and retrieveable. But there are instances where Delete is just good business... like a forest fire is to new growth... or a car yard is for snagging snippets and parts for a new or similar vehicle...
Viewing 15 posts - 16 through 30 (of 37 total)
You must be logged in to reply to this topic. Login to reply