smartbulkcopy
smartbulkcopy copied to clipboard
SmartBulkCopy was fast only initially but slowed down for last heavy tables
I am trying to move ~1TB of data from one sql server to another sql and it has many tables with ~10heavy tables( more than~700gb size). I set the config file with 2 threads initially and then later 8 and then 12 threads. I observed that SmartBulkCopy(SBC) ran quickly with good network speed used up the threads and later slowly degraded once most of the small to medium sized tables are finished and left with ~5 heavy tables and also network speed dropped too. Finally we end up with ~4hours to migrate ~1TB of data. Why is this happening and how to speed up the process here?
What are your recommendations to migrate heavy tables?
How can we ensure the parallel threads use up network speed?
FYI, We disabled foreign key indexes and dropped non-clustered index to move fast