April 2, 2009 at 4:08 pm
Hi ,
I have this table -- tblABCD and it has number of records 214324814 (as of now in my Dev bed)
I want to add new column to this table. I ran the following query
ALTER TABLE tblABCD ADD columnXYZ INT
NOT NULL Default 1
it's been 3 hours and still running.
Am I terrible mistaken something or is there better way of doing this. Production database has much more record than this. Definitely production database cannot take 3 hours to do this.
April 2, 2009 at 4:50 pm
Is it consuming resources or is it just waiting? If the later, then it is probably blocked and probably by you or some other DBA having the Table Design window open on it. (I've done this myself)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 2, 2009 at 5:24 pm
It says "Executing Query..."
It took 3 hours and 08 minutes to finish, Can this happen since it has to update big number of records...?
April 2, 2009 at 5:24 pm
It says "Executing Query..."
It took 3 hours and 08 minutes to finish, Can this happen since it has to update big number of records...?
April 2, 2009 at 5:31 pm
Use sysprocesses or sp_who or the Activity Monitor to look at the CPU & IO stats for the process and see if they are changing or not and if the process indicates that it is blocked.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 2, 2009 at 8:11 pm
There might be a lot of page splits happening as the table is rebuilt.
It's not like you are adding a varchar field with no data in it. This is an integer field, so every row has to grow by four bytes immediately. That's not a trivial task when you have 214+ million rows.
You might get better speed by
(1) creating a new table with the new column,
(2) loading it from the old table (in clustered index sequence)
(3) rebuilding your indexes and
(4) doing some renaming to put it into play instead of the existing column
It's worth a test run.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply