再開可能なテーブル制約の追加

適用対象: SQL Server 2022 (16.x) 以降 Azure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric 内の SQL データベース

オンライン インデックス作成と再構築ための再開可能な操作は、SQL Server 2019、Azure SQL Database、Azure SQL Managed Instance で既にサポートされています。 再開可能な操作を使用すると、テーブルがオンライン (ONLINE=ON) である間にインデックス操作を実行できます。また、次の操作も実行できます。

• インデックスの作成または再構築操作を複数回一時停止して再開し、メンテナンス期間に合わせます

• インデックスの作成または再構築の障害 (データベースのフェールオーバーやディスク領域の不足など) から回復します。

• インデックスの作成または再構築操作の間はトランザクション ログの切り捨てを有効にします。

• インデックス操作を一時停止すると、元のインデックスと新しく作成されたインデックスの両方にディスク領域が必要であり、データ操作言語 (DML) 操作中に更新する必要があります。

SQL Server 2022、SQL Database、およびSQL Managed Instanceの新しい拡張機能を使用すると、ALTER TABLEデータ定義言語 (DDL) コマンドに対して再開可能な操作を実行し、主キーまたは一意キーを追加できます。 主キーまたは一意キーの追加の詳細については、「 ALTER TABLE table_constraint」を参照してください。

再開可能な操作

以前のバージョンの 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 の T-SQL 構文

テーブル制約に対して再開可能な操作を有効にするために使用される構文については、 ALTER TABLE (Transact-SQL) の構文とオプションを参照してください。

ALTER TABLE の備考

• 新しい句 WITH <resumable_options が、ALTER TABLE (Transact-SQL) の現在の T-SQL 構文に追加されました。

• RESUMABLE オプションは新規であり、既存のALTER TABLE (Transact-SQL) 構文に追加されました。

• MAX_DURATION = time [MINUTES] は 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

MAX_DURATION が 240 分の列 (a) にクラスター化された主キーを追加するための再開可能な ALTER TABLE 操作。

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

例 2

MAX_DURATION が 240 分の 2 つの列 (a と b) に一意の制約を追加するための再開可能な ALTER TABLE 操作。

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 ステートメントを使用して時系列的に実行される 2 つのセッション (Session #1 と Session #2) を示しています。 Session #1 では、列 ALTER TABLE ADD CONSTRAINT に主キーを作成する再開可能な Col1 操作を実行します。 Session #2 では、実行中の制約の実行状態を確認します。 しばらくすると、再利用可能な操作が一時停止されます。 Session #2 では、一時停止中の制約の状態を確認します。 最後に、Session #1 では一時停止中の制約を再開し、Session #2 では状態をもう一度確認します。

操作が完了したら、次の T-SQL ステートメントを実行して制約を確認します。

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

結果セットは次のとおりです。

関連項目

• オンライン インデックス操作のガイドライン
• CREATE INDEX (Transact-SQL)
• ALTER INDEX (Transact-SQL)
• sys.index_resumable_operations
• WAIT_AT_LOW_PRIORITY
• ALTER TABLE (Transact-SQL)
• ALTER TABLE index_option