Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

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

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)
Waitting for answers

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...