オンライン インデックス操作のガイドライン

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceMicrosoft Fabric プレビューの SQL データベース

インデックス操作をオンラインで実行するときは、次のガイドラインに従ってください。

• 基になるテーブルに image、 ntext、 textなどの LOB (ラージ オブジェクト) データ型が含まれている場合、クラスター化インデックスの作成、再構築、または削除は、オフラインで行う必要があります。
• LOB データ型を使用する列がテーブルに存在するが、これらの列がキー列またはインクルード列としてインデックス定義で使用されていない場合は、非クラスター化インデックスをオンラインで作成できます。
• ローカル一時テーブルのインデックスの作成、再構築、または削除は、オンラインでは実行できません。 この制限は、グローバル一時テーブルのインデックスには当てはまりません。
• RESUMABLE または ALTER INDEX の句を使用して、再開可能な操作としてオンライン インデックス操作を開始できます。 再開可能なインデックス操作は、予期しないエラー、データベースのフェールオーバー、または ALTER INDEX PAUSE コマンドの後に再起動し、中断された場所から続行できます。

次の表に、オンラインで実行可能なインデックス操作、これらのオンライン操作対象から除外されるインデックス、再開可能なインデックスの制限を示します。 また、その他の制限についても記載します。

オンライン インデックス操作の進行中は、基になるテーブルを変更、切り捨て、または削除することはできません。

クラスター化インデックスの作成または削除時に指定したオンライン オプションの設定 (ONまたは OFF) は、再構築する必要のある非クラスター化インデックスに適用されます。 たとえば、クラスター化インデックスが CREATE INDEX WITH DROP_EXISTING, ONLINE=ONを使用してオンラインで構築されている場合、関連付けられているすべての非クラスター化インデックスもオンラインで再作成されます。

UNIQUE インデックスをオンラインで作成または再構築するときに、インデックス ビルダーと同時実行ユーザー トランザクションが、同じキーの挿入を試み、一意性が損なわれる場合があります。 ソース テーブルの元の行が新しいインデックス (ターゲット) に移動される前に、ユーザーが入力した行が新しいインデックスに挿入されると、オンラインのインデックス操作が失敗します。

ユーザーやアプリケーションの作業によっては、オンラインのインデックス操作とデータベースの更新が連携して実行される場合、まれに、オンラインのインデックス操作によりデッドロックが発生する場合があります。 このようなまれなケースでは、ユーザーまたはアプリケーションのアクティビティがデッドロックの対象として選択されます。

複数の新しい非クラスター化インデックスを作成しているとき、または非クラスター化インデックスを再構成しているときに限り、同じテーブルやビューに対してインデックス DDL 操作をオンラインで同時実行できます。 その他すべてのオンライン インデックス操作は、同時に実行しようとしても失敗します。 たとえば、既存のインデックスをオンラインで再構築している間に、同じテーブル上で新しいインデックスをオンラインで作成することはできません。

インデックスにラージ オブジェクト型の列が含まれており、オンライン インデックス操作が開始される前に同じトランザクションがデータ変更を行う場合は、オンライン操作を実行できません。 この問題を回避するには、オンライン インデックス操作をトランザクションの外部に移動するか、同じトランザクション内のデータ変更の前に移動します。

ディスク領域に関する注意点

オンライン インデックス操作では、オフライン インデックス操作よりも多くのディスク領域が必要です。

• インデックス作成操作やインデックス再構築操作の間、作成または再構築されるインデックスのために追加の領域が必要になります。 通常、この追加の領域は、インデックスによって占有されている現在の領域と同じですが、現在のインデックスまたは再構築されたインデックスで使用される圧縮によっては、大きいか小さくなる可能性があります。
• また、仮のマッピング インデックス操作にもディスク容量が必要になります。 この一時インデックスは、クラスター化インデックスを作成、再構築、または削除する、オンライン インデックス操作で使用されます。
• クラスター化インデックスをオンラインで削除する場合と、クラスター化インデックスをオンラインで作成または再構築する場合は同じ量の領域が必要になります。

詳細については、「インデックス DDL 操作に必要なディスク領域」を参照してください。

Performance considerations

オンライン インデックス操作では同時ユーザー更新アクティビティが許可されますが、更新アクティビティが多い場合は、インデックス操作に時間がかかる場合があります。 通常、オンラインのインデックス操作は、同時実行更新操作の負荷レベルに関係なく、同じインデックス操作をオフラインで行った場合よりも時間がかかります。

ソース構造とターゲット構造の両方がオンライン インデックス操作中に維持されるため、挿入、更新、および削除トランザクションのリソース使用量が増加し、2 倍になる可能性があります。 このため、インデックス操作中のパフォーマンスが低下し、リソースの使用量 (特に CPU 使用時間) が増大する可能性があります。 オンラインでのインデックス操作は、完全にログに記録されます。

オンラインでの操作を推奨しますが、実際の環境と特定の要件を評価してください。 オフラインでインデックス操作を実行することが最適な場合もあります。 これを行う場合、操作中にユーザーからのデータ アクセスは制限されますが、操作をより短時間で完了でき、使用するリソースも軽減できます。

SQL Server 2016 (13.x) 以降のバージョンを実行しているマルチプロセッサ コンピューターでは、インデックス操作で並列処理を使用して、インデックス ステートメントに関連付けられているスキャン操作と並べ替え操作を実行できます。 MAXDOP インデックス オプションを使用して、オンライン インデックス操作の並列処理の次数を制御できます。 このようにすることで、インデックス操作が使用するリソースと他の同時実行ユーザーが使用するリソースのバランスをとることができます。 詳細については、「 並列インデックス操作の構成」を参照してください。 並列インデックス操作をサポートする SQL Server のエディションの詳細については、「SQL Server 2022 の各エディションとサポートされている機能」を参照してください。

共有 (S) ロックまたはスキーマ変更 (Sch-M) ロックはインデックス操作の最終フェーズで保持されるため、明示的なユーザー トランザクション内でオンライン インデックス操作 ( BEGIN TRANSACTION ... COMMIT ブロックなど) を実行するときは注意が必要です。 これを行うと、トランザクションが終了するまでロックが保持され、他のワークロードがブロックされる可能性があります。

ALLOW_PAGE_LOCKS=OFFを使用してインデックス ページロックが無効になっている場合、オンライン インデックス再構築では、MAXDOPが 1 より大きい場合にインデックスの断片化が増加する可能性があります。 詳細については、「 動作方法: オンラインでのインデックス再構築 - 断片化が増加する可能性」を参照してください。

トランザクション ログに関する注意点

オフラインまたはオンラインで実行される大規模なインデックス操作では、大量のトランザクション ログが生成される可能性があります。 これは、オフラインとオンラインの両方のインデックス再構築操作が完全にログに記録されるためです。 インデックス操作をロールバックできるようにするため、インデックス操作が完了するまでは、トランザクション ログを切り捨てることはできません。ただし、インデックス操作中にログをバックアップすることはできます。

したがって、トランザクション ログには、インデックス操作中に、インデックス操作によるトランザクションと、同時実行ユーザーによるトランザクションの両方を格納できるだけの十分な領域が割り当てられている必要があります。 詳細については、「インデックス操作用のトランザクション ログのディスク領域」を参照してください。

高速 データベース復旧 (ADR) が有効になっている場合、オンライン インデックス操作ではトランザクション ログの増加は発生しません。

永続的なバージョン ストアに関する考慮事項

ADR が有効になっている場合、大規模なインデックスをオンラインで作成または再構築すると、インデックス操作の進行中に永続バージョン ストア (PVS) のサイズが大幅に増加する可能性があります。 データベースに PVS を拡張するための十分な空き領域があることを確認します。 詳細については、「高速データベース復旧 監視とトラブルシューティング」を参照してください。

再開可能なインデックスに関する考慮事項

RESUMABLEとCREATE INDEXのALTER INDEXインデックス オプションは、SQL Server (SQL Server 2017 (14.x) 以降のALTER INDEX)、および SQL Server 2019 (15.x) 以降のCREATE INDEX)、Azure SQL Database、および Azure SQL Managed Instance に適用されます。 詳細については、 CREATE INDEX と ALTER INDEX を参照してください。

RESUMABLE オプションを使用するには、ONLINE オプションも使用する必要があります。 再開可能なインデックスの作成または再構築を実行する場合は、次のガイドラインが適用されます。

• インデックスのメンテナンス期間の管理、計画、および拡張をより適切に制御できます。 インデックスの作成または再構築操作を何回でも一時停止して再開し、メンテナンス期間に合わせることができます。
• インデックスの作成または再構築の失敗 (データベースのフェールオーバーやディスク領域の不足など) から復旧できます。最初からインデックス操作を再開する必要はありません。
• インデックス操作を一時停止すると、元のインデックスと新しく作成されたインデックスの両方にディスク領域が必要であり、DML 操作中に更新する必要があります。
• `CONTAINMENT` オプションはサポートされていません。
• 無効なインデックスはサポート対象外です。

一般に、再開可能なオンライン インデックス操作と再開不可能なオンライン インデックス操作の間にパフォーマンスの違いはありません。 再開可能な CREATE INDEXでは、一定のオーバーヘッドが発生し、小さいテーブルの操作が著しく遅くなる可能性があります。

再開可能なインデックス操作が一時停止されている場合:

• ほとんどの読み取りワークロードでは、パフォーマンスの低下は重要ありません。
• 更新の負荷の高いワークロードでは、ワークロードの詳細に応じてスループットが低下する可能性があります。

一般に、再開可能なオンライン インデックスの作成または再構築と再開不可能なオンライン インデックスの作成または再構築の間に、最適化の品質の違いはありません。

オンラインの既定のオプション

ELEVATE_ONLINEまたはELEVATE_RESUMABLEデータベース スコープの構成を設定することで、オンラインおよび再開可能なインデックス操作をデータベース レベルの既定のオプションとして設定できます。 これらの既定のオプションを使用すると、実行中にテーブルまたはインデックスにアクセスできなくなるオフライン インデックス操作を誤って開始することを回避できます。 どちらのオプションでも、データベース エンジンは特定のインデックス操作をオンラインまたは再開可能な実行に自動的に昇格させます。

オプションは、 FAIL_UNSUPPORTED、 WHEN_SUPPORTED、または OFFとして設定できます。 ELEVATE_ONLINEとELEVATE_RESUMABLEに異なる値を設定できます。 詳細については、「ALTER DATABASE SCOPED CONFIGURATION」を参照してください。

ELEVATE_ONLINE と ELEVATE_RESUMABLE はいずれも、オンラインと再開可能の構文をそれぞれサポートする DDL ステートメントにのみ適用されます。 たとえば、 ELEVATE_ONLINE=FAIL_UNSUPPORTEDを使用して XML インデックスを作成しようとすると、XML インデックスは ONLINE オプションをサポートしていないため、操作はオフラインで実行されます。 オプションは、 ONLINE または RESUMABLE オプションを指定せずに送信される DDL ステートメントにのみ影響します。 たとえば、ONLINE=OFF または RESUMABLE=OFF でステートメントを送信することで、ユーザーは FAIL_UNSUPPORTED 設定をオーバーライドし、オフラインか再開不可能でステートメントを実行できます。

Related content

• オンライン インデックス操作のしくみ
• オンラインでのインデックス操作の実行
• ALTER INDEX (Transact-SQL)
• インデックス作成 (Transact-SQL)