May 30, 2016 at 4:21 am
Hi All,
I am working on a proof of concept to export data from a large table (1.500.000.000 rows) so we can import that into a new table.
The whole idea behind this has to do with a column data type change. Currently there are smallints in the source table that need to be altered to a normal int.
So i thought of the strategy to export all the data, insert into a "staging" table with the correct datatypes. Also making sure this table is using the same partitioning as the source table.
After that is all done i alter the source table to the correct datatypes and do a partition switch.
I have used BCP a lot, so this sounded like the right aproach. However. When i do a BCP out i only get about 9000 rows/second. Which will take me to a total of 46 hours, just for 1 table, i have 4....
I am using BCP with the -a option set at 65535. The statistics op BCP show me that only half of it is being used (32k). Without the -a option the results are even more drama.. only 1500 rows/sec and a network packet size of 4k
Any thoughts on this? I am looking to make this one faster... even without BCP if that is possible
May 30, 2016 at 4:29 am
Quick question, what value are you passing with the [-a packetsize] parameter? IIRC, if none is passed it will revert to the default 4096k
😎
May 30, 2016 at 4:36 am
Eirikur Eiriksson (5/30/2016)
Quick question, what value are you passing with the [-a packetsize] parameter? IIRC, if none is passed it will revert to the default 4096k😎
tried 64k, 32k and nothing
64k will result in 32k
and nothing will result in 4k
May 30, 2016 at 4:10 pm
We've previously run into hardware limitations (it was old hardware which has since been replaced). First, going from the big machine to Network Attached Storage had a couple of problems. The switches were setup incorrectly (half duplex instead of full, auto-negotiate was on, etc). Once they fixed those, we were running at 60-80 MBytes/Second. Not lightning quick, but acceptable for a one-off.
But it would only do that for 12 minutes and then taper off to just 2 or 3 MBytes/second. The problem turned out to be the bloody drives being used. It would handle the 60-80MB/Second until cache on the drive filled and the the darned thing would "go synchronous", which was a mode as frightful as old Apple Talk I.
Have your hardware guys check the path between your database and your disk target.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 30, 2016 at 7:09 pm
Why BCP at all? You're adding the expensive steps of converting all of your data into and back out of text while forcing all of it to be written, flushed, and re-read from storage.
The target of your data move is inside the database that holds the source data. Stay in that context.
Create the new table with only the clustered index, batch-commit-insert the data in clustered index order, build all the non-clustered indexes, catch up the current rows, then rename when you're ready.
I've taken that approach in a 24x7 environment on tables with billions of rows while many processes continue to read and insert data in those tables. A well-crafted trigger can copy any changes to the original data after those rows have been copied. By using controlled, committed batches during the copy, you can easily set up that copy to be stopped during heavy database use and restarted, picking up where it left off.
-Eddie
Eddie Wuerch
MCM: SQL
May 30, 2016 at 10:42 pm
I am thinking that the change that is being done (datatype change from SMALLINT to INT) is really a metadata change and SQL Server should be able to do that without the need to actually re-load any data. Just Alter the column definition (ALTER TABLE...ALTER COLUMN) and you are done.
NOTE: this is not what SSMS does so don't use its scripts. SSMS re-creates the table and does copy data from the old to the new one.
My understanding is that SQL Server does not need to move any data for this scenario. It keeps versions of the metadata for each table and when access data it applies the correct version of the metadata and converts it as necessary to the current version. When a row is updated, the metadata that is the current version is used (not sure but this is probably done for all rows in the page).
So, do you need to BCP out and back - I don't think so.
Of course, I do recommend that you test this.
May 30, 2016 at 10:49 pm
mark.kremers.prive (5/30/2016)
Eirikur Eiriksson (5/30/2016)
Quick question, what value are you passing with the [-a packetsize] parameter? IIRC, if none is passed it will revert to the default 4096k😎
tried 64k, 32k and nothing
64k will result in 32k
and nothing will result in 4k
Can you tell us more about the source system, the destination system and the connection between those two?
😎
June 1, 2016 at 5:54 am
Well..
I know now why BCP was so "slow"
Storage is fast and no CPU or Memory pressure. However, with a table with 1.5 billion rows and doing a BCP will give a load on storage and network. BCP only worked with 32k network packet sizes. i gave 65k for the -a option, however, it said it only used 32k. Not sure why.. but probably some network setting i think.
The 5/6 MB/s was not normal in our case, should be way higher (100-140 MB/s). But since this table is enormous i think that the size was the culprit here.
What i did to solve this problem?
We are using partitioning on this very table.
I looped through the amount of partitions and created a new "worktable" to switch 1 partition.
Then i update the datatype on this new table.
This is done for all partitions, in my case 133 partitions, so 133 worktables.
After all the partitions were switched i updated the datatypes on the original table and did a switch back.
From 40 hours (approx., didn't let the BCP finish) to 7 minutes....
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply