Auto Grow and Performance

Question

Auto Grow and Performance

Angela Henry

SSCommitted

Points: 1545
More actions
August 31, 2006 at 12:52 pm

#60760

All,
Just need a reality check here.
Been having a "discussion" with others regarding the performance of databases that have auto grow turned on. We inherited a database that was originally sized entirely too small (less than 10GB). In addition to it being sized to small to begin with, some genius decided that it would be a great idea to shrink the database on a nightly basis. Needless to say when the database started showing performance problems it had grown to about 60GB in size. Gee, I wonder why they had such poor performance (read with extreme sarcasm). That's when we inherited "fixing" this database.
Long story short, we built a brand new database, sized at 200GB (based on growth statistics that were provided to us and disk space limitations). We transferred all the data to the new database and like magic, no more performance problems. Haven't had an performance problems since either (related to this specific issue, bad code is another story!). Needless to say we also turned off that nasty little job that was shrinking the database on a nightly basis.
Now the "owners" of this little database are paranoid about the database autogrowing. It still has almost 40GB of the 200GB left available. Unfortunately the growth statistics we were provided were not accurate, so this database will continue to grow beyond the 200GB. After some more calculations I figured out that it would grow every 5-6 months or so. Given our hardware we're looking at less than 2 minutes to grow 20GB.
So my question for a reality check here is this: Is a 2 minute lag once every 5-6 months an acceptable performance hit? Mind you we are not dealing with health care or money, i.e., no one will die or even be harmed or lose any money because of this lag. Or is this my former lazy programmer habits shining through?
Thanks
-A.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

DHeath SSCertifiable Points: 5200 More actions · Answer 1

I think the 2 minutes lag time is borderline acceptable but you have to remember this. Is it 2 minutes everytime that db is hit? or just initially. Also it makes ya think will the next 20GB after the first 2 min accumulate another 2 minutes so now we are sitting on a 4 minute lag time? at that rate then you are surely to know its unacceptable but IF 2 minutes is the worse it will ever be well its not the greatest but it will do. Take the challenge and make the cpu sweat to zero lag time hehehehe

DHeath

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 2

The biggest issue with autogrowth is that it will usualy occur at the most inopportune moment. I'll not touch the growth parameters since that coiuld be another entire thread. What helps is automated space monitoring to warn you when space thresholds for growth are met. This way you can step in and manually perform the growth at the most opportune time ( during the least system activity and having minimal impact).

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Philip Yale-193937 SSCarpal Tunnel Points: 4603 More actions · Answer 3

The required time to grow the database won't be cumulative, as long as you increase by a fixed amount each time instead of a percentage (which WOULD be cumulative).

Does the database share the disks with anything else? If not, then why not grow the database manually now to use 90% of the available disk space? This will leave you some in reserve should you get close to the new limit before you've bought new disks, but will mean that you can leave autogrow off altogether.

To answer your specific question - I wouldn't regard a 2-minute lag every 6-months as a problem, particulary in a system where performance is not a major consideration (it would be different if you were running a trading system or something similar, but you say you're not).