SQL Server tempdb files growth increment size

Question

In our Azure SQL Managed Instance, we have 12 tempdb data files each of size 11GB. The Log file growth value is set to a max default size of 2TB. The growth value of each data file is 32GB.

Question: Based on the above numbers, what should be the growth increment size of these 12 tembdb data files? NOTE: Question is specific to tempdb data files.

Remark: As a best practice recommended by Azure documents, one should have growth increment of these files to a reasonable size to prevent the tempdb database files from growing by too small a value. If the file growth is too small compared to the amount of data that's being written to tempdb, tempdb might have to constantly expand. That will affect performance. NOTE: Ours is largely a data warehouse project where large amount of data is imported that is then used for Data Analytics.

Answer 1

If instant file initialization (IFI) is enabled (see sys.dm_server_services, column instant_file_initialization_enabled for SQL Server service), then growth value for data files is not so important than for log file. IFI doesn't work for log files and thus, the growth increment should be set to something not big, which doesn't take much time for SQL server to grow the file. Usually, the increment is a result of set&try attempts - start from, say, 512MB and see the how many growth events will happen during the day. The best option is to avoid autogrowing in the first place by correctly sizing the files.

Reference: https://www.brentozar.com/training/fundamentals-database-administration/tempdb-config-9m/ https://www.brentozar.com/archive/2016/01/cheat-sheet-how-to-configure-tempdb-for-microsoft-sql-server/