Tuesday, March 27, 2012

Database Growth Options

Please, is there an advantage for using a static megabit number over using a
percentage and what will be a recommeded growth size for a 3gb database.
Thanks in advance for your assistance.
KenO
In a production environment, relying upon the Autogrow features is outside
of 'Best Practices'. Not bad to have as an 'emergency fallback' option, but
the database filegrowth 'should' be actively managed and extended during
'slack' operation times.
If you 'MUST' reply upon the Autogrow features, then the use of the
database, the new/changed data flow, and the number of users are important
considerations. A database used only for reporting may not need any
Autogrowth, whereas a very active order entry database with hundreds of
users may need to 'grow' in large chunks.
The primary thing to remember is that Autogrow has a performance impact, and
may result in fragmented datafiles, so you don't want it happening often.
Grow by large enough chunks to handle a substantial amount of need.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"KenO" <KenO@.discussions.microsoft.com> wrote in message
news:69A126EC-D842-4C3A-A482-FE89B44F4781@.microsoft.com...
> Please, is there an advantage for using a static megabit number over using
> a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO
|||KenO wrote:
> Please, is there an advantage for using a static megabit number over using a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO
Imagine your database is 214GB, and you've set it to auto-grow at a 10%
interval. The next time it grows, you're going to have to wait for it
to write 21.4GB of empty space. Might take a while.
Ideally, your database should never grow automatically. You, as the
DBA, should plan accordingly, and adjust the database size as needed
during planned outages or quiet periods when the overhead of growing the
database won't affect performance.
How much to grow by? Again, YOU need to determine that. If you add
600MB of data each month, and you have a monthly outage window, then
you'll want to add AT LEAST 600MB to the database during that outage.
You also need to consider disk fragmentation. The more often you add
space to the database, the more "pieces" the database will be broken in
to. You can reach a point where the database is so badly fragmented
that performance begins to suffer, due to the need to hunt around on the
disk for all of the fragments.
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Ken
http://www.sql-server-performance.com/database_settings.asp
"KenO" <KenO@.discussions.microsoft.com> wrote in message
news:69A126EC-D842-4C3A-A482-FE89B44F4781@.microsoft.com...
> Please, is there an advantage for using a static megabit number over using
> a
> percentage and what will be a recommeded growth size for a 3gb database.
> Thanks in advance for your assistance.
> KenO

No comments:

Post a Comment