Just want to make sure I have my facts straight about autogrowth.

Question

Just want to make sure I have my facts straight about autogrowth.

Duran

SSChampion

Points: 13259
More actions
June 9, 2015 at 8:10 pm

#304876

Hey Everyone.
I just wanted to confirm something, if I have given the right to Instant File Initialization, the same process occurs when the files has to grow (everything stops while it resizes), but just does it quicker, right?
Someone is saying that as that's the case, they are growing there database in 1mb incremements, because its instant. But I'm thinking this is still a bad idea, because surely then the database is split up into 1MB files/fragments all over the SAN, making data retrieval slow?
Just wanted to make sure I am thinking along the right lines. Thanks for reading!
Regards,
D.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

It depends on a couple of things but, generally speaking and considering the size of today's databases, auto incrementing by 1MB is a horrible thing to do especially with the Log file where you also have to deal with VLFs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Duran SSChampion Points: 13259 More actions · Answer 2

Hi Jeff,

Thanks for the reply, you mentioned that it depended on a couple of things, in a nutshell, what are those things?

Regards,

D.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

Duran (6/9/2015)
Hey Everyone.
I just wanted to confirm something, if I have given the right to Instant File Initialization, the same process occurs when the files has to grow (everything stops while it resizes), but just does it quicker, right?
Someone is saying that as that's the case, they are growing there database in 1mb incremements, because its instant. But I'm thinking this is still a bad idea, because surely then the database is split up into 1MB files/fragments all over the SAN, making data retrieval slow?
Just wanted to make sure I am thinking along the right lines. Thanks for reading!
Regards,
D.

If you're going to all the trouble of enabling Instant File Initialisation then you want to grow the data files by more than 1MB, that's just ridiculous, the physical file fragmentation will be huge.

I typically grow data files at 1GB or 2GB increments with IFI enabled

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

Duran (6/10/2015)
Hi Jeff,
Thanks for the reply, you mentioned that it depended on a couple of things, in a nutshell, what are those things?
Regards,
D.

Pretty much what I said in my previous post... the size of the database and, in this case, I mean the size of the expected growth over at least a month.

To be a bit more detailed for what I do, I agree with Perry whether IFI is available or not. If it's available, then it's a "set it and forget it" type of thing. If it's not, then I don't want file growth to take me by surprise because it almost always happens when you can least afford it. In that case, I setup a monthly job to grow it for the next month's worth of anticipated data (I use a 125% of what I expect a month to be unless I know something big is going to happen). Note that I do NOT ever set autogrowth to a percentage.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Duran SSChampion Points: 13259 More actions · Answer 5

Thanks for the reply, peeps!

'1MB, that's just ridiculous, the physical file fragmentation will be huge.'

I couldnt agree more!

Jeff, Yep I always change it to MB from percent.

Thanks again!

D.

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 6

Agree with the above. You might also keep a few placeholders around: https://voiceofthedba.wordpress.com/2014/11/24/placeholders-for-emergencies/

ultimate sql Mr or Mrs. 500 Points: 575 More actions · Answer 7

All the good advices. Just to add my 2c, I would prefer to have the auto growth only catering for emergency growths for most of my databases. We plan, or make the business owner plan and anticipate for at least a year worth of growth and then increase/add space on routine basis.

For some databases that are shared on an instance with more critical databases, I have even disabled the auto growth, but added an alert to poke DBAs to add space when the database is reaching limit.

[font="Tahoma"]Fahim Ahmed[/font]
[font="Times New Roman"]Knowledge is a journey, not a destiny [/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Steve Jones - SSC Editor (6/11/2015)
Agree with the above. You might also keep a few placeholders around: https://voiceofthedba.wordpress.com/2014/11/24/placeholders-for-emergencies/

You just have to remember to be a bit careful with what get's backed up at a system level. No sense in backing up place-holders.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 9

Jeff Moden (6/11/2015)
Steve Jones - SSC Editor (6/11/2015)
Agree with the above. You might also keep a few placeholders around: https://voiceofthedba.wordpress.com/2014/11/24/placeholders-for-emergencies/
You just have to remember to be a bit careful with what get's backed up at a system level. No sense in backing up place-holders.

That's someone else's job, but you can use an extension that get's excluded. Most software could be set up to look for .place.