July 30, 2015 at 4:06 pm
Hello,
I have a table (named table1) with 20million rows. It takes around 11 minutes to apply the primary key to this table. There are some tables with over 100 million rows so based on the previous time if my calculations are correct it will take close to an hour apply this primary key for tables with around 100 million rows.
My current solution is to create another table (named table2) with no indexs or primary keys. Pump over only like 5 days worth of data, then apply the primary key. Then have a script that will eventually populate table2 with the rest of the data gradually. When I say gradually I mean like insert like every 100k per hour or something. Keep in mind this table2 is heavily updated with new records.
Is there another solution to this problem? If not any concerns with this idea except for the tran log growing because of the inserts? Appreciate the help.
July 30, 2015 at 8:46 pm
I think you have a great plan, with a few conditions:
1) Are the rows in the original table just ADDED or are there also UPDATES and DELETES? If just the former, you are fine. If there are either of the other two things you are screwed because you would need to track all of that in the new table too, while you added rows to it.
2) I wouldn't do just 100K per hour. I would migrate them in batches that were small enough that you got index seeks on the thing controlling the batching. Then you could do 5-10K per batch and just put in a waitfor delay '00:00:01' or whatever to give the table and server some breathing room. Locking should not be a problem with index seeks on the acquisition of rows. I have done similar operations with billion-row tables before with no issues.
3) You can use dbcc sqlperf(logspace) or some DMVs probably to keep an eye on tlog size during your looping and execute backups as necessary.
4) Obviously explicit transactioning and error checking are mandatory in this evolution.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 31, 2015 at 10:19 am
Thank you for the rich feedback!
There are no updates or inserts going on to table1 only to table2.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply