Home > Workload Solutions > SQL Server > Best Practices > AMD-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, we modified the autogrow setting from the default value for both UserDB and TempDB. The goal of this best practice is to tune autogrow to address database space requirements while minimizing the impact on transactions when the database increases the size of a UserDB or TempDB files.
Category | SQL Server 2019 Enterprise |
Product | SQL Server |
Type of best practice | Performance Optimization |
Day and value | Day 3, Fine Tuning |
Overview
The autogrow default values include the following:
Modifying the default values for both UserDB and TempDB files helps optimize how the database adds space and minimizes the impact on transactions. By increasing the size of autogrowth the database benefits by not having excessive autogrowths events. Thus, the impact to the database is minimized and operates more efficiently. Database administrators must determine the proper autogrow value for their databases, as each is different. For validating autogrow best practices the following changes were made to our SQL Server databases:
Recommendation
Increasing the data and log file growth for UserDB and TempDB did show minor performance improvements in the following metrics:
There was no significant improvements in the metrics below:
We are recommending database administrators review the autogrowth setting for databases as part of a Day 3, fine tuning exercise.
Implementation Steps
The script we used to resize the data and log files, repeat the same for all files that need to be updated is provided below:
USE [master]
GO
ALTER DATABASE [TPCC500GB] MODIFY FILE ( NAME = N'TPCC500GB', FILEGROWTH = 1048576KB )
GO
We can use the ALTER DATABASE command to resize the TempDB data/log files. Amend the script for each TempDB file.
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