SQL Server Performance

Alternative to alter table?

Discussion in 'T-SQL Performance Tuning for Developers' started by Kryptonit, Oct 8, 2007.

  1. Kryptonit New Member

    I need to add a few fields in a table that is really big (many million rows, +30 GB).
    Is "alter table" the best way of doing this?
  2. satya Moderator

    Yes, by default and during offline hours on the database will do lot better.
    Also ensure to take care of transaction log growth during this process is executed.
  3. Kryptonit New Member

    I've notised that the transactionlog is used during the execution, so I suppose it will have to be greater then the table that is being modified. (The recovery model is set to simple during the changes.)
    Any tips for speeding up the execution?
    After seaching for similar topics here I'm going to see that there aren't any triggers on the table. I'll also drop all indexes, run alter table and then recreate the indexes.
    (The database is going to be offline during the changes, but it's important that the downtime is kept to a minimum because the system is normaly up and running 24/7.)
  4. satya Moderator

    Better to run as a Job on the server and keep the transaction log in SIMPLE mode, while doing the aLTER table perform frequent log dumps WITH NO_LOG in order to keep up the log size. This is required for the databases in SIMPLE recovery model, for the transaction log usage and resize.
    Also better to get the process practice you should test it on the development environment.
  5. Kryptonit New Member

    Great tip [:)] I'll make the preperations today and test it tomorrow.
    To increase the transaction log from 4 GB to 35+ were kind of a problem due to free diskspace in some of the enviroments. (I'm going to upgrade several test enviroments before the real upgrades take place.)
  6. satya Moderator

    Do not attempt straightaway to increase the log, better to test it first and then set to 10gb or 15gb.

Share This Page