During bulk data loads with an Azure SQL Database Managed Instance, we noticed a significant performance hit as we imported data into staging tables. It turns out a Managed Instance default file growth for data and files is 16 MB!
Change your Managed Instance default file growth size now!
With the database file growth set so low, SQL Server grinds to a snail’s pace when importing large amounts of data. In our case, I changed the file growth to something reasonable given the batches of data we were loading like this:
1 2 3 4 5 6 | USE [master] GO ALTER DATABASE [AnalyticsDB] MODIFY FILE ( NAME = N'data_0', FILEGROWTH = 200MB ) GO ALTER DATABASE [AnalyticsDB] MODIFY FILE ( NAME = N'log', FILEGROWTH = 200MB ) GO |
After I changed the default file growth size, our SQL Server database performed as expected.
