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

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

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

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

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

• Включить усечение журналов транзакций при создании или перестроении индекса.

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

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

Примечание.

Повторно добавляемые ограничения таблицы применяются только к ограничениям 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

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

• Параметр 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 INDEXALL.

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_text state_desc percent_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_text state_desc percent_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_text state_desc percent_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	тип ограничения
PK_Constraint	TestConstraint	ПЕРВИЧНЫЙ КЛЮЧ

Связанные материалы

• Рекомендации по операциям с индексами в сети
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)
• sys.index_resumable_operations (Transact-SQL)
• WAIT_AT_LOW_PRIORITY
• ALTER TABLE (Transact-SQL)
• ALTER TABLE index_option (Transact-SQL)