可恢复添加表约束操作

  • 项目

适用于： SQL Server 2022 (16.x) Azure SQL 数据库Azure SQL 托管实例

SQL Server 2019、Azure SQL 数据库和 Azure SQL 托管实例已支持在线索引创建和重新生成操作的可恢复操作。 可恢复操作允许在表联机 (ONLINE=ON) 时执行索引操作，还允许：

  • 多次暂停并重启索引创建或重新生成操作，以适应维护时段

  • 从索引创建或重新生成故障（如数据库故障转移或磁盘空间不足）恢复。

  • 在索引创建或重新生成操作期间启用事务日志的截断。

  • 索引操作暂停时，原始索引和新创建的索引都需要磁盘空间，并且都需要在数据操作语言 (DML) 操作期间更新。

SQL Server 2022、SQL 数据库和 SQL 托管实例的新扩展允许对数据定义语言 (DDL) 命令 ALTER TABLE ADD CONSTRAINT 执行可恢复操作，并允许添加主键或唯一键。 有关添加主键或唯一键的详细信息，请参阅 ALTER TABLE table_constraint。

注意

可恢复添加表约束仅适用于主键和唯一键约束。 FOREIGN KEY 约束不支持可恢复添加表约束。

可恢复操作

在以前版本的 SQL Server 中，可使用 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 TABLE ADD CONSTRAINT 的可恢复操作要求在线执行 ALTER 命令 (WITH ONLINE = ON)。

此功能对于大型表特别有用。

ALTER TABLE 的 T-SQL 语法

有关用于对表约束启用可恢复操作的语法的信息，请参阅 ALTER TABLE (Transact-SQL) 中的语法和选项。

ALTER TABLE 的备注

  • ALTER TABLE (Transact-SQL) 中的当前 T-SQL 语法中添加了一个新子句 WITH <resumable_options。

  • 选项 RESUMABLE 是新选项，已添加到现有的 ALTER TABLE (Transact-SQL) 语法中。

  • MAX_DURATION = time [MINUTES] 与 RESUMABLE = ON 一起使用（需要 ONLINE = ON）。 MAX_DURATION 指示可恢复联机添加约束操作在暂停之前执行的时间（以分钟为单位指定的整数值）。 如果未指定，则操作会一直持续到完成。

ALTER INDEX 的 T-SQL 语法

若要暂停、恢复或中止 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

用于添加聚集在 (a) 列上的主键的可恢复 ALTER TABLE 操作，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

用于在两列（a 和 b）上添加唯一约束的可恢复 ALTER TABLE 操作，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 操作。

下表显示了使用以下 T-SQL 语句按时间顺序执行的两个会话（Session #1 和 Session #2）。 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 (...)RUNNING43.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 (...)RUNNING90.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 PRIMARY KEY

另请参阅