Почему добавление нулевого ограничения по умолчанию в существующий столбец занимает так много времени?

У меня есть существующая таблица с примерно 400 миллионами строк. Эта таблица содержит набор столбцов bit с именами IsModified, IsDeleted и 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]

Я хотел бы добавить ограничение по умолчанию для таблицы, которое для всех будущих вставленных строк по умолчанию будет иметь эти столбцы бит в 0. Я пытаюсь сделать это с помощью следующей команды:

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

В конце концов я хотел бы вернуться и очистить существующие данные, чтобы поместить нулевое значение везде, где есть значения NULL, но мне действительно не нужно это делать прямо сейчас.

Просто попытка запустить первую команду ADD CONSTRAINT выполняется уже более часа. Учитывая, что я не пытаюсь изменить какие-либо существующие значения, почему это так долго?

Ответ 1

Возможно, у вас есть другой процесс на вашем сервере, который блокирует эту таблицу.

Представьте, что у меня открыто два SSMS-окна, и в первом я выполняю следующие команды:

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

И затем оставьте окно SSMS открытым, чтобы транзакция никогда не закрывалась, попытка выполнить эту команду простого ограничения в другом сеансе SSMS будет вечно вешать:

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

Обратите внимание, что в этом примере размер или сложность таблицы не имеет значения; Я вынужден дождаться завершения транзакции. Моя команда alter в сеансе 2 не будет завершена до тех пор, пока я не отпущу блокировку на Foo либо COMMIT транзакции или закрывающей сессии 1.

Как вы можете определить, является ли это вашей проблемой? Посмотрите список "Процессы" на мониторе активности SSMS. Если ваша команда ALTER ожидает чего-то еще, то в столбце "Заблокировано" указывается номер сеанса команды, вызывающей вашу проблему.

Эта сессия, в свою очередь, может ждать друг друга и т.д. Если вы следуете этим ссылкам, вы в конечном итоге найдете процесс с 1 в столбце "Блокировка заголовка". Оттуда вы можете решить, должно ли соответствующее действие убить процесс оскорбления или просто дождаться его.

Ответ 2

воссоздайте объект со всеми ограничениями
сбрасывать данные
заблокировать исходный объект
изменить имена объектов

этот способ является самым быстрым, если вы хотите оптимизировать, переиндексировать и избегать конфликтов, подобных тем, о которых говорил Dan