Home > Workload Solutions > SQL Server > Best Practices > Intel-Based SQL Server Best Practices on Dell PowerEdge R740 and PowerMax 2000 > Database Best Practices > Performance Best Practices > SQL Server 2019: Autogrowth
In this best practice, the default setting for autogrow is modified for both data files and TempDB. The goal of this recommendation is to tune autogrow to address database space requirements while minimizing the impact on transactions when the database increases the size of a data file or TempDB.
Category | SQL Server 2019 Enterprise |
Product | SQL Server |
Type of best practice | Performance Optimization |
Day and value | Day 3, Fine Tuning |
Overview
In this best practice we modify the default values for both data file and TempDB log to optimize how the database adds space and minimize the impact on transactions. The autogrow default values are as follows:
Database transactions that trigger the autogrowth of log space, along with other transactions using the log space, must wait until for the log files to grow to the configured amount. As each database is different, administrators must determine the proper autogrow value for their specific databases. To validate autogrow best practices, we implemented the following changes to our SQL Server databases:
Recommendation
Setting data files and TempDB autogrow to 1 GB did not significantly increase performance for the following metrics:
Although configuring autogrow to 1 GB did not significantly improve performance, this best practice is an important consideration for databases. We recommend that database administrators review the autogrow setting for databases as part of a Day 3, fine tuning exercise.
Implementation Steps
Perform the following command to modify data files and TempDB autogrow to 1.
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = '/mnt/temp_data/tempdb.mdf', SIZE = 8192MB, FILEGROWTH = 1048576KB);
Additional Resources
Considerations for autogrow and autoshrink settings in SQL Server