Home / server / Questions / 36696
Accepted
Chris
Asked: 2009-07-07 15:17:48 +0800 CST

How To Speed Up Adding Column To Large Table In Sql Server

  • 772

I want to add a column to a Sql Server table with about 10M rows. I think this query would eventually finish adding the column I want:

alter table T
add mycol bit not null default 0

but it's been going for several hours already. Is there any shortcut to get a "not null default 0" column inserted into a large table? Or is this inherently really slow?

This is Sql Server 2000. Later on I have to do something similar on Sql Server 2008.

sql-server

9 Answers

  1. Hmm, 10M rows is a quite few, but it's not outside the realm of MSSQL and that does seem very slow.

    We had a table with a huge row size (poorly designed) and over 10M rows. When we had to modify the structure, it was def. very slow, so what we did was (to keep the table online, and this is rough from memory because it was a long time ago):

    • Created new table with the suffix "C" (for Conversion) and new structure (i.e. same as old one, but with new column/index/etc)
    • SELECT * INTO tableC FROM table
    • sp_rename 'table' 'tableOld'
    • sp_rename 'tableC' 'table'

    This way it doesn't matter how long the conversion takes, as the old data is online. It might cause issues with rows being written to the table whilst the conversion takes place though (this wasn't an issue for us as the data was only written once daily, but queried thousands of times an hour) so you might want to investigate that.

    • 10

  2. You could try performing each step of the operation in a separate batch, e.g.

    alter table T add mycol bit null
go
update T set mycol = 0
go
alter table T alter column mycol bit not null
go
alter table T add default 0 for mycol
go

    Advantages are:

    • You get better feedback on the progress of the operation, since it is now 4 separate batches each taking roughly 1/4 of the time.
    • It reduces the likelihood of timeout errors when running it from client-side code.
    • I find that it sometimes improves performance.

    You could also try dropping all nonclustered indexes on the table before making the change, and restoring them afterwards. Adding a column may well involve large-scale page splits or other low-level re-arrangements, and you could do without the overhead of updating nonclustered indexes while that is going on.

    • 10
  3. Best Answer

    Depending on your row size, table size, indexes, etc, I've seen SQL Server 2000 grind away for several hours (4-5ish hours) before FINALLY completing.

    The worst thing you can do right now is "panic" and hard kill the thing. Let it run itself out.

    In the future, you may wish to try doing what Farseeker mentioned and create a second (empty) structure and copy your records over that way.

    • The longer the table row, the longer it will take.
    • The more indexes you have on that table, the longer it will take.
    • If you add a default value (which you did), it will take longer.
    • If you have heavy usage on the server it will take longer.
    • If you don't lock that database or put it in single user mode, it will take longer.

    When I have to do ugly stuff like this I try and do it at night... like 2am when nobody is on it (and maintentance is NOT running on the server).

    Good luck! :-)

    • 8

  4. This will take quite a while. Its because you are adding the default value. This is causing the SQL Server to update all the rows in a single transaction. Ensure that noone else is using the table as this will cause blocking of your process.

    • 5

  5. I have done similar things in a table with at least 65million rows and it did not take that long. Do you have enough memory and a enough performance in the disk system

    If you want to speed up the process you can remove all indexes execpt clustered index and foreign key constraints before you alter the table, but it has to be done when the system is not use, or else you may end up with inconcistent data. But in the end you will need to apply the foreign keys and the indexes before you are done, but you will ease the pain for the transaction log, at least if you run in simple recovery model. And in SQL server 2008 you can build the indexes with ONLINE=on and SORT_IN_TEMPDB=on

    Håkan Winther

    • 1

  6. You are not really going to shortcut something like this - no matter what you do SQL Server is going to have to do some processing on all the rows in the table.

    You could ensure it runs as fast as possible by making sure that your data files and logs are on separate drives and the other usual recommendations.

    • 0

  7. Hours for 10m rows is far too long. Check that nothing is holding locks open on the table.

    • 0

  8. At one training course I had a conversation with a couple of DBAs from the DoD. They manage MySQL databases of 100TB and more. Table changes are done with dump and load but that obviously requires some down time. They also mentioned they don't like doing this with databases over 10TB because of the time taken.

    The data is dumped, they didn't specify what to but I'd assume SQL files. The tables are then truncated and the schema altered as required. The data is then reloaded.

    • 0

  9. Did you happen to have a number of indexes for your table, and may even be a clustered index on your table T?

    I also had problem adding a new column (it is an identity column). The table had 9.3 million rows, and it has one non-clustered index on the primary key.

    For some reason if we drop the index for table T, follow by adding the column, then add back the index for table T. It was basically 60X faster on the Standard SQLServer 2008 .

    I haven't figure out why it speeded up so much, hopefully someone can give me answer for this.

    • 0