I'm the process of planning the live migration of a 2TB database to partitioned tables. The system is broadly speaking a document store, the majority of the space being allocated to LOBs of between 50kb and 500kb, with a small percentage in the 500kb to 1MB range. Part of the migration will involve BCPing data from the old to new database.

BCP is the preferred approach as the current/historic divide in the data permits extracting the older data in stages (during quieter periods) in advance of a final switch, minimising impact on the live system. The volume of data and availability of storage precludes an in-situ rebuild to a partition scheme.

I'm suspect there maybe some performance gains to be had by experimenting with KILOBYTES_PER_BATCH rather than ROWS_PER_BATCH, due to the BLOB content. It's suggested in the BCP documentation that SQL can optimise the operations based on this value.

What I can't find is any guidance on the nature of these optimisations or where to start my testing. In the abscence of suggestions I'll try short runs at 4/8/16/32/64mb boundaries to start.

Probably some gains to be from changing the packet size (BCP -a parameter, rather than server level setting) but I'm inclined to bump this to the maximum 65535 unless anyone has a more formulaic approach.


1 Answer 1


This is not a direct answer to your question, but there are some articles that you would benefit from reading them (in case you didn't find them first :-) ). They are about loading lots of data using bcp/bulk copy. I've read them all and I didn't find anything detailed regarding KILOBYTES_PER_BATCH, they're all using ROWS_PER_BATCH, but I'm sure you will find other useful information.

and the obvious MSDN references:

In my personal experience, I succeeded to make a fast data load using parallel load and testing with several batch sizes. I guess that only personal testing will suit you. Hopefully you will find some good advices in the references.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.