Возобновляемые ограничения на добавление таблиц

  • Статья

Область применения: SQL Server 2022 (16.x) База данных SQL Azure Управляемый экземпляр SQL Azure

Возобновляемая операция создания и перестроения в Сети уже поддерживается для SQL Server 2019, База данных SQL Azure и Управляемый экземпляр SQL Azure. Возобновляемые операции позволяют выполнять операции с индексами, пока таблица подключена (ONLINE=ON), а также:

  • Операцию создания и перестроения индексов в периоды обслуживания можно многократно приостанавливать и запускать повторно.

  • Восстановление после сбоев при создании и перестроении индекса (например, при переходе на другую базу данных или нехватке места на диске).

  • Появляется возможность усекать журналы транзакций во время операции создания или перестроения индекса.

  • При приостановке операции с индексами исходный и вновь созданный индекс требуют места на диске и обновления во время операций языка обработки данных DML.

Новые расширения для SQL Server 2022, База данных SQL и Управляемый экземпляр SQL разрешают повторную операцию для команды ALTER TABLE ADD CONSTRAINT и добавления первичного или уникального ключа. Дополнительные сведения о добавлении первичного или уникального ключа см. в ALTER TABLE table_constraint.

Примечание.

Повторно добавляемые ограничения таблицы применяются только к ограничениям PRIMARY KEY и UNIQUE KEY. Повторное добавление ограничений таблицы не поддерживается для ограничений FOREIGN KEY.

Возобновляемые операции

В предыдущих версиях SQL Server операция ALTER TABLE ADD CONSTRAINT может выполняться с параметром ONLINE=ON. Однако выполнение операции для большой таблицы может занять несколько часов и потребляет большой объем ресурсов. Существует также вероятность сбоев или прерываний во время такого выполнения. Мы представили пользователям возобновляемые возможности в ALTER TABLE ADD CONSTRAINT, которые позволяют приостановить операцию во время периода обслуживания или перезапустить ее, если она была прервана на время сбоя выполнения, не перезапуская ее с самого начала.

Поддерживаемые сценарии

Новая возобновляемая возможность для ALTER TABLE ADD CONSTRAINT поддерживает следующие сценарии клиента.

  • Приостановка или возобновление выполнения операции ALTER TABLE ADD CONSTRAINT, например приостановка на период обслуживания и возобновление после завершения периода обслуживания.

  • Возобновляйте операцию ALTER TABLE ADD CONSTRAINT после отработки отказа и сбоев системы.

  • Выполняйте операцию ALTER TABLE ADD CONSTRAINT в большой таблице, несмотря на небольшой доступный размер журнала.

Примечание.

Возобновляемая операция для ALTER TABLE ADD CONSTRAINT требует выполнения команды ALTER в сети (WITH ONLINE = ON).

Эта функция особенно удобна для больших таблиц.

Синтаксис T-SQL для ALTER TABLE

Сведения о синтаксисе, используемом для включения возобновляемых операций с ограничением таблицы, см. в описании синтаксиса и параметров в разделе ALTER TABLE (Transact-SQL).

Примечания для ALTER TABLE

  • В текущий синтаксис T-SQL в ALTER TABLE (Transact-SQL)< добавлено новое предложение WITH resumable_options.

  • Параметр RESUMABLE является новым и добавлен в существующий синтаксис ALTER TABLE (Transact-SQL).

  • MAX_DURATION = время [МИНУТЫ] используется с RESUMABLE = ON (требуется ONLINE = ON). MAX_DURATION указывает время (целочисленное значение минутах), в течение которого выполняется возобновляемая операция добавления ограничений в сети до приостановки. Если этот параметр не указан, операция продолжается вплоть до завершения.

Синтаксис T-SQL для ALTER INDEX

Чтобы приостановить, возобновить или прервать возобновляемую операцию ограничения таблицы для ALTER TABLE ADD CONSTRAINT, используйте синтаксис T-SQL ALTER INDEX (Transact-SQL).

Для возобновляемых ограничений используется существующая команда ALTER INDEX ALL.

ALTER INDEX ALL ON <table_name>  
      { RESUME [WITH (<resumable_index_options>,[...n])]
        | PAUSE
        | ABORT
      }
<resumable_index_option> ::=
 { 
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION =<time> [MINUTES]
    | <low_priority_lock_wait>  
 }
 <low_priority_lock_wait>::=  
{  
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,   
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )  
}

Примечания для ALTER INDEX

ALTER INDEX ALL ON <Table> PAUSE

  • Приостановка возобновляемой и подключенной операции добавления ограничения таблицы, которая выполняется в данный момент

ALTER INDEX ALL ON <Table> RESUME [WITH (<resumable_index_options>,[...n])]

  • Возобновить операцию добавления ограничений таблицы, приостановленную вручную или из-за сбоя.

MAX_DURATION используется с RESUMABLE=ON

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

WAIT_AT_LOW_PRIORITY используется с RESUMABLE=ON и ONLINE = ON

  • При возобновлении подключенной операции добавления ограничений таблицы после приостановки необходимо дожидаться операций блокировки в этой таблице. WAIT_AT_LOW_PRIORITY указывает, что операция добавления ограничения таблицы будет ожидать блокировки с низким приоритетом, позволяя выполняться другим операциям, пока возобновляемая операция находится в состоянии ожидания. Пропуск параметра WAIT_AT_LOW_PRIORITY эквивалентен WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE). Дополнительные сведения см. в разделе WAIT_AT_LOW_PRIORITY.

ALTER INDEX ALL ON <Table> ABORT

  • Прервать выполняющуюся или приостановленную операцию добавления ограничения таблицы, объявленную как возобновляемая. Операция прерывания должна явным образом выполняться как команда ABORT, чтобы завершить возобновляемую операцию ограничения. Сбой или приостановка возобновляемой операции ограничения таблицы не завершает ее выполнение. Вместо этого операция остается в неопределенном состоянии приостановки.

Дополнительные сведения о параметрах PAUSE, RESUME и ABORT, доступных для возобновляемых операций, см. в разделе ALTER INDEX (Transact-SQL).

Просмотр статуса возобновления операции

Чтобы просмотреть статус возобновляемой операции ограничения таблицы, используйте представление sys.index_resumable_operations.

Разрешения

Необходимо разрешение ALTER для таблицы.

Новые разрешения для возобновляемой ALTER TABLE ADD CONSTRAINT не требуются.

Примеры

Далее приведены примеры использования возобновляемых операций добавления ограничений таблицы.

Пример 1

Возобновляемая операция ALTER TABLE для добавления первичного ключа, кластеризованного по столбцу (a) со значением параметра MAX_DURATION, равным 240 минутам.

ALTER TABLE table1
ADD CONSTRAINT PK_Constrain PRIMARY KEY CLUSTERED (a)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);

Пример 2

Возобновляемая операция ALTER TABLE для добавления уникального ограничения в два столбца (а и b) со значением параметра MAX_DURATION, равным 240 минутам.

ALTER TABLE table2
ADD CONSTRAINT PK_Constrain UNIQUE CLUSTERED (a,b)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 240);

Пример 3

Операция ALTER TABLE для добавления кластеризованного первичного ключа, которая была приостановлена и возобновлена.

В приведенной ниже таблице показаны два сеанса (Session #1 и Session #2), которые выполняются в хронологическом порядке с помощью следующих инструкций T-SQL. Session #1 выполняет возобновляемую операцию ALTER TABLE ADD CONSTRAINT для создания первичного ключа в столбце Col1. Session #2 проверяет статус выполнения для выполняемого ограничения. Через некоторое время он приостанавливает возобновляемую операцию. Session #2 проверяет статус приостановленного ограничения. И наконец, Session #1 возобновляет приостановленное ограничение, и Session #2 снова проверяет статус.

Сеанс 1 Сеанс 2
Выполнение повторного ограничения добавления

ALTER TABLE TestConstraint
ADD CONSTRAINT PK_TestConstraint PRIMARY KEY (Col1)
WITH (ONLINE = ON, MAXDOP = 2, RESUMABLE = ON, MAX_DURATION = 30);
Проверка состояния ограничения

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Выходные данные, показывающие операцию

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)ВЫПОЛНЯЕТСЯ43.552
Приостановка повторного ограничения

ALTER INDEX ALL ON TestConstraint PAUSE;
Ошибка

Msg 1219, Level 16, State 1, Line 6
Your session has been disconnected because of a high priority DDL operation.

Msg 1750, Level 16, State 1, Line 6
Could not create constraint or index. See previous errors.

Msg 0, Level 20, State 0, Line 5
A severe error occurred on the current command.
The results, if any, should be discarded.
Проверка состояния ограничения

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Выходные данные, показывающие операцию

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)ПРИОСТАНОВЛЕНО65.339
ALTER INDEX ALL ON TestConstraint RESUME;
Проверка состояния ограничения

SELECT sql_text, state_desc, percent_complete
FROM sys.index_resumable_operations;
Выходные данные, показывающие операцию

sql_textstate_descpercent_complete
ALTER TABLE TestConstraint (...)ВЫПОЛНЯЕТСЯ90.238

После завершения операции выполните следующую инструкцию T-SQL, чтобы проверить ограничение:

SELECT constraint_name, table_name, constraint_type 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='PRIMARY KEY';
GO

Вот результирующий набор:

constraint_name table_name constraint_type
PK_Constraint TestConstraint ПЕРВИЧНЫЙ КЛЮЧ

См. также