Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2003
    Posts
    268

    Unanswered: 8 hours to add a bit column

    I ran the following:
    Code:
    ALTER TABLE Recipients ADD Obscene BIT NOT NULL DEFAULT 0
    On a table with 80 million records. It's been running for 8 hours and counting now. This is ridiculous. No one else is using this server.

    Server configuration:
    SQL Server 2000 Enterprise Edition with SP3a
    2 GB RAM
    3.0 GHz P4 with hyperthreading
    SCSI RAID

    Any ideas why this is taking so long? Can I find out what it's doing? Is there anything I can do to make it go faster?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You won't like any of my answers.

    Before we descend into that morass though, crank up the NT Performance Monitor and have a look-see at the server. Is it CPU bound, disk bound, memory bound, or are all of those counters at reasonable levels? How many SQL threads (spids) are active? What do the SQL page counters look like?

    My guess would be that the box is hideously RAM bound, that it is having page splits up the ying-yang, and that it might be disk bound as well.

    Is your log file on a different disk device than your data files? Is that device mirrored instead of RAID? Do you have the ability to add RAM with the box running (some servers can do that!)?

    -PatP

  3. #3
    Join Date
    Oct 2003
    Posts
    268
    If your answers give me any ideas and any closer to figuring these kinds of problems out, I like them. The business that I'm working for is very data driven and we are moving quickly in a database direction and we need this knowledge.

    I've been watching CPU and disc levels in perfmon all day. The system has been steadily disc bound.
    sp_who 'active' returns 17 rows.
    How do I look at the page counters?

    A RAM bound system exhibits itself as disc bound, correct? With lots of paging to/from disc.

    The data files and temp db are on a SCSI RAID 5. The main log file is on a separate 250 GB IDE disc (it has grown to 150 GB in the past which wouldn't fit on the RAID)

    Quote Originally Posted by Pat Phelan
    You won't like any of my answers.

    Before we descend into that morass though, crank up the NT Performance Monitor and have a look-see at the server. Is it CPU bound, disk bound, memory bound, or are all of those counters at reasonable levels? How many SQL threads (spids) are active? What do the SQL page counters look like?

    My guess would be that the box is hideously RAM bound, that it is having page splits up the ying-yang, and that it might be disk bound as well.

    Is your log file on a different disk device than your data files? Is that device mirrored instead of RAID? Do you have the ability to add RAM with the box running (some servers can do that!)?

    -PatP

  4. #4
    Join Date
    Jul 2004
    Posts
    60
    if you trace an alter table statement it will show you the issue i think you are hitting.

    alter table creates a new table in temp space with the new column, inserts all the data from the old into the new temp, then swaps the names around.

    with 80million rows, i'd be willing to bet your are being I/O thottled either creating the temp (which using a low logged select..into..) or the insert, (which is using a fully logged insert into..select).

    either way, 8 hours sux but doesnt really suprise me.

  5. #5
    Join Date
    Feb 2004
    Posts
    492
    I'v never had to wait 8 hours to add a column to a big table. Besides, I thought that the EM did the swap-trick and the alter table did not.

  6. #6
    Join Date
    Jul 2004
    Posts
    60
    i'd agree...8 hours is way out there.
    you are correct...EM does the old swap'r'roo trick. that was an assumption on my part - uber apologies - that you were using the Enterprise Mangler.

    i would suspect the same thing is happening underneath an alter, though.
    (i'm moving into the 'out of my ass' realm so i'm going to qualify that statement as an 'idea'- not something i claim to know.)

    the page counters are under the Memory object with some useful other SQL specifc page counters SQLServer:BufferManager.

    i wonder if your disk queues are backing up for reads or writes?

    perhaps that could help understand if a large read/write is actually occuring underneath your alter???

  7. #7
    Join Date
    Feb 2004
    Posts
    492
    hm, here's another though. Perhaps the Analyzer is waiting for the table to be freed from a lock. What does sysprocesses say?

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just curious as to why you made it NOT NULL?

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99 (Col1 int)
    GO
    INSERT INTO myTable99(Col1) SELECT 1
    GO
    
    SELECT * FROM myTable99
    
    ALTER TABLE myTable99 ADD Obscene BIT NOT NULL DEFAULT 0
    GO
    
    SELECT * FROM myTable99
    GO
    
    ALTER TABLE myTable99 ADD Obscene2 BIT DEFAULT 0
    GO
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO

    If you needed it null maybe you caould have performed batch updates after the fact, the changed ALTERed the column to make it NOT NULL...

    Is it still running?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    OK, here comes Robert with his BCP again...But it's true, no matter how you look at it! Non-logged data load would beat "in-line" DDL+DML (because this is exactly what happens when you add a new NON-NULLable column with default - the only way to add a new non-nullable column) If you added the same column but made it nullable, - you'd be onto something else 7 hours and 59 minutes ago. At this point though you can't even interrupt this operation because ALTER TABLE is a fully logged operation. It means that every 0 that came from your DEFAULT is logged in your transaction log (thus its size is very explainable). If you decide to kill the process you'll be looking at 8+ hours of rollback. If you stop the service you'll be looking at "Recovering database x..." for probably as much.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by Brett Kaiser
    Just curious as to why you made it NOT NULL?
    That's an application issue, right? We are trying to mark certain records as "obscene", so every record should be obscene or not. There should be no NULL. I didn't realize that this would be a large performance issue. If I knew in advance I could have dealt with NULL values in one way or another.

    It finished overnight but as of 12:30 AM last night, it was running for 13 hours and still going.

    I've previously added datetime columns to the same table with EM (this was added via QA with a ALTER TABLE statement) and it took less than an hour. I was really surprised that this took so long. Ideally, I know what to look for to remedy such an issue and how to prevent such things from happening.

    thanks guys!

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Did you look at the code that em scripted for you?

    Just make sure you don't have a table called tmp_yourtable..

    WAIT...damn I just tested it...it's smart enough to add a _1 to the end...damn that's good
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Brett Kaiser
    WAIT...damn I just tested it...it's smart enough to add a _1 to the end...damn that's good
    Tricky little devils, ain't they ???

    -PatP

  13. #13
    Join Date
    Jul 2004
    Posts
    60
    curious - when you added the datetime values last time did you set a default of getdate()? or something else? or allow them to be null?

  14. #14
    Join Date
    Oct 2003
    Posts
    268
    Quote Originally Posted by oddity
    curious - when you added the datetime values last time did you set a default of getdate()? or something else? or allow them to be null?
    They defaulted to NULL. And it was added through EM as opposed to an ALTER TABLE statement.

    The exact time on adding the bit column was 17 hours and 23 minutes.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •