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

  • [アーティクル]

適用対象: SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed Instance

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

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

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

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

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

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

Note

再開可能なテーブルの追加制約は、PRIMARY KEY 制約と UNIQUE KEY 制約にのみ適用されます。 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 操作を実行します。

Note

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

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 では、列 Col1 に主キーを作成する再開可能な ALTER TABLE ADD CONSTRAINT 操作を実行します。 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 (...)PAUSED65.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

関連項目