Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 9 down vote accepted

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.

share|improve this answer
    
Thank you Marian, I've bookmarked a few new finds from that exhaustive list. As a one off task, many of the incremental/refining steps are not as useful but there are plenty of tips in there I can make use of. –  Mark Storey-Smith Aug 26 '11 at 10:55
    
Yep, I get your feeling, for me too was a one time task, and found some useful stuff in the list. It's a great task though :-). You can also do a small .NET application (if you're familiar with .NET), like in another article from Linchi Shea: Performance Impact: the Most Optimal Insert Script can't Beat BulkCopy. You might find the same as he did :-). –  Marian Aug 26 '11 at 11:55
    
As there doesn't seem to be any BLOB specific guidance for BCP out in the wild, I'm marking your very thorough answer as accepted. Thanks again. –  Mark Storey-Smith Oct 17 '11 at 18:33
    
Sorry I couldn't help you more, but I hope you found something useful in it. –  Marian Oct 17 '11 at 21:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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