This is a basic topic but one that crops up time and time again.

By default SharePoint will create databases with settings to grow 1 MB at a time. That means that if you add a 5 MB file the database will grow 5 times to fit it in. If you add a 250 MB file (the default largest size file for a 2013 farm) that means a worrying 250 growth operations will be needed.

Why is growth bad?
During a growth operation the database is completely unresponsive. That means that any access at all, read only or not, will have to wait for the growth to complete. That will result in slower responses for users and reduce the number of users that your environment can support at any one time. As such growth is bad.

How long does growing take
It varies depending on your hardware. In my test environment, a Hyer-V machine with a dynamically growing VHDX on an SSD I got the following numbers. I’m going to put up a post on how I got these numbers sometime soon, i’ll update this post once I’ve done so.

Growth Size / MB	Average time / ms	Average time per MB
1	13.40	13.40
10	46.11	4.61
100	328.17	3.28
1000	2480.67	2.48

If we plot that data on a chart we get a clearer image

The duration for growth operations scales with the size, however the change isn’t quite linear, there is an overhead to each growth that makes a single larger growth more efficient. As such it’s best to grow as few times as possible with larger individual growth operations.

On the other hand as you can see a single growth operation can take a significant amount of time. Growing by 1 GB took around 2 and a half seconds. That is a significant delay and whilst it will be infrequent your users may well notice, especially in a heavily used site.

For a 250 MB file the 1 MB growth rate would take 3.25 seconds. A 10 MB growth rate gives 1.15, 100 MB comes in at 0.98 and 1000 MB at 2.48 seconds (but you only have to grow every fourth item).

Category	Example Growth Rate
Default	1 MB
Max File Size	250 MB
Large Fixed	1000 MB
Nightly	2000 MB
Pre-grow	N/A

Default

This is more or less the worst possible choice. Every time a database grows it needs to lock the entire system. Whilst that might only be for a short time for each one the total time is excessive. As we saw this will take around 3.25 seconds to perform all the growth needed for a 250 MB file. The only redeeming feature of this setting is that it is very efficient for storage space.

Max File Size

By setting the file growth rate to the web application maximum file size you can ensure that only a single growth operation will happen for any file upload. This gives a relatively low individual growth duration which means the disruption for a single file will be as small as possible. On the other hand it will result in a lot more individual growth operations than a larger size. That means that in a highly used site you may have statistically significant and detectable amounts of downtime.

Large Fixed

By setting the autogrowth setting to an arbitrarily large size, eg. 1 GB, you can minimise the number of growth operations that need to occur. This has a much lower total disruption than the Default settings or setting to the maximum file size but when it does occur the delay can be significant. This is a popular setting.

Nightly Growth

As an alternative to autogrowth settings you can grow the databases ahead of the rate that users put data into them. To do this your SQL management team would set an alert on the database fill rate to ensure that there is more free space available than can reasonably be expected to be used in a day. If the database drops below that point then your SQL admins will grow the databases manually during the next scheduled downtime or period of low load.
This avoids/minimises any of the aforementioned disruption to users when growth occurs but requires an alert and capable SQL team. If your SQL team miss the alert then either a Max File Size or Large Fixed policy should be in place as backup.

Pre-grow

The most performant option is to grow your databases to their final size on creation. This requires a highly predictable environment with site collection quotas in use to prevent the databases growing beyond their expected size. It’s also a very storage-space inefficient way to manage your databases.

Summary

Which option you should use depends on where you’re putting SharePoint in. My preference is to set the growth settings to the maximum file size for the web application it is attached to. This provides the ideal blend of minimising total growth time whilst avoiding large delays when larger growth operations are needed.
If you have a good SQL team behind you to grow the databases nightly then that is an even better way to avoid disruption to users. However that does require the SQL team to be available and able to assist, if this isn’t available or you’re in a really high usage environment then you should pre-grow the databases up front and never have to suffer growth again.