Greetings. I need to export several tables with LOB columns into new tables for long winded reasons. My current example has two XML columns on it, is 10 million records, and took about 12 hours to load through a job that calls SSIS.
A few fun facts:
Nobody else is on the box, resources are fine, no blocking, etc.
The source and destination are on the same box, the only real difference is they're on different disks, but this is a VM anyways, so not sure that really matters.
From googling I'm under the impression this is totally common. I've seen a couple suggestions, but not really clear on a common solution.
Questions:
Are there any settings within SSIS itself I can tweak to speed this up?
Is there an better option than SSIS -- Bulk Insert, Insert...Select, etc.?
Is there any reason to think something goofy like export all columns but LOB columns first, then do an update for LOB columns only would be faster?
Because resources are fine, is there any reason to not export several tables at the same time, as there won't be resource contention?
Editing the source to be an actual query instead of just relying on the GUI, I can convert one of the columns as such. If I use 1024, I get a warning about the column being too small for XML, but if I stick to 2048 I don't. Is this safe? Will it perform better?
convert(varchar(2048), [RequestHashInfo]) as RequestHashInfo,
My wait types are constantly ASYNC_NETWORK_IO -- why would this be under these circumstances?
Thanks!