Production table - Need to Alter 4 existing columns and increase the size and precision

Question

Production table - Need to Alter 4 existing columns and increase the size and precision

DanielP

SSCrazy

Points: 2320
More actions
November 8, 2012 at 9:33 am

#264606

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

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.

DanielP SSCrazy Points: 2320 More actions · Answer 2

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.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

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.

ScottPletcher SSC Guru Points: 101126 More actions · Answer 4

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

DanielP SSCrazy Points: 2320 More actions · Answer 5

DanielP

SSCrazy

Points: 2320

November 9, 2012 at 10:50 am

#1557730

Thank you.

ScottPletcher SSC Guru Points: 101126 More actions · Answer 6

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".