Why does simple ALTER TABLE command take so long on table with full-text index?

Question

I have a large (~67 million rows) name-value table that has full-text indexing on the value column (DataValue).

My question is:

If I try to run the following sql command:

ALTER TABLE VisitorData ADD NumericValue bit DEFAULT 0 NOT NULL;

GO

It runs for 1 hour 10 minutes and still does not complete on a VisitorData table that contains ~67 million rows.

My questions are:

1. Why is this taking so long and not completing?
2. What can I do about it?

Here are more particulars about the table:

CREATE TABLE [dbo].[VisitorData](
            [VisitorID] [int] NOT NULL,
            [DataName] [varchar](80) NOT NULL,
            [DataValue] [nvarchar](3800) NOT NULL,
            [EncryptedDataValue] [varbinary](max) NULL,
            [VisitorDataID] [int] IDENTITY(1,1) NOT NULL, 
CONSTRAINT [PK_VisitorData_VisitorDataID] PRIMARY KEY CLUSTERED (
            [VisitorDataID] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY], 
CONSTRAINT [UNQ_VisitorData_VisitorId_DataName] UNIQUE NONCLUSTERED (
            [VisitorID] ASC,
            [DataName] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[VisitorData]
ADD  CONSTRAINT [UNQ_VisitorData_VisitorDataID] UNIQUE NONCLUSTERED (

[VisitorDataID] ASC
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
      IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
      ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[VisitorData]
    WITH CHECK ADD
        CONSTRAINT [FK_VisitorData_Visitors] FOREIGN KEY([VisitorID])
        REFERENCES [dbo].[Visitors] ([VisitorID])
GO

ALTER TABLE [dbo].[VisitorData]
    CHECK CONSTRAINT [FK_VisitorData_Visitors] GO

CREATE FULLTEXT CATALOG DBName_VisitorData_Catalog WITH ACCENT_SENSITIVITY = ON
CREATE FULLTEXT INDEX ON VisitorData ( DataValue Language 1033 )
    KEY INDEX UNQ_VisitorData_VisitorDataID
    ON DBName_VisitorData_Catalog
    WITH CHANGE_TRACKING AUTO
GO

The wait types that are occurring during the ALTER TABLE command are LCK_M_SCH_M (Schema modification), as per the query results below:

select * from  sys.dm_os_waiting_tasks

waiting_task_address    session_id exec_context_id wait_duration_ms     wait_type            resource_address       blocking_task_address   blocking_session_id blocking_exec_context_id resource_description
--------------------             ----------     --------------- --------------------              -------------------- ------------------             ---------------------            -------------------        ------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0x0000000000B885C8   54               0                   112695                            LCK_M_SCH_M   0x00000000802DF600 0x000000000054E478     25                            0                                         objectlock lockPartition=0 objid=834102012 subresource=FULL dbid=5 id=lock438a02e80 mode=IS associatedObjectId=834102012
0x0000000000B885C8   54               0                   112695                            LCK_M_SCH_M   0x00000000802DF600 0x00000000088AB048    23                            0                                         objectlock lockPartition=0 objid=834102012 subresource=FULL dbid=5 id=lock438a02e80 mode=IS associatedObjectId=834102012

I am adding my comments to feedback here because they're lengthy:

As per Jason's answer below, I issued the following update instead:

ALTER TABLE VisitorData ADD NumericValue bit NULL
GO 

This finally did execute as several folks have suggested in their answers, but took 29 minutes, 16 seconds!

I'm working with production servers that are running sql 2005 SP 2 (soon to be upgraded to 2008 SP2).

Martin: I did not see your comment until I did a workaround (removing the DEFAULT 0 NOT NULL part) to add that new bit column to the table. So, I couldn't run that 2nd query on the sys.dm_exec_requests table that you suggested.

With the new bit field in place in the table (as per above comment), I was able to quickly add the default value to it via the script:

ALTER TABLE VisitorData ADD
CONSTRAINT DF_VisitorData_NumericValue DEFAULT(0) FOR NumericValue;

GO

I am in the process of now setting all of the NumericValue bits in the table using a user-defined function (see below). It is in progress and taking about 1 minute per every 1 million rows in the ~68 million row table.

WITH RD_CTE (VisitorD, DataName) 
AS
(
    SELECT TOP 10000 VisitorD, DataName
    FROM VisitorData WITH (NOLOCK)
    WHERE NumericValue IS NULL  
)
UPDATE VisitorData
SET NumericValue = CASE WHEN dbo.ufn_IsReallyNumeric(rd.DataValue) = 1 THEN 1 ELSE 0 END
FROM VisitorData rd WITH (NOLOCK) 
INNER JOIN RD_CTE rdc WITH (NOLOCK) ON rd.VisitorD = rdc.VisitorD  AND rd.DataName = rdc.DataName

GO 6800

Once that's complete, I plan to run the final schema adjustment to make that new bit column non-null:

ALTER TABLE VisitorData ALTER COLUMN NumericValue bit NOT NULL;
GO

Hopefully, this last schema update will run quickly once all values are non-null and the NumericValue default is in place.

What then concerns me is how to actually deploy these changes to a live customer site. This is an extremely lengthy process and I cannot afford to take the customer DB offline for so long - with the worst offender being the initial 29+ minute update just to add that new nullable bit column to the table.

Any thoughts on how to actually deploy this to a live website (using sql 2005 sp2 or 2008 sp 2) would be appreciated.

Answer 1

The schema change it taking so long because you are assigning a default value to the column during the change and enforcing that with a non-nullable column, and it has to populate the column for 60+ million rows, which is an incredibly expensive operation. I'm not sure what your application requirements are but an approach that would make the schema change faster is to add it in as a nullable column with no default value and then perform an update in batches to assign 0 as the value for the column. After your update are done then you can apply another schema change to change the column to non-nullable and assign the default value.

Answer 2

Previous to SQL Server 2012 the ADD COLUMN NOT NULL DEFAULT ... is an offline operation as it has to run an update and populate each row with the new default value of the newly added column. In SQL Server 2012 the operation is much faster, see Online non-NULL with values column add in SQL Server 11 as it only updates the metadata of the table and does not actually update any row.

That being said, your ALTER TABLE could be slow because of the update (remember, being a single transaction implies a huge log will be generated and your log is likely growing now and being constantly zero-ed out as it expands). But it could also be slow because of ordinary contention: the statement cannot acquire the SCH-M lock on the table. Looking at sys.dm_exec_requests should show if this is the case, the wait_type and wait_resource columns would indicate if the ALTER statement is blocked or is making progress.