Why does adding a nullable default constraint to an existing column take so long?

Question

I have an existing table with approximately 400 million rows. That table includes a set of bit columns named IsModified, IsDeleted, and IsExpired.

CREATE TABLE [dbo].[ActivityAccumulator](
    [ActivityAccumulator_SK] [int] IDENTITY(1,1) NOT NULL,
    [ActivityAccumulatorPK1] [int] NULL,
    [UserPK1] [int] NULL,
    [Data] [varchar](510) NULL,
    [CoursePK1] [int] NULL,
    [TimeStamp] [datetime] NULL,
    [SessionID] [int] NULL,
    [Status] [varchar](50) NULL,
    [EventType] [varchar](40) NULL,
    [DWCreated] [datetime] NULL,
    [DWModified] [datetime] NULL,
    [IsModified] [bit] NULL,
    [DWDeleted] [datetime] NULL,
    [IsDeleted] [bit] NULL,
    [ActivityAccumulatorKey] [bigint] NULL,
    [ContentPK1] [bigint] NULL
) ON [PRIMARY]

I would like to add a default constraint to the table that, for all future inserted rows, will default those bit columns to 0. I'm trying to do this via the following command:

ALTER TABLE ActivityAccumulator 
ADD CONSTRAINT DF_ActivityAccumulatorIsExpired DEFAULT (0) FOR IsExpired

ALTER TABLE ActivityAccumulator 
ADD CONSTRAINT DF_ActivityAccumulatorIsDeleted DEFAULT (0) FOR IsDeleted

ALTER TABLE ActivityAccumulator 
ADD CONSTRAINT DF_ActivityAccumulatorIsModified DEFAULT (0) FOR IsModified

I'd eventually like to go back and clean up the existing data to put the zero value in wherever there are NULL values, but I don't really need to do so right now.

Just trying to run the first ADD CONSTRAINT command has been executing for over an hour now. Given that I'm not trying to change any existing values, why is this taking so long?

Answer 1

One possibility may be that you have another process on your server that's locking this table.

Imagine I have two SSMS windows open, and in the first one I execute these commands:

-- Session 1
CREATE TABLE Foo(IsTrue BIT) 
INSERT INTO Foo VALUES (1),(1),(0)
BEGIN TRANSACTION
UPDATE Foo SET IsTrue = 1 - IsTrue

And then leave the SSMS window open so that the transaction never closes, trying to execute this simple constraint command in the other SSMS session will hang forever:

-- Session 2
ALTER TABLE Foo ADD CONSTRAINT FooDefault DEFAULT(0) FOR IsTrue

Note that in this example, the size or complexity of the table is irrelevant; I'm forced to wait for the transaction to complete. My alter instruction in session 2 won't complete until I release the lock on Foo either by COMMITing the transaction or closing session 1.

How can you tell if this is your problem? Have a look at the "Processes" list in the SSMS activity monitor. If your ALTER instruction is waiting for something else to complete, there'll be a number in the "Blocked By" column indicating the Session ID of the command that's causing your problem.

That session may in turn be waiting on another and so forth. If you follow these references, you eventually find a process with a 1 in the "Head Blocker" column. From there you can decide whether the appropriate action is to kill the offending process, or just wait it out.