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 DataValue column.

If I try to run the following command:

ALTER TABLE VisitorData ADD NumericValue bit DEFAULT 0 NOT NULL;

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

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'm working with production servers that are running SQL Server 2005 SP 2 (soon to be upgraded to 2008 SP2).

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

Full-text-indexing is, likely, irrelevant to your problem. Previous to SQL Server 2012 the ADD COLUMN NOT NULL DEFAULT ... is an offline operation that 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 rows.

Your ALTER TABLE is most likely slow because of the update. Remember, since that is a single transaction, a huge log will be generated and your log is likely growing now and being constantly zero-ed out as it expands. However, it might also be slow because of ordinary contention: the statement may not be able to 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.

Answer 3

_{Answer originally added to the question by its author:}

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

ALTER TABLE VisitorData ADD NumericValue bit NULL

This finally did execute, but took 29 minutes, 16 seconds. The operation itself should be pretty quick (metadata-only) so I imagine that almost all of that time was spent waiting to acquire the necessary LCK_M_SCH_M (schema modification) lock.

With the new bit field in place, 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;

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;

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