November 8, 2012 at 9:33 am
I have a request to increase the size and precision of 4 existing columns from DECIMAL (18,5) to DECIMAL (18,8) on one of our most highly used tables in production. What is the best method of performing this task to limit down time and contention?
It took approximately 2.5 hours to complete in a mock production environment by altering the existing. I am looking for alternative ideas.
Thanks,
Daniel
November 8, 2012 at 10:05 am
If may be faster to create a new table, load the data from the old table, rename the old table, and rename the new table.
November 8, 2012 at 10:07 am
Yes, that is one of the options I will test in our mock environment. Also going to test adding new columns, running a crawler to update the new columns with the correct data t ypes and than drop the old and rename the new columns and ReOrganize the clustered index. Pretty large table.
November 8, 2012 at 10:12 am
DanielP (11/8/2012)
Yes, that is one of the options I will test in our mock environment. Also going to test adding new columns, running a crawler to update the new columns with the correct data t ypes and than drop the old and rename the new columns and ReOrganize the clustered index. Pretty large table.
I doubt that the option of adding new columns will be faster. In fact, I would be surprised if it isn't much slower.
November 8, 2012 at 4:52 pm
You're not changing the physical length of the column, so it shouldn't be too bad.
My guess is that logging the change is taking the time.
Try it again in test but pre-allocate enough log space to do each ALTER.
For example, if the table has 500M rows, and since decimal(18, <any>) is 9 bytes, pre-allocate at least 4.6G of free log space (500M * 9bytes + 100M for miscellaneous control records in the log).
That much space will be needed for each ALTER (extremely frustrating that SQL can't do multiple ALTER COLUMNs at once).
So, you either have to pre-allocate enough log space total for all 4 ALTERs
OR
explicitly start and commit a separate transaction for each ALTER, with a log backup (or the equivalent) between each one so they can share the same log space
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 9, 2012 at 10:50 am
Thank you.
November 9, 2012 at 1:03 pm
To be safe, you probably want to add the length of the clustering key to the column to be ALTERed.
So, for example, if the clus key is 4 bytes, then (9 + 4) * #_of_rows + 100M.
I don't know remember the exact details of what data specifically gets logged for an ALTER.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply