SQL Server: Bulk Insert Data Loading to Partitioned Table with Multiple File Groups

Question

I am trying to load a series of CSV files, ranging from 100MB to 20GB in size (total of ~3TB). So, I need every performance enhancement that I can. I am aiming to use filegrouping, and partitioning as a mean. I performed a series of tests to see the optimum approach.

First, I tried various filegroup combination; the best I get is when I am loading into a table that is on 1 filegroup; with multiple files assigned to it, and they are all siting on one disc. This combination outperformed to the case that I have multiple filegroups.

Next step was naturally to have partitioning. ODDLY, all the partitioning combination that I examined have lower performance. I tried defining various partition function/schemes and various filegroup combinations. But all showed a lower loading speed.

I am wondering what I am missing here!?

So far, I managed to load (using bulk insert) a 1GB csv file in 3 minutes. Any idea is much appreciated.

Answer 1

For gaining optimal Data Loading speed you need to first understand SQL Server data load process, which means understanding how SQL Server achieves below mentioned optimizations.

1. Minimal Logging.
2. Parallel Loading.
3. Locking Optimization.

These two article will explain in detail how you can achieve all the above optimizations in detail. Fastest Data Loading using Bulk Load and Minimal Logging and Bulk Loading data into HEAP versus CLUSTERED Table

Hope this helps.