October 12, 2012 at 5:01 am
I can't seem to be able to convince the development manager, who I report to(which is abnormal in most settings) that shrinking the database will not improve performance. I've tried to describe what occurs in a shrink in detail to them(even a written document which links to articles from renowned
DBA's with alot more experience than me) and I can't seem to convince them. I've told them repeatedly
that the only thing a shrink database will do is unallocate unused space from the database back to the
disk drive which the database is going to have to acquire everntly again anyhow at a great cost to perfromance because when the database has to acquire more extents, everything comes to a halt until the space is acquired. They seem to think that this unused space is leaving holes in the pages. They are getting confused with shrinking the database and fragmentation. I run a script every weekend to rebuild all of the indexes that have a certain percentage of fragmentation.
Can anyone give me more ammunition that what I've stated about? My manager told me that at his old
company they shrinked the database on a weekly basis and they want to do this here too.
I think I have a creditability problem here, but, I know I'm right
October 12, 2012 at 7:25 am
ericwenger1 (10/12/2012)
I can't seem to be able to convince the development manager, who I report to(which is abnormal in most settings) that shrinking the database will not improve performance. I've tried to describe what occurs in a shrink in detail to them(even a written document which links to articles from renownedDBA's with alot more experience than me) and I can't seem to convince them. I've told them repeatedly
that the only thing a shrink database will do is unallocate unused space from the database back to the
disk drive which the database is going to have to acquire everntly again anyhow at a great cost to perfromance because when the database has to acquire more extents, everything comes to a halt until the space is acquired. They seem to think that this unused space is leaving holes in the pages. They are getting confused with shrinking the database and fragmentation. I run a script every weekend to rebuild all of the indexes that have a certain percentage of fragmentation.
Could you get a detailed trail of thought posted here as to what exactly your manager thinks shrink will do?
Shrinking depends on the shrink operation you perform, you can just release unused space without reorganising data which is quickest but will only shrink to last used extent. This sometimes will not yield any results, so don't be surpried if the size doesn't change at all.
The next operation you can perform will reorganise data pages and then truncate free space. This operation takes time and will likely introduce new fragmentation into the database where the pages have been moved around.
Now here's the thing, you'll run your nice little index rebuild scripts and guess what??
Yep, the database will grow again to accomadate the rebuild 😉
Constantly shrinking and growing the files is an expensive operation and should be avoided. If you know the database will consume 100Gb of data then realistically you need to pre size the file at 200Gb or even more to support the current data, new data and maint ops like rebuilds.
ericwenger1 (10/12/2012)
My manager told me that at his oldcompany they shrinked the database on a weekly basis and they want to do this here too.
I don't wish to offend or sound rude, but he shouldn't be playing with anything that requires batteries or mains power!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 12, 2012 at 8:48 am
ericwenger1 (10/12/2012)
...Can anyone give me more ammunition that what I've stated about? My manager told me that at his old
company they shrinked the database on a weekly basis and they want to do this here too...
It doesn't sound like your manager has an actual reason, other than someone else did it. You really can't do much, since there is no technical reason to argue against.
If you are forced into shrinking the database, I would suggest leaving at least as much free space as your largest table and a little bit more, since the index rebuild will need that. If you don't know how to do that, this script may help:
Shrink DB File by Increment to Target Free Space
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355
Of course, you should immediately rebuild or reorganize your indexes after doing a shrink. If you have heap tables, you should put clustered indexes on them before doing the shrink in order to be able to reorganize the space.
Is there some performance problem you are attempting to solve?
October 12, 2012 at 11:59 am
Your best bet here is to provide external documentation and evidence (blogs, SQLTeam white papers, etc) as to what shrinking actually does to the speed if it's done over enthusiastically. Regrowth times, drive fragmentation due to spastic growth, and then fragmenting indexes and the like during maintenance if done too far.
But in the end, if he's not believing you, hand him a list of 20+ links that will bring him to the same conclusion over.. and over... and over... If he won't listen to you, sic the gurus on him via their published work.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply