SQL Server Performance Forum – Threads Archive

very slow alter table

Hi… does anyone have any insight into why an alter table that adds a float column with a default constraint would take a long time? The table has on the order of 25 million rows. The amount of time is 17.5 hours and counting! One oddity is that the default is specified as 0 and not 0.0. The filegroup that is involved is on a drive with a tempdb file. Anecdotal evidence indicates that this alter has run in past test passes in a handful of seconds! Are there any known issues that might be relevant? This is happening on SQL Server 2000 (8.00.760 SP3). TIA,
Dave

Are you running with rescticc access to database?
I mean are you along when you run Alter?
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Just to confirm, youre doing ALTER TABLE in T-SQL, and not using enterprise manager right ? take a look at the process list.. sp_who2, see if this highlights anything, existing locks maybe. Also you could run perfmon and see if the bottleneck is hardware activity such as disk etc. I personally think the default of 0 vs 0.0 is not an issue.

Hi Luis. The only connections to the database are the process that is running the alter (through a java program) and one or two Query Analyzer sessions that are occasionally selecting from a table that is monitoring how long various portions of the upgrade (this is part of a data model upgrade) are taking. It has now been running for about 19.5 hours. It really smells like an infinite loop in sql server… We are about to kill it… but I don’t have a good theory about the cause so I don’t really have any ideas for how to fix! thanks,
Dave

Because post time, I suppose you don’t read Chappy post. Is important to know your answer.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Sorry! I did miss the post from Chappy. The alter is being run as a jdbc Statement. Definitely not through Enterprise Manager. sp_who2 shows the following, but nothing else of interest. Nothing blocking or anything like that. CPUTime DiskIO
7798530482438930 CPU use is very low. Perfmon shows that the K drive is very busy. This is the drive that has the filegroup in which the alter is occurring and a tempdb data file. There is a pattern, the K drive oscillates around 80-90 percent for a while and then flatlines at 100% for a while before dropping back down to the 80-90. I just killed the process. This alter is part of an upgrade. The table being altered is actually created earlier in the process. We had the alter as a separate file instead of changing the original creation in order to be able to upgrade dev databases that were in intermediate states. The code was tested on large datasets and didn’t have problems before. But we have decided to simply merge the alter back into the original create and circumvent the problem entirely. Still, this is quite worrisome. But, thanks for the thought processes! If you have any theories, please let me know. I am going to have the database around for 3-4 hours yet, so I can poke around – sort of an autopsy, I guess you could say. Thanks,
dc

I suggest to drop all index, alter table and recreate index.
Luis Martin
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

It’s taking so long because it’s having to update the entire table to set the new value for the new column. Luis’s suggestion may help. It may be quicker to create a new table and insert into.. select from your existing table, create indexes, drop old table, rename new table.. Does the table have any triggers? This will further slow it down. I’ve found in the past adding columns like this is very slow and is best approached in the manner i’ve described. Tom Pullen
DBA, Oxfam GB

Thanks Thomas. There is an update trigger that syncs up an update datetime field. I bet that is a large part of it. Your approach with the intermediate table makes sense. Like I said, though, we decided to just merge the alter back into the create, which occurred in an earlier upgrade file, so we won’t even be doing the alter at this point. But, I have another test upgrade in progress and I have disabled the trigger, so I am curious to see how much of a difference that makes! Thanks again,
dc

]]>