Jason-299789 (5/29/2014)
I am working for a company that has a 3rd party vendor application.The Table that having an ALTER Column run against it is a 300 column heap table (import only).
The vendors application issues a number of ALTER TABLE ALTER COLUMN (sometimes 50-60 separate columns) for each import, The change is the data type length eg Decimal (18,2) to (30,10).
These seem to be taking a long time to execute especially when the table has around 4GB of data in it.
Is it possible that the problem is being caused because the data requires extra pages so the engine is spooling the existing table into the TempDB, and back again once the space has been allocated or is it a meta data only operation?
Not really answering your question, but surely altering the table with data in it is liable to failure, if any columns ever shrink?
If this is an 'import table', I would have expected it to be empty immediately preceding each import ...
But I guess the vendor has reasons.
If the columns are only ever getting bigger, why not make them all huge right now and do away with the ALTER issue?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.