Autogrowth should only ever be considered as a pressure relief valve to an acutely bad situation (no more free space in the database - that’s really bad!) and should never be considered the strategy for database right sizing. Every time autogrowth events occur, there is a cost: time @@ (I’ll come back to the @@ in a moment) and depending on the disk subsystem, fragmentation of the physical files themselves.
Let say you have a large-ish database at 200GB. No more free space in the database file. End user adds a record to the system, and the autogrowth event happens. By default the autogrowth settings expand the database files by 10% - well, adding 20GB to the mdf takes more than zero time and in some situations I’ve seen it take more than the autogrowth timeout that SQL has (30 or 60 seconds; I don’t recall the specific timeout) and it rolls back the operation.
You may be wondering if our concern is with a large autogrowth event at one time that could cause a transaction to roll back/timeout in the middle of the day(and cause issues that present in many different ways), why doesn’t the Config Check just check one of the following instead?
- the autogrowth settings themselves to make sure it never grows more than 1GB at a time instead of the default 10%?
OR
-
@@ that SQL server service execution account has the database instant file initialization privilege so autogrowth is almost a zero millisecond operation?
If you are comfortable with the PROs and CONs of those adjustments then those are definitely options available to you. We ultimately decided that the Config Check should be used to encourage people to right-size their database to prevent any autogrowth events (which is the best case) instead of affecting how long the autogrowth event takes as our view is that autogrowth events should be avoided all together. Regarding the @@, there are some legitimate security concerns when that is enabled, but, SQL 2016 presents users with the option of granting the SQL Service user account the zero initialization privilege so…
TL/DR: with this rule we’re trying to prevent one of many possible issues from occurring at all and to help customers get into the habit of knowing their db growth patterns. For best possible performance and stability databases need a little TLC from time-to-time, and making sure there is enough free space in the database for new transactions (aka: right sizing) on a regular/planed basis is a very good thing.
LoL