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?

Dan · Accepted Answer · 2014-09-05T16:39:43.533

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.

SSMS Process list showing blocked process

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.

Yep, for issues like this, my first thought is also "deadlock". — Ellesedil, Sep 05 '14 at 16:07
@Dan, +25 doesn't seem like enough ... thank you! That exactly describes the issue. A previously canceled query is still running with a KILLED/ROLLBACK state. I was so focused on the size of the table in question that I never even considered checking for blocking processes. — AHiggins, Sep 05 '14 at 16:33
The size isn't wholly irrelevant: If it were a tiny table, that ROLLBACK wouldn't be taking so long. — Dan, Sep 05 '14 at 16:38
True. I guess the best thing now is to just wait and see how long it runs ... either way, though, I now know the answer to my original question. Thank you! — AHiggins, Sep 05 '14 at 16:43

score 0 · Answer 2 · answered Sep 05 '14 at 16:13

0

recreate the object with all the constrains
dump the data
lock the original object
switch the object names

this way is the fastest if you want to optimize, re-index and avoid conflicts like the one mentioned by Dan

answered Sep 05 '14 at 16:13

ninjabber

371
2
7

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

2 Answers2