Published: May 01, 2015
This is another bug-bear of mine… it’s something I believe should always be set at the time of creating a database and then periodically checked and altered where necessary... and this applies not only to the data file but to the log file as well.
I keep seeing instance upon instance in which the growth settings have not been altered from the defaults provided by the Model database after installation. I don’t know if it’s because people don’t understand the consequences of these settings, or if it’s simply an oversight, but either way it’s something I will check on any new instance I see.
As we all know, SQL Server comes with the Model database… a database from which all new user databases obtain their basic settings (recovery model, growth settings, database options etc). But what many people seem to forget is that these basic settings are generic and not tuned for your system… therefore they need to be altered where appropriate.
For example, if you have a system which only has one 5Gb drive and you are only keeping tiny databases on it then yes, the 1MB growth settings for the data file are probably fine.
But if you are working on a larger scale, have several 1TB drives in your server and are creating a database which will be 200GB from the outset and grow 10GB a day, then you really want to be considering something a little larger than 1MB autogrowth… maybe more like 500MB, for example.
In our latter example of the 200GB fast growing database, using 1MB as the autogrowth could have numerous disadvantages… firstly you have the fact that inserting 10GB a day means that SQL will be forever increasing the datafile… all of which generates extra IO and causes SQL to slow down whilst these 1MB files are written. Also, you’re potentially introducing incredible amounts of fragmentation on the disk itself.
Consider a situation in which you have five 100GB databases on a drive, each is receiving 1GB data per day… if you have them all set to 1MB autogrowth then database 1 will grab 1MB on disk, then database 3 might grab 1MB, then database 4, then database 5, then database 3 again, then 2, then 4, then 5, then 3, then 1… that means that in growing just 2MB database 1 has become badly fragmented (having eight 1MB files between them on disk). Imagine this on a massive scale and you can see the disk IO issues you are introducing.
This is also the case with log file growth, but to an even greater extent as it also impacts VLFs… but that’s something for a later post I think.
Basically the lesson here is that you need to gauge your database growth and amend your settings accordingly. This is why I would always recommend taking a guestimate when you first create your database and then re-visiting once it’s in use and you can get actual figures. Either way, PLEASE don’t just leave the 1MB default in place!!!