Tryin to load 20GB of data in 40 batches (0.5 GB each) using LOAD TABLE .. FROM CSV command in a table which is 200GB in size before my load commands. few of these writes sometimes (not every-time) takes more than 30 minutes to execute as opposed to 10-12 seconds which is usual. If i monitor sp_active output during this 30 mins i can see MainMBUsed increases from 0 to 200GB. It seems DB thinks this is a concurrent write and tries to take snapshot of entire table. I'm not sure that is what it is though.
It is not fixed which of these 40 writes will end up causing DB to snap entire table.
Can someone help me with what is going on and how to prevent it ?
Here's what i have tried.
- time delay between each 40 write, 30 seconds to 2 minutes
- set transaction isolation level read committed/uncommitted