Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Pruning Expand / Collapse
Author
Message
Posted Thursday, February 26, 2009 9:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 1:16 PM
Points: 33,202, Visits: 15,345
Comments posted to this topic are about the item Pruning






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #665489
Posted Thursday, February 26, 2009 9:51 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 4:00 PM
Points: 10, Visits: 376
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. :D

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
Post #665490
Posted Friday, February 27, 2009 1:11 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 21, 2014 4:21 AM
Points: 561, Visits: 1,168
Is it groundhog day? :D If so I'll repeat last week's post and call for stored proc to be organised into folders within the database.
Post #665538
Posted Friday, February 27, 2009 2:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, September 8, 2013 5:39 PM
Points: 263, Visits: 862
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
Post #665555
Posted Friday, February 27, 2009 2:58 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 5:32 PM
Points: 73, Visits: 94
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.



Post #665573
Posted Friday, February 27, 2009 3:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 9, 2009 5:18 AM
Points: 3, Visits: 23
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.
Post #665576
Posted Friday, February 27, 2009 5:35 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 9, 2014 4:10 PM
Points: 1,388, Visits: 239
In my iopinion, pruning can be very advantageous when it comes to bringing new technical resources into your database.

Clutter = Distraction
Post #665624
Posted Friday, February 27, 2009 5:43 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711
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
Post #665629
Posted Friday, February 27, 2009 5:43 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:09 AM
Points: 15,661, Visits: 28,050
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #665630
Posted Friday, February 27, 2009 6:15 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 8, 2009 10:04 AM
Points: 107, Visits: 156
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
Post #665647
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse