Dlaczego proste polecenie ALTER TABLE zajmuje tak dużo czasu dla tabeli z indeksem pełnotekstowym?

Mam dużą tabelę nazwa-wartość (~ 67 milionów wierszy) z indeksowaniem pełnotekstowym w DataValuekolumnie.

Jeśli spróbuję uruchomić następujące polecenie:

ALTER TABLE VisitorData ADD NumericValue bit DEFAULT 0 NOT NULL;

Działa przez 1 godzinę 10 minut i nadal nie kończy się na VisitorDatastole zawierającym ~ 67 milionów wierszy.

1. Dlaczego to trwa tak długo i nie kończy się?
2. Co mogę z tym zrobić?

Oto więcej szczegółów na temat tabeli:

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

Typy oczekiwania występujące podczas wykonywania ALTER TABLEpolecenia to LCK_M_SCH_M(modyfikacja schematu), zgodnie z wynikami zapytania poniżej:

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

Pracuję z serwerami produkcyjnymi, na których działa SQL Server 2005 SP 2 (wkrótce zostanie uaktualniony do 2008 SP2).

Schemat zmienia go tak długo, ponieważ podczas zmiany przypisujesz wartość domyślną do kolumny i wymuszasz ją kolumną, która nie ma wartości zerowej, i musi wypełnić kolumnę dla ponad 60 milionów wierszy, co jest niezwykle kosztowną operacją. Nie jestem pewien, jakie są wymagania aplikacji, ale podejście, które przyspieszyłoby zmianę schematu, polega na dodaniu go jako kolumny zerowalnej bez wartości domyślnej, a następnie przeprowadzeniu aktualizacji partiami, aby przypisać 0 jako wartość dla kolumny. Po zakończeniu aktualizacji możesz zastosować kolejną zmianę schematu, aby zmienić kolumnę na inną niż null i przypisać wartość domyślną.

Indeksowanie pełnotekstowe jest prawdopodobnie nieistotne dla twojego problemu. Wcześniejsze niż SQL Server 2012 ADD COLUMN NOT NULL DEFAULT ...to operacja offline, która musi uruchomić aktualizację i wypełnić każdy wiersz nową wartością domyślną nowo dodanej kolumny. W SQL Server 2012+ operacja jest znacznie szybsza, zobacz Online nie-NULL z kolumną wartości dodawaną w SQL Server 11, ponieważ aktualizuje tylko metadane tabeli i nie aktualizuje żadnych wierszy.

Twoje ALTER TABLEnajprawdopodobniej jest wolne z powodu aktualizacji. Pamiętaj, ponieważ ponieważ jest to pojedyncza transakcja, zostanie wygenerowany ogromny dziennik, który prawdopodobnie rośnie teraz i jest stale zerowany w miarę rozszerzania. Jednak może być również powolny z powodu zwykłej rywalizacji: instrukcja może nie być w stanie uzyskać blokady SCH-M na stole. Patrząc na sys.dm_exec_requestspowinien pokazać, czy tak jest, kolumny wait_typei wait_resourcewskazują, czy ALTERinstrukcja jest zablokowana lub robi postępy.

_{Odpowiedź pierwotnie dodana do pytania przez autora:}

Zgodnie z odpowiedzią Jasona zamiast tego wydałem następującą aktualizację:

ALTER TABLE VisitorData ADD NumericValue bit NULL

To w końcu się wykonało, ale zajęło 29 minut i 16 sekund. Sama operacja powinna być dość szybka (tylko metadane), więc wyobrażam sobie, że prawie cały ten czas spędziłem na uzyskaniu niezbędnej LCK_M_SCH_Mblokady (modyfikacji schematu).

Po wprowadzeniu nowego bitpola byłem w stanie szybko dodać do niego wartość domyślną za pomocą skryptu:

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

Jestem teraz w trakcie ustawiania wszystkich NumericValuebitów w tabeli za pomocą funkcji zdefiniowanej przez użytkownika (patrz poniżej). Trwa i trwa około 1 minuty na każdy milion wierszy w tabeli ~ 68 milionów wierszy.

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

Kiedy to się skończy, planuję uruchomić ostateczną korektę schematu, aby nowa kolumna bitów miała wartość inną niż null:

ALTER TABLE VisitorData ALTER COLUMN NumericValue bit NOT NULL;

Mamy nadzieję, że ta ostatnia aktualizacja schematu uruchomi się szybko, gdy wszystkie wartości będą inne niż null, a NumericValuedomyślna wartość zostanie wprowadzona.