import - SQL Server when importing large dataset and add second data file to DB filegroup, load performance becomes very slow

Question

We are loading 10 TB of CSV files into a table into a SQL Server database. The database initially had one data file in the Primary filegroup. We are loading with SSIS and the load was going quickly, approx. loading at a rate of 1 million rows every 15 seconds. Once the first database data file in the Primary filegroup grew to 16TB, we had to add a second database data file because of the SQL 16TB data file limit. However, after adding the second data file to the filegroup the performance of the imports dramatically dropped. The data load dropped to loading 1 million rows every 5-7 minutes.

We moved another table out of the Primary filegroup into another filegroup to free up space in the Primary filegroup. This immediate improved the performance of the data import again.

Why does the addition of a second data file to a filegroup cause this performance impact? Is there a way to get around this?

We are using SQL Server 2017 Enterprise. In our business we regularly get ad-hoc data from clients that we load into SQL so that our project teams can analyze. In this case the majority of the data is for one table. We did not have any indexes on the table so it would load as quickly as possible.

Any feedback or ideas is much appreciated.

Thanks!

question from:https://stackoverflow.com/questions/65861672/sql-server-when-importing-large-dataset-and-add-second-data-file-to-db-filegroup

Answer 1

Waitting for answers