Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Pruning


Pruning

Author
Message
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36308 Visits: 18752
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
My Blog: www.voiceofthedba.com
owen_west
owen_west
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 608
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. BigGrin

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
P Jones
P Jones
Say Hey Kid
Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)Say Hey Kid (682 reputation)

Group: General Forum Members
Points: 682 Visits: 1505
Is it groundhog day? BigGrin If so I'll repeat last week's post and call for stored proc to be organised into folders within the database.
James Stover
James Stover
SSC Veteran
SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)SSC Veteran (281 reputation)

Group: General Forum Members
Points: 281 Visits: 862
Did something just change in The Matrix? Because I'm pretty sure we already had this discussion Smile

But hey, pruning stored procedures. Hmmm? Maybe if I'm really looking for something to do.


James Stover, McDBA

Perry Dyball
Perry Dyball
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
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.



Peter Blatchford
Peter Blatchford
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
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.
rgillings
rgillings
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1390 Visits: 239
In my iopinion, pruning can be very advantageous when it comes to bringing new technical resources into your database.

Clutter = Distraction
SuperDBA-207096
SuperDBA-207096
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1479 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
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17635 Visits: 32268
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
AndyG-538510
AndyG-538510
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search