Accidental DBA - Autogrowth

Question

Accidental DBA - Autogrowth

JBB9

Grasshopper

Points: 16
More actions
December 14, 2018 at 2:55 pm

#409382

Hypothetical: New fast SSD server / main db auto growing during working hours by 1+ gb or 10%...what impact would users see in a web based platform?
DB's on one partition / tempdb/C: on another - all sitting on same back plane as a raid
How about a db growing all day 1mb at a time? Impact?

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

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

JBB9 - Friday, December 14, 2018 2:55 PM
Hypothetical: New fast SSD server / main db auto growing during working hours by 1+ gb or 10%...what impact would users see in a web based platform?
DB's on one partition / tempdb/C: on another - all sitting on same back plane as a raid
How about a db growing all day 1mb at a time? Impact?

Will need more information here, what are the initial sizes, the recovery model etc.?
😎

Database growth is something that a DBA needs to stay on top on!

Jeff Moden SSC Guru Points: 1003895 More actions · Answer 2

While SSDs are certainly more forgiving the physical drives when it comes to fragmentation and other things, there's still no way that I'd set autogrowth to a percentage. People tend to lose track of what's going on over time and you can end up in a position where you get to the point where a 10% growth is way too big for the remaining capacity and you end up with a major problem.

SSDs are also still comparatively expensive. They should be treated as if the space on them were gold. The more you can prevent waste, the more you can use for the things you need them for.

As for a 1MB growth, it may not affect you much for MDF/NDF files but it will still affect performance of the log files thanks to the VLFs there, especially when it comes to restores. It's kind of like Read Aheads when SQL Server needs to read from "disk" to memory. While the "disk" (SSDs) is much faster than in the past, SQL Server still reads them as if they were physical disks. The size of the Read Aheads that SQL Server can do are still affected by the order of the physically numbered pages compared to the logical order of the pages. So it is with size of the VLFs in the log file. And, yes, small is still bad but don't forget that "too big" is also bad.

--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)