BCP Out Slow?

I am running the following BCP to extract a table with 156641604 rows.

bcp TestDB..data out test3.bcp -T -b1000000 -a32000

When running this i notice that the disk read bytes\sec counter in performance monitor on the drive that has the database devices is only reading 30mb\sec. I am writing the bcp file to a different drive. Both drives are far more capable of achieving much higher IO. Is this a limitation with BCP or are there futher switches available that would speed this process up. Also the drives are both local so the bottle neck is not network. Any ideas?

Hi Andy,

Try not using the -a option, and reducing the batch size to 100000.

What is the purpose of extracting the data. Do you need it to be in char format.

If use -n for native, it will be a bit quicker.

Jag

|||

Hi Jag

That does help with throughput. The thing I don't understand is that even if I run a Select * From TestDB..data i get low read IO on this table. I know the disk IO is far more capable and if I run a DBCC SHOWCONTIG command for instance, the disk read bytes\sec counter in performance monitor jumps to over 70mb\sec. My question is, why doesn't BCP or a Select statement achieve this IO?

|||That's because it might not need to. The DBCC is going to explicitly hit the disk drive. Doing a BCP or a SELECT is going to utilize the normal infrastructure. It is going to read it from memory and then go out to disk as needed. Since SQL Server has an intelligent read ahead capability, it might only need 30mb/sec to keep up with the write process out the other side.|||Ok i can see what you are saying but the write IO is hovering around 30mb\sec also when it can write much faster than that. Is this IO limitation possibly due to the row count being so high, or some other factor?|||The row count shouldn't have any effect on this. BCP is going to run just as fast, regardless of the number of rows. It simply starts at the beginning and is done when SQL Server doesn't send anything else to it. I would suggest comparing this to the performance you get using SSIS. From everything that I've seen thus far, SSIS is going to move stuff a lot faster than BCP. Has to do with the basic interface that is used to connect to and get the data.