February 26, 2009 at 9:51 pm
We have a third party (vendor) application which uses a SQL Server 2000 database, and for which we (the internal IS department) are responsible for developing crystal reports (using stored procedures).
Currently we have in excess of 1000 reporting stored procedures and each time we get a vendor upgrade each one of these procedures needs to be checked against database structure changes, verified and sometimes modified to work with any database changes.
By tracking which reports have actually been used (and hence which procedures have been used) we can remove the unnecessary stored procedures and (hopefully) reduce the number of database objects that we need to check each application version upgrade. 😀
Assuming each procedure check takes 10 min, 1000 checks = 10000 minutes = 166 hrs = 20 days (8 hrs per day). That's a developer for almost a month, every time we get a version upgrade. That's a hefty investment in payroll and other things not getting done.
If we can remove even 100 procedures, that's 1000 minutes - that's a 2-day time saving. Gotta be happy with that.
Owen West
Programmer,
Hunter New England Health
February 27, 2009 at 1:11 am
Is it groundhog day? 😀 If so I'll repeat last week's post and call for stored proc to be organised into folders within the database.
February 27, 2009 at 2:00 am
Did something just change in The Matrix? Because I'm pretty sure we already had this discussion 🙂
But hey, pruning stored procedures. Hmmm? Maybe if I'm really looking for something to do.
James Stover, McDBA
February 27, 2009 at 2:58 am
I guess I must be one of those people with a tidy mind but this is something I am quite passionate about.
Having said that there is no need to be obsessive about it, but doing a cleanup once in a while has many benefits but the most important is reduction of wasted effort when changes to tables/views are made.
I took a new role once, where both db and web code were in a horrendous state. 1000's of object in the database, 1000's of web assets and no one had any idea what everything did. When the business wanted a change made, doing an impact analysis on the code threw up 100's of changes every time.
This was totally unsustainable so myself and collegues set to task to clean things up via db and web logging. It took some time, but eventually we removed 67% of ALL code in the system with no impact on the end product. It meant 2 out of every 3 'blind' proc changes were a complete waste of time.
That then allowed us to actually take that product forward as when impact analysis was then done, we knew that we were not wasting our time. Productivity and quality skyrocketted as a result.
Having had that exeperience I have done the same at my next 2 jobs, both with similar results.
Once the system is clean - then put the db under source control, build processes and change management and voila - you never have the worry again.
Periodic cleanups then keep everything in order - and trust me its not as burdonsome as you may think.
February 27, 2009 at 3:11 am
Pruning is not something that needs to be done all the time but we have recently made massive changes to the structure of our main database, we currently have nearly 1000 stored procedures in our database and every single one of these would have to be recoded to handle the different table structures and any logic around the new usage. If I hadn't taken the time to investigate what procedures were in use and which were obsolete I wouldn't have been able to reduce the workload down to less than half that.
I did, however, take a different approach. Rather that just go through trace files, I did the following..
1. checked through the source code of all applications for each of the database objects.
this reduced the number of stored procedures that were possibly in use, and although laborious, didn't take too long, most of the time being taken up by the search rather than by me. All these procs were pruned
2. run a short term trace to isolating procedures which were definitely used
3. of what was left I edited the remainder with a simple logging procedure which logged their last use, left this on for a number of months and then revisited what had been logged.
Of course I have the advantage of having the source code but it saved the developers months of pointless extra work so they were happy to provide me with it.
February 27, 2009 at 5:35 am
In my iopinion, pruning can be very advantageous when it comes to bringing new technical resources into your database.
Clutter = Distraction
February 27, 2009 at 5:43 am
My 02c... Pruning is a good idea, but you need to be sure. it's a pay me now or pay me later deal, if not pruning put them in a different schema, 'cause they are a distraction and could lead someone down a wrong path when troubleshooting or changing an application.
Another time they could be a distraction is when a new DBA or developer is looking at a system/making changes and grabs the wrong sproc, and spends hours trying to figure out what's wrong.
the ideal situation is when a sproc is no longer used/needed it's deleted then (by the person who created the replacement sproc/deleted the job/ etc.)
Anyway, thats been my experience...
Mark
February 27, 2009 at 5:43 am
Will they still compile? Leave 'em there. If not, clean 'em out.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 27, 2009 at 6:15 am
My philosophy on pruning (for procs anyway) has been to only prune procedures that (1) are completely unused, and (2) can easily be confused with other stored procedures because of their names or their internal logic or structure...we definitely don't prune much.
AndyG
February 27, 2009 at 6:38 am
Here is my opinion. The thing that promotes efficiency is knowing where something is when it is needed or what has an impact on what. As “the-man-in-the t-shirt” 🙂 🙂 said, it’s not the space allocated. So therefore keeping an easily searchable database list of the above information is more important. It is then easier to relocate obsolete stuff to a “Temporarily-Unused” area, if you’re so inclined to make it less “Cluttersome”.
February 27, 2009 at 6:39 am
Wow! Its' deja vu all over again. :crazy:
Baseball is back... yeah!! :w00t:
February 27, 2009 at 6:45 am
A couple of posts above suggest relocating the obsolete procs, I'm not sure what the point of that would be, if you've identified that they are obsolete why keep them at all, they are going to be stored in source control if they need to go back, why do they need to be in production?
February 27, 2009 at 6:48 am
Peter Blatchford (2/27/2009)
A couple of posts above suggest relocating the obsolete procs, I'm not sure what the point of that would be, if you've identified that they are obsolete why keep them at all, they are going to be stored in source control if they need to go back, why do they need to be in production?
Pete,
Sometimes it's not simple, you think it's obsolete and later find out its something that runs once a quarter or once a year. Relocation provides an easy way to go back.
Mark
February 27, 2009 at 6:55 am
Ditto on this statement. If have to make a change that affects stored procedures that are not even used and proceed to change those time is wasted. A clean DB make change management much easier 🙂
owen_west (2/26/2009)
We have a third party (vendor) application which uses a SQL Server 2000 database, and for which we (the internal IS department) are responsible for developing crystal reports (using stored procedures).Currently we have in excess of 1000 reporting stored procedures and each time we get a vendor upgrade each one of these procedures needs to be checked against database structure changes, verified and sometimes modified to work with any database changes.
By tracking which reports have actually been used (and hence which procedures have been used) we can remove the unnecessary stored procedures and (hopefully) reduce the number of database objects that we need to check each application version upgrade. 😀
Assuming each procedure check takes 10 min, 1000 checks = 10000 minutes = 166 hrs = 20 days (8 hrs per day). That's a developer for almost a month, every time we get a version upgrade. That's a hefty investment in payroll and other things not getting done.
If we can remove even 100 procedures, that's 1000 minutes - that's a 2-day time saving. Gotta be happy with that.
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply