SQL Server Performance

ALTER TABLE taking a long time ?

Discussion in 'Performance Tuning for DBAs' started by oneilldo, Nov 13, 2003.

  1. oneilldo New Member

    Does anyone know why ALTER TABLE seems to take so long. For instance
    consider a small example table called TestTable. Adding a new field does

    ALTER TABLE dbo.TestTable ADD

    which surely should be fairly quick. If I had the same table and one field allows NULL's on it, and I wanted
    to take off the NULL constraint then it would do the following
    CREATE a tmp table
    Then Insert into it everything from the original table.
    I know it does this as I was profiling at the time.

    I would like to know how it takes SQL Server 2000 over twice as long to do the ALTER TABLE than to remove the NULLS
    as surely they are very similar things (i'm guessing that ALTER TABLE does a create table and insert into behind the scenes, so how does the addition of the new field, which
    is probably done during the select into, cause it to take twice as long.) The time taken also increases dramtically when the table contains any indexes.
    I tested this on a table of 40 million rows.

    Any help would be much appreciated on this query.

  2. satya Moderator

    If any other job is running currently on that table or while processing any DBCC commands then changing such sequence on the table is prone to slow performance.

    You can monitor the event by using PROFILER for assessment.


    Satya SKJ
  3. oneilldo New Member

    Sorry I forgot to say. Nobody else was connected to the DB at that time, no other Jobs were running on the DB (I was using profiler whilst I was making the change). I made the change using Enterprise Manager and added a new field that way. Anyone else know why it takes SQL Server 2000 so long to add a field compared to removing NULL's?
  4. Twan New Member

    Don't use EM to do DBA work... You can't control what it does behind the scenes. Remember that EM has to cater for every eventuality, so it will create a new table, drop all fks, constraints and indexes on the original table, rename the old table and then rename the new table, recreate all fks, constraints and indexes.

    Use Query Analyser instead... With the default=-1, remember that it will have to update every row whereas a nullable column does not

  5. oneilldo New Member

    Thanks for your help.
    However taking the nulls off in EM also created a new table, inserted everything into it and then renamed them. I still dont understand how this took twice as long as just removing the nulls, I know that it would have to allocate more space for the new field, but it was only an int field, how can this take twice as long as removing the nulls off an existing field?

    I also forgot to mention that this table did not have any indexes on (so it cant be that) and it also had no other constraints specified.
    I don't use EM normally I was just generally wondering whilst adding a new field takes twice as long as removing a NULL constraint from an existing field as both operations seem very similar.
    ANy more help would be much appreciated
  6. richmondata New Member

    Just to echo an earlier reply. Don't use Enterprise Manager for most DBA work, especially DDL. EM can be dumb as a box of rocks and frequently picks the least efficient way to do the kinds of operations you described. As to why EM, might do one thing a little faster than another, you would have to monitor what it's actually doing in the background. Based on my experience, there is no situation in which EM does a given task faster than an equivalent SQL command. The other plus to using SQL commands is that they can saved for audit trails, repeatability, etc.
  7. oneilldo New Member

    I know EM is not very good, however I was using profiler at the same time so it would
    show exactly what was running.
    I suppose the real question is what is the difference between using ALTER TABLE to add a new column and using CREATE TABLE to create a table with the new column (and the existing old columns) and then inserting everything from the old table into this new table (with the new field on it) using INSERT INTO.

    So why does ALTER TABLE take longer than the other method even if the table has no indexes on it and no other constraints?


    ALTER TABLE dbo.TestTable ADD


    CREATE TABLE dbo.Tmp_TestTable
    ( other field names.....
    TestID int NOT NULL
    ) ON [PRIMARY]

    IF EXISTS(SELECT * FROM dbo.TestTable)
    EXEC('INSERT INTO dbo.Tmp_TestTable (Other field names...,TestID )
    SELECT Other field names..., TestID FROM dbo.TestTable TABLOCKX')

    The tables are on the same file group
    The 1st example takes twice as long as the second!

  8. Twan New Member

    The first statement will require SQL to go through every row, try to extend it. It will have to move the row if it can't fit in the same slot (due to another row being in the slot next to it)

    The second statement will be set based, which may well be much faster if there are no indexes, constraints, etc. to put back

  9. oneilldo New Member

    I'm sorry Twan, I dont understand what you mean by slots. Do you mean SQL Server 2000 will try to extend each row on the data page and if it cant it will cause a page split that will slow it down, whereas the other way is set based (does this mean that it will insert multiple columns at once and hence the read and write operations will be faster cos it wont keep stop-starting). and because its set based its faster as it will be inserting multiple rows into the new table at once. If the second version is faster, and it can yeild the same end result then why does EM use alter table, when it could theretically do it using create table and then insert into, which as we have seen is a lot faster.

    Thanks for all the help
  10. Twan New Member

    Hi ya,

    yeah I mean that in the alter table case SQL will ahve to move rows and causes fragmentation in the table, this will be slow and result in a larger table size. The create table, insert is a fairly simple operation and fairly fast.

    I'm not sure why EM uses alter table, I don't think it used to... perhaps it has been 'improved' for cases where there is only a new column added to the end of the table?


Share This Page