8 hours to add a bit column

I ran the following:

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?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|||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)

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|||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.|||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.|||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?|||hm, here's another though. Perhaps the Analyzer is waiting for the table to be freed from a lock. What does sysprocesses say?|||Just curious as to why you made it NOT NULL?

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?|||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.|||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!|||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|||WAIT...damn I just tested it...it's smart enough to add a _1 to the end...damn that's goodTricky little devils, ain't they ?

-PatP|||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?|||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.