SQL Server Performance

speeding up table modifications

Discussion in 'Performance Tuning for DBAs' started by oneilldo, Sep 2, 2003.

  1. oneilldo New Member

    I have several large tables and occasionly these need new fields adding to them.

    I normally use Enterprise Manager for this. This process can take a long long time
    with some tables (33,000,000 or so rows) and has took about 2 hours in the past.
    The problem with it taking this amount of time is that the DB is used throughout the day

    Does anyone know how to speed this up?
    I know that Enterprise Manager uses ALTER TABLE when adding new fields, does this first delete the exisiting indexes? If not, would deleting them increase performance dramatically? I currently increase the size of the transaction log to avoid the need for it to keep growing. Is there any other things that can be done to speed up table modifications of this sort?

    There is no way that I can decrease the number of rows and the tables tend to be fairly narrow.

    Cheers
  2. gaurav_bindlish New Member

    Deleting indexes may increase the speed of the ALTER TABLE but that will depend on the size of data being modified.

    ALTER TABLE does not delete indexes until the index keys have changed.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  3. bradmcgehee New Member

    Based on my experience, use Transact-SQL code, not EM for this job. First, delete the indexes, make the column addition, then readd the indexes. It still won't be really fast, but faster than using EM.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  4. Twan New Member

    Why would you delete and create indexes to add a column? I've never had a problem adding a column to a table with a large number of rows... If the table has a clustered index, then that is the only index that might be affected by adding a column if rows move to different pages due to space problems... Unless you're talking about a table with lots of non-clustered indexes and no clustered index...? Not sure what the performance difference might be here...<br /><br />EM doesn't use alter table... it creates a new table copies the data, creates the indexes, drops the old table and renames the table (I probably have the wrong order of things) Profiler will tell you precisely what EM does.<br /><br />In short, don't use EM to make changes to tables. Scripts are much more efficient, can be easily repeated and you know exactly what goes on <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Cheers<br />Twan<br />

Share This Page