Database Reference
In-Depth Information
sized data files ensures even growth and more predictable performance.
The next important point is that you should preallocate all your data files and transaction
log files. This will eliminate the need for the database to constantly grow the files and
resize them, which will degrade performance and put more stress on your storage
platform. The files can't be accessed for the period of time they are being extended, and
this will introduce avoidable latency.
It is a Microsoft best practice and our recommendation to manually and proactively
manage file sizes. Because you are presizing and proactively managing your database
files, you shouldn't need to rely on Auto Grow as much. Even though it may not be
needed, we recommend that Auto Grow be left active as a safety net.
Tip
Auto Grow should be set to grow at the same or a multiple of the underlying
storage system block size. In VMware environments, the block size on data stores
will be between 1MB and 8MB. Your Database Auto Grow size should be set
similarly, or at a multiple of this. Auto Grow should not be configured for
unrestricted growth; it should be limited to less than the size of the underlying file
system, taking into consideration the size of any other files on the file system. See
VMware KB 1003565.
If you are unsure what your underlying block size is, set Auto Grow to a multiple of
1MB. To prevent Auto Grow from being active too often, consider configuring it to
grow at around 10% of your initial database size rounded up to the nearest 1MB (or
block size), up to a maximum of 4GB. In most cases, an Auto Grow amount of 256MB
to 512MB should be sufficient. This will ensure the grow operation doesn't take too
long and is aligned to the underlying storage subsystem.
Caution
Because Auto Grow will by default zero out all the blocks and prevent access to
the files during that period, you don't want the operation to take too long. You
also don't want these operations to happen too frequently. Therefore, the Auto
Grow size needs to be small enough that it completes in a reasonable time but not
too small as to require constant growth. The database file sizing guidelines need
to be adjusted based on the performance in terms of throughput of your storage
and the workload behavior of your database. If you are proactively managing the
size of your database files, then Auto Grow should not be kicking in at all and
this shouldn't be a concern.
