7

I have a table with 45M rows (45 GB data space and 2GB Index space). I added a new column and it finished instantly.

alter table T add C char(25)

Then I found the size is too small so I run the following query.

alter table T alter column C varchar(2500)

And it runs one hour and is still running. sp_whoisactive shows (at the moment, still running)

reads: 48,000,000
writes: 5,000,000
physical reads: 3,900,000

Shouldn't it be really fast?

11

Assuming all previously-created columns are fixed-width like char and datetime, adding column C above just added it to the end of the fixed-width section of the record (effectively a meta-only change). However, recasting it as a varchar required it to be moved to the variable-width section of the record, forcing an implicit rebuild of the table. Paul Randal explains the internals of records in great detail in Inside the Storage Engine: Anatomy of a Data Record.

| improve this answer | |
  • Thanks, I guessed the table was being rebuilt. However, I found the writes count times 8K was greater than the data space + index space and it was still running? – u23432534 May 31 '16 at 20:50
  • @u23432534 Writes can also encompass other things such as log, worktable, workfile, etc. – Aaron Bertrand May 31 '16 at 20:57
  • @AaronBertrand Too bad I killed the session after found the writes exceeded my calculated number. However, it stll shows runnable status. I think I will drop the column and recreate it and it should finish instantly. – u23432534 May 31 '16 at 21:00
8

I tested the case. You can do it faster using below steps:

  1. Create the same table structure with a different name (call it Tbl2)
  2. Alter the column on Tbl2
  3. insert data from Tbl1 into Tbl2
  4. Drop Tbl1 (the old table)
  5. Rename Tbl2 (the new one) to Tbl1

This will give you much better performance.

The reason is, altering the column on table containing data, will take a lot of data transfer and data page alignment. Using my solution you just insert data w/o any page reorganization.

Edit:
In my test, I used table with about 40 M rows, Table Size 7 GB and Index size 2.5 GB. The suggested method, was 1 minute VS 4 minutes when I used renaming the field in original table

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.