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

  • [アーティクル]

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

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

  • 基になるテーブルに image、 ntext、 textなどの LOB (ラージ オブジェクト) データ型が含まれている場合、クラスター化インデックスの作成、再構築、または削除は、オフラインで行う必要があります。
  • テーブルに LOB データ型が含まれていても、そのデータ型の列がキー列または非キー (付加) 列としてインデックス定義で使用されていない場合は、一意ではない非クラスター化インデックスをオンラインで作成できます。
  • ローカル一時テーブルのインデックスの作成、再構築、または削除は、オンラインでは実行できません。 この制限は、グローバル一時テーブルのインデックスには当てはまりません。
  • インデックスは、予期しないエラー、データベースのフェールオーバー、または PAUSE コマンドの後で、停止したところから再開できます。 「CREATE INDEX」および「ALTER INDEX」を参照してください。

Note

オンラインでのインデックス操作は、Microsoft SQL Server のすべてのエディションで使用できるわけではありません。 SQL Server の各エディションでサポートされる機能の一覧については、「SQL Server 2022 の各エディションとサポートされている機能」を参照してください。

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

オンラインのインデックス操作 操作対象外のインデックス その他の制限事項
ALTER INDEX REBUILD 無効化されたクラスター化インデックスまたは無効化されたインデックス付きビュー

XML インデックス

列ストア インデックス

ローカル一時テーブルのインデックス		 テーブルに操作対象外のインデックスが含まれている場合、キーワード ALL を指定すると操作が失敗する可能性があります。

無効化されたインデックスの再構築には、他にも制限があります。 詳細については、「 インデックスと制約の無効化」を参照してください。
CREATE INDEX XML インデックス

ビューの最初の一意クラスター化インデックス

ローカル一時テーブルのインデックス
CREATE INDEX WITH DROP_EXISTING 無効化されたクラスター化インデックスまたは無効化されたインデックス付きビュー

ローカル一時テーブルのインデックス

XML インデックス
DROP INDEX 無効化されたインデックス

XML インデックス

非クラスター化インデックス

ローカル一時テーブルのインデックス		 1 つのステートメント内に複数のインデックスは指定できません。
ALTER TABLE ADD CONSTRAINT (PRIMARY KEY または UNIQUE) ローカル一時テーブルのインデックス

クラスター化インデックス		 サブ句は、一度に 1 つしか使用できません。 たとえば、同じ PRIMARY KEY ステートメント内で UNIQUE 制約または ALTER TABLE 制約を追加および削除することはできません。
ALTER TABLE DROP CONSTRAINT (PRIMARY KEY または UNIQUE) クラスター化インデックス

オンラインのインデックス操作の実行中は、基になるテーブルに変更、切り詰め、削除を行えません。

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

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

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

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

インデックスにラージ オブジェクト型の列が含まれていて、同じトランザクション内でオンライン操作の前に更新操作がある場合は、このオンライン操作を実行できません。 この問題を回避するには、オンライン操作をトランザクションの外部に配置するか、トランザクション内で更新操作の前に配置してください。

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

オンライン インデックス操作には、オフライン インデックス操作より多くのディスク容量が必要になります。

  • インデックス作成操作やインデックス再構築操作の間、作成または再構築されるインデックスのために追加の領域が必要になります。
  • また、仮のマッピング インデックス操作にもディスク容量が必要になります。 この一時インデックスは、クラスター化インデックスを作成、再構築、または削除する、オンライン インデックス操作で使用されます。
  • クラスター化インデックスをオンラインで削除する場合と、クラスター化インデックスをオンラインで作成または再構築する場合は同じ量の領域が必要になります。

詳細については、「 Disk Space Requirements for Index DDL Operations」をご参照ください。

パフォーマンスに関する考慮事項

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

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

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

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

S-Lock または Sch-M ロックはインデックス操作の最後のフェーズで保持されるので、BEGIN TRANSACTION ... COMMIT ブロックなど、明示的なユーザー トランザクション内でのオンラインのインデックス操作を実行する場合は十分に注意してください。 この場合、ロックがトランザクションの最後まで保持され、その結果ユーザーのコンカレンシーが損なわれます。

MAXDOP より大きい 1、および ALLOW_PAGE_LOCKS=OFF で実行する場合は、オンラインでのインデックス再構築により断片化が進む可能性があります。 詳細については、「 動作方法: オンラインでのインデックス再構築 - 断片化が増加する可能性」を参照してください。

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

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

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

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

インデックス作成とインデックス再構築の再開可能インデックス オプションは SQL Server と SQL Database で利用できます (インデックス再構築は SQL Server 2017 (14.x) 以降。インデックス作成は SQL Server 2019 (15.x))、および Azure SQL データベースでもサポートされています)。 詳細については、「インデックスを作成する」および「インデックスの変更」を参照してください。

再開可能なオンライン インデックスの作成または再構築を実行するときは、次のガイドラインが適用されます。

  • インデックス メンテナンス期間の管理、計画、延長。 インデックスの作成または再構築操作を何回でも一時停止して再開し、メンテナンス期間に合わせることができます。
  • インデックスの作成または再構築の障害からの回復 (データベースのフェールオーバーやディスク領域の不足など)。
  • インデックス操作を一時停止すると、元のインデックスと新しく作成されたインデックスの両方にディスク領域が必要であり、DML 操作中に更新する必要があります。
  • インデックスの作成または再構築操作の間はトランザクション ログの切り捨てを有効にします。
  • SORT_IN_TEMPDB=ON オプションはサポートされていません。
  • 無効なインデックスはサポート対象外です。

重要

再開可能なインデックスの作成または再構築では実行時間の長いトランザクションを開いたままにする必要はなく、この操作の間のログの切り捨てと、より優れたログ領域管理が可能です。 新しい設計では、必要なデータを、再開可能な操作を再開するために必要なすべての参照と共に、データベースに保持しています。

一般に、再開可能なオンライン インデックス再構築と再開不可能なオンライン インデックス再構築の間に、パフォーマンスの違いはありません。 再開可能なインデックス作成については、再開可能なインデックス作成と再開不可能なインデックス作成の間のわずかなパフォーマンスの違いを引き起こす一定のオーバーヘッドが存在します。 ほとんどの場合、この違いは小さいテーブルでのみ顕著です。

インデックス操作を一時停止している間に再開可能なインデックスを更新すると、次のようになります。

  • 通常は読み取り専用のワークロードの場合、パフォーマンスに与える影響は大きくありません。
  • 更新の多いワークロードの場合、スループットが低下する可能性があります (弊社テストで 10% 未満の低下)。

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

Note

オンライン インデックス操作が一時停止されている間、一時停止したインデックスを含むテーブルに対してテーブル レベルの排他ロックを必要とする操作は失敗します。 これは、INSERT ... WITH (TABLOCK) 操作で最も頻繁に発生します。 次のエラーが表示されることがあります。

Msg 10637, Level 16, State 1, Line 32: Cannot perform this operation on 'object' with ID (objectid) as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

エラー 10637 を解決するには、トランザクションから TABLOCK ヒントを削除するか、インデックス操作の一時停止を解除し、完了するまで待ってからトランザクションを再試行します。

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

ELEVATE_ONLINE または ELEVATE_RESUMABLE データベース スコープ構成オプションを設定することで、データベース レベルでオンラインまたは再開可能の既定のオプションを設定できます。 これらの既定のオプションを設定すると、データベース テーブルをオフラインにする操作を誤って実行してしまう事態を回避できます。 いずれのオプションでも、エンジンは特定の操作をオンラインまたは再開可能実行に自動昇格します。
ALTER DATABASE SCOPED CONFIGURATION コマンドを使用して、オプションを FAIL_UNSUPPORTED、WHEN_SUPPORTED、または OFF のいずれかに設定できます。 オンラインと再開可能に異なる値を設定できます。

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

Note

ELEVATE_ONLINE および ELEVATE_RESUMABLE は、 XML インデックス操作には適用されません。

関連するコンテンツ