Pruning

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQLServerCentral.com » Editorials » Pruning

38 posts, Page 1 of 4

1 2 3 4 »»»

Pruning

Rate Topic

Display Mode

Topic Options

Author

Message

Steve Jones - SSC Editor

Posted Thursday, February 26, 2009 9:45 PM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 11:09 AM
Points: 31,416, Visits: 13,730

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

owen_west

Posted Thursday, February 26, 2009 9:51 PM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 8:30 PM
Points: 10, Visits: 251

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

P Jones

Posted Friday, February 27, 2009 1:11 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 7:35 AM
Points: 515, Visits: 1,016

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

James Stover

Posted Friday, February 27, 2009 2:00 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 10:37 AM
Points: 263, Visits: 859

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

Perry Dyball

Posted Friday, February 27, 2009 2:58 AM

Valued Member

Group: General Forum Members
Last Login: Monday, January 28, 2013 11:29 AM
Points: 73, Visits: 93

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

Peter Blatchford

Posted Friday, February 27, 2009 3:11 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, March 09, 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

rgillings

Posted Friday, February 27, 2009 5:35 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 12:13 PM
Points: 1,388, Visits: 235

In my iopinion, pruning can be very advantageous when it comes to bringing new technical resources into your database.

Clutter = Distraction

Post #665624

SuperDBA-207096

Posted Friday, February 27, 2009 5:43 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 02, 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

Grant Fritchey

Posted Friday, February 27, 2009 5:43 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 10:37 AM
Points: 13,375, Visits: 25,158

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

AndyG-538510

Posted Friday, February 27, 2009 6:15 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 08, 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 »

38 posts, Page 1 of 4

1 2 3 4 »»»

Permissions