1

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.

Improve this question
3
  • 1
    erikdarlingdata.com/… your log file grows to 2TB? sounds like on-prem a better option for your SQL Server. Wouldn't SQL Synaptics (or whatever MS marketing call it this week) be a better fit?
    – Mitch Wheat
    Commented Sep 7, 2022 at 23:23
  • @MitchWheat, Azure Managed Instances are a different beast. If you want decent I/O for your storage then you will grow your files (database and log) so that they will fit onto the appropriate storage tier. Larger files get put on larger disks with better I/O. I grew my 1TB database to 3TB so I could get acceptable disk I/O. sqlstarters.com/2021/05/28/…
    – Jonathan Fite
    Commented Nov 17, 2023 at 14:09
  • @JonathanFite - perhaps you'll find Paul White's latest on the subject interesting
    – Hannah Vernon ♦
    Commented Nov 17, 2023 at 15:13

2 Answers 2

Reset to default
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/

Improve this answer
1

I use Azure Managed Instances as well. When we first moved, we had issues with I/O performance and had to grow our files out so they would get onto larger disks and therefore onto better throughput.

When I did that, I used the "grow in 8GB chunks" until we hit our target size, as recommended by Kimberly Tripp here https://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

It's worked well for us and haven't had storage I/O issues since.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.