Today, I want to discuss autogrowth in SQL Server. When creating a new database, SQL Server sets default values for autogrowth to handle sizing of the database files as they grow over time. As of SQL Server 2016, these defaults come from the values set in the model database. Unfortunately, these defaults are going to be terrible for most databases. I’ve most often seen databases with values of 1 MB for data files and 10% for the log file.
Why is that a problem?
If your database is configured for your data file to grow every 1 MB and your database is growing multiple MB a day, then you will be experiencing multiple growth events. These growth events mean more physical fragmentation which means your database is going to perform more poorly.
Configuring a file for a percentage can also cause problems, moreso as databases get larger. If databases are growing based on percentage, the amount of disk space for growth will grow as well. If your databases are a few MB then this isn’t as significant. But what about 10% growth for 100 GB? What about 10% growth for 1 TB? This can become an easy way to have a lot of wasted space caught up in autogrowth.
What should autogrowth be set to?
First, I would avoid anything with percentages. That leaves us with setting a value by MB. Considering what MB growth to set, the answer is…it depends. In a perfect world, you can manually grow your databases every month or every few months as needed and not be concerned with unplanned growth events. Unfortunately, we do not live in a perfect world. Maybe by the end of 2021 we’ll be there but I’m not holding my breath. Instead, the consensus I’ve seen is setting data files to 256 MB and log files to 128 MB.
Your mileage may vary. This isn’t the perfect answer but is going to take your performance further than a setting of 1 MB or 10%.
Setting Database Autogrowth
Assuming a database called ExampleDB with a data file and log file, the following script could be used to set autogrowth to the 256 MB/128 MB consensus:
|
1 2 3 4 5 6 7 8 |
USE [master] GO ALTER DATABASE [ExampleDB] MODIFY FILE ( NAME = N'ExampleDB', FILEGROWTH = 256MB ) GO ALTER DATABASE [ExampleDB] MODIFY FILE ( NAME = N'ExampleDB_log', FILEGROWTH = 128MB ) GO |
If you prefer making the change in the SSMS GUI, you can right click on your database, select Properties, select the Files page on the left, and adjust your autogrowth from there:
