オンライン インデックス操作のガイドラインGuidelines for Online Index Operations

適用対象:○SQL Server (2008 以降)○Azure SQL Database×Azure SQL Data Warehouse ×Parallel Data Warehouse THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

インデックス操作をオンラインで実行するときは、次のガイドラインに従ってください。When you perform online index operations, the following guidelines apply:

  • 基になるテーブルに imagentexttextなどの LOB (ラージ オブジェクト) データ型が含まれている場合、クラスター化インデックスの作成、再構築、または削除は、オフラインで行う必要があります。Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains the following large object (LOB) data types: image, ntext, and text.

  • テーブルに LOB データ型が含まれていても、そのデータ型の列がキー列または非キー (付加) 列としてインデックス定義で使用されていない場合は、一意ではない非クラスター化インデックスをオンラインで作成できます。Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns.

  • ローカル一時テーブルのインデックスの作成、再構築、または削除は、オンラインでは実行できません。Indexes on local temp tables cannot be created, rebuilt, or dropped online. この制限は、グローバル一時テーブルのインデックスには当てはまりません。This restriction does not apply to indexes on global temp tables.

  • インデックスは、予期しないエラー、データベースのフェールオーバー、または PAUSE コマンドの後で、停止したところから再開できます。Indexes can be resumed from where it stopped after an unexpected failure, database failover, or a PAUSE command. ALTER INDEX」をご覧ください。See Alter Index. この機能は、SQL Server 2017 および Azure SQL Database でパブリック プレビュー段階にあります。This feature is in public preview for SQL Server 2017 and Azure SQL Database.
注意

オンラインでのインデックス操作は、 MicrosoftMicrosoft SQL ServerSQL Server のすべてのエディションで使用できるわけではありません。Online index operations are not available in every edition of MicrosoftMicrosoft SQL ServerSQL Server. SQL ServerSQL Server の各エディションでサポートされる機能の一覧については、各エディションがサポートする機能に関するページを参照してください。For a list of features that are supported by the editions of SQL ServerSQL Server, see Features supported by editions.

次の表に、オンラインで実行可能なインデックス操作、これらのオンライン操作対象から除外されるインデックス、再開可能なインデックスの制限を示します。The following table shows the index operations that can be performed online, the indexes that are excluded from these online operations, and resumable index restrictions. また、その他の制限についても記載します。Additional restrictions are also included.

オンラインのインデックス操作Online index operation 操作対象外のインデックスExcluded indexes その他の制限事項Other restrictions
ALTER INDEX REBUILDALTER INDEX REBUILD 無効化されたクラスター化インデックスまたは無効化されたインデックス付きビューDisabled clustered index or disabled indexed view

XML インデックスXML index

列ストア インデックスColumnstore index

ローカル一時テーブルのインデックスIndex on a local temp table
テーブルに操作対象外のインデックスが含まれている場合、キーワード ALL を指定すると操作が失敗する可能性があります。Specifying the keyword ALL may cause the operation to fail when the table contains an excluded index.

無効化されたインデックスの再構築には、他にも制限があります。Additional restrictions on rebuilding disabled indexes apply. 詳細については、「 インデックスと制約の無効化」を参照してください。For more information, see Disable Indexes and Constraints.
CREATE INDEXCREATE INDEX XML インデックスXML index

ビューの最初の一意クラスター化インデックスInitial unique clustered index on a view

ローカル一時テーブルのインデックスIndex on a local temp table
CREATE INDEX WITH DROP_EXISTINGCREATE INDEX WITH DROP_EXISTING 無効化されたクラスター化インデックスまたは無効化されたインデックス付きビューDisabled clustered index or disabled indexed view

ローカル一時テーブルのインデックスIndex on a local temp table

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

XML インデックスXML index

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

ローカル一時テーブルのインデックスIndex on a local temp table
1 つのステートメント内に複数のインデックスは指定できません。Multiple indexes cannot be specified within a single statement.
ALTER TABLE ADD CONSTRAINT (PRIMARY KEY または UNIQUE)ALTER TABLE ADD CONSTRAINT (PRIMARY KEY or UNIQUE) ローカル一時テーブルのインデックスIndex on a local temp table

クラスター化インデックスClustered index
サブ句は、一度に 1 つしか使用できません。Only one subclause is allowed at a time. たとえば、同じ ALTER TABLE ステートメント内で PRIMARY KEY 制約または UNIQUE 制約を追加して削除することはできません。For example, you cannot add and drop PRIMARY KEY or UNIQUE constraints in the same ALTER TABLE statement.
ALTER TABLE DROP CONSTRAINT (PRIMARY KEY または UNIQUE)ALTER TABLE DROP CONSTRAINT (PRIMARY KEY or UNIQUE) クラスター化インデックスClustered index

オンラインのインデックス操作の実行中は、基になるテーブルを変更、切り捨て、または削除できません。The underlying table cannot be modified, truncated, or dropped while an online index operation is in process.

クラスター化インデックスの作成または削除時に指定したオンライン オプションの設定 (ON または OFF) は、再構築する必要のある非クラスター化インデックスに適用されます。The online option setting (ON or OFF) specified when you create or drop a clustered index is applied to any nonclustered indexes that must be rebuilt. たとえば、クラスター化インデックスが CREATE INDEX WITH DROP_EXISTING, ONLINE=ON を使用してオンラインで構築される場合、関連するすべての非クラスター化インデックスも、オンラインで再作成されます。For example, if the clustered index is built online by using CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, all associated nonclustered indexes are re-created online also.

UNIQUE インデックスをオンラインで作成または再構築するときに、インデックス ビルダーと同時実行ユーザー トランザクションが、同じキーの挿入を試み、一意性が損なわれる場合があります。When you create or rebuild a UNIQUE index online, the index builder and a concurrent user transaction may try to insert the same key, therefore violating uniqueness. ソース テーブルの元の行が新しいインデックス (ターゲット) に移動される前に、ユーザーが入力した行が新しいインデックスに挿入されると、オンラインのインデックス操作が失敗します。If a row entered by a user is inserted into the new index (target) before the original row from the source table is moved to the new index, the online index operation will fail.

ユーザーやアプリケーションの作業によっては、オンラインのインデックス操作とデータベースの更新が連携して実行される場合、まれに、オンラインのインデックス操作によりデッドロックが発生する場合があります。Although not common, the online index operation can cause a deadlock when it interacts with database updates because of user or application activities. このようなまれなケースでは、 SQL Server データベース エンジンSQL Server Database Engine により、ユーザーやアプリケーションの作業がデッドロックの対象として選択されます。In these rare cases, the SQL Server データベース エンジンSQL Server Database Engine will select the user or application activity as a deadlock victim.

複数の新しい非クラスター化インデックスを作成しているとき、または非クラスター化インデックスを再構成しているときに限り、同じテーブルやビューに対してインデックス DDL 操作をオンラインで同時実行できます。You can perform concurrent online index DDL operations on the same table or view only when you are creating multiple new nonclustered indexes, or reorganizing nonclustered indexes. その他すべてのオンライン インデックス操作は、同時に実行しようとしても失敗します。All other online index operations performed at the same time fail. たとえば、既存のインデックスをオンラインで再構築している間に、同じテーブルの新しいインデックスをオンラインで作成することはできません。For example, you cannot create a new index online while rebuilding an existing index online on the same table.

インデックスにラージ オブジェクト型の列が含まれていて、同じトランザクション内でオンライン操作の前に更新操作がある場合は、このオンライン操作を実行できません。An online operation cannot be performed when an index contains a column of the large object type, and in the same transaction there are update operations before this online operation. この問題を回避するには、オンライン操作をトランザクションの外部に配置するか、トランザクション内で更新操作の前に配置してください。To work around this issue, place the online operation outside the transaction or place it before any updates in the transaction.

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

オンライン インデックス操作には、オフライン インデックス操作より多くのディスク容量が必要になります。Online index operations require more disk space requirements than offline index operations.

  • インデックス作成操作やインデックス再構築操作の間、作成または再構築されるインデックスのために追加の領域が必要になります。During index creation and index rebuild operations, additional space is required for the index being built (or rebuilt).
  • また、仮のマッピング インデックス操作にもディスク容量が必要になります。In addition, disk space is required for the temporary mapping index. この一時インデックスは、クラスター化インデックスを作成、再構築、または削除する、オンライン インデックス操作で使用されます。This temporary index is used in online index operations that create, rebuild, or drop a clustered index.
    • クラスター化インデックスをオンラインで削除する場合と、クラスター化インデックスをオンラインで作成または再構築する場合は同じ量の領域が必要になります。Dropping a clustered index online requires as much space as creating (or rebuilding) a clustered index online.

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

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

オンラインのインデックス操作では、同時実行ユーザーによる更新操作は許可されていますが、その更新操作の負荷が非常に高いと、インデックス操作の処理時間が長くなります。Although online index operations permit concurrent user update activity, the index operations will take longer if the update activity is very heavy. 通常、オンラインのインデックス操作は、同時実行更新操作の負荷レベルに関係なく、同じインデックス操作をオフラインで行った場合よりも時間がかかります。Typically, online index operations will be slower than equivalent offline index operations regardless of the concurrent update activity level.

オンラインのインデックス操作中は、ソースの構造とターゲットの構造の両方が保持されるため、挿入、更新、削除のトランザクションによるリソースの使用量は、最大 2 倍にまで増加する場合があります。Because both the source and target structures are maintained during the online index operation, the resource usage for insert, update, and delete transactions is increased, potentially up to double. このため、インデックス操作中のパフォーマンスが低下し、リソースの使用量 (特に CPU 使用時間) が増大する可能性があります。This could cause a decrease in performance and greater resource usage, especially CPU time, during the index operation. オンラインでのインデックス操作は、完全にログに記録されます。Online index operations are fully logged.

オンラインでの操作を推奨しますが、実際の環境と特定の要件を評価してください。Although we recommend online operations, you should evaluate your environment and specific requirements. オフラインでインデックス操作を実行することが最適な場合もあります。It may be optimal to run index operations offline. この場合、操作中にユーザーからのデータ アクセスは制限されますが、操作をより短時間で完了でき、使用するリソースも軽減できます。In doing this, users have restricted access to the data during the operation, but the operation finishes faster and uses fewer resources.

SQL Server 2016 を実行するマルチプロセッサ コンピューターでは、他のクエリと同様に、インデックスのステートメントがこのステートメントに関連付けられているスキャン操作や並べ替え操作の実行に、より多くのプロセッサを使用する場合があります。On multiprocessor computers that are running SQL Server 2016, index statements may use more processors to perform the scan and sort operations associated with the index statement just like other queries do. MAXDOP インデックス オプションを使用して、オンラインでのインデックス操作専用に使用するプロセッサ数を制御できます。You can use the MAXDOP index option to control the number of processors dedicated to the online index operation. このようにすることで、インデックス操作が使用するリソースと他の同時実行ユーザーが使用するリソースのバランスをとることができます。In this way, you can balance the resources that are used by index operation with those of the concurrent users. 詳細については、「 並列インデックス操作の構成」を参照してください。For more information, see Configure Parallel Index Operations. 並列インデックス操作をサポートする SQL Server のエディションの詳細については、各エディションがサポートする機能に関するページを参照してください。For more information about the editions of SQL Server that support Parallel indexed operations, see Features Supported by editions.

S-Lock または Sch-M ロックはインデックス操作の最後のフェーズで保持されるので、BEGIN TRANSACTION...COMMIT ブロックなど、明示的なユーザー トランザクション内でのオンラインのインデックス操作を実行する場合は十分に注意してください。Because an S-lock or Sch-M lock is held in the final phase of the index operation, be careful when you run an online index operation inside an explicit user transaction, such as BEGIN TRANSACTION...COMMIT block. この場合、ロックがトランザクションの最後まで保持され、その結果ユーザーの同時実行性が損なわれます。Doing this causes the lock to be held until the end of the transaction, therefore impeding user concurrency.

インデックスをオンラインで再構築すると、 MAX DOP > 1 オプションと ALLOW_PAGE_LOCKS = OFF オプションでの実行が許可されたときに断片化が増加する可能性があります。Online index rebuilding may increase fragmentation when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF options. 詳細については、「 動作方法: オンラインでのインデックス再構築 - 断片化が増加する可能性」を参照してください。For more information, see How It Works: Online Index Rebuild - Can Cause Increased Fragmentation.

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

オフライン、オンラインを問わず、大規模なインデックス操作を行うと、大量のデータ読み込みが発生し、トランザクション ログがすぐにいっぱいになってしまうことがあります。Large-scale index operations, performed offline or online, can generate large data loads that can cause the transaction log to quickly fill. インデックス操作をロールバックできるようにするため、インデックス操作が完了するまでは、トランザクション ログを切り捨てることはできません。ただし、インデックス操作中にログをバックアップすることはできます。To make sure that the index operation can be rolled back, the transaction log cannot be truncated until the index operation has been completed; however, the log can be backed up during the index operation. したがって、トランザクション ログには、インデックス操作中にインデックス操作によるトランザクションと、同時実行ユーザーによるトランザクションの両方を格納できるだけの十分な領域が割り当てられている必要があります。Therefore, the transaction log must have sufficient space to store both the index operation transactions and any concurrent user transactions for the duration of the index operation. 詳細については、「 インデックス操作用のトランザクション ログのディスク領域」を参照してください。For more information, see Transaction Log Disk Space for Index Operations.

再開可能なインデックスの再構築に関する考慮事項Resumable Index Rebuild Considerations

注意

ALTER INDEX」をご覧ください。See Alter Index. この機能は、SQL Server 2017 および Azure SQL Database でパブリック プレビュー段階にあります。This feature is in public preview for SQL Server 2017 and Azure SQL Database.

再開可能なオンライン インデックスの再構築を実行するときは、次のガイドラインが適用されます。When you perform resumable online index rebuild the following guidelines apply:

  • インデックス メンテナンス期間の管理、計画、延長。Managing, planning and extending of index maintenance windows. インデックス再構築操作を何回でも一時停止して再開し、メンテナンス期間に合わせることができます。You can pause and restart an index rebuild operation multiple times to fit your maintenance windows.
  • インデックス再構築の障害からの回復 (データベースのフェールオーバーやディスク領域の不足など)。Recovering from index rebuild failures (such as database failovers or running out of disk space).
  • インデックス操作を一時停止すると、元のインデックスと新しく作成されたインデックスの両方にディスク領域が必要であり、DML 操作中に更新する必要があります。When an index operation is paused, both the original index and the the newly created one require disk space and need to be updated during DML operations.

  • インデックス再構築操作の間は切り捨てログの切り捨てを有効にします (通常のオンライン インデックス操作に対してこの操作を実行することはできません)。Enables truncation of truncation logs during an index rebuild operation (this operation cannot be performed for a regular online index operation).

  • SORT_IN_TEMPDB=ON オプションはサポートされていません。SORT_IN_TEMPDB=ON option is not supported
重要

再開可能な再構築では実行時間の長い切り捨てを開いたままにする必要はなく、この操作の間のログの切り捨てと、より優れたログ領域管理が可能です。Resumable rebuild does not require you to keep open a long running truncation, allowing log truncation during this operation and a better log space management. 新しい設計では、必要なデータを、再開可能な操作を再開するために必要なすべての参照と共に、データベースに保持しています。With the new design, we managed to keep necessary data in a database together with all references required to restart the resumable operation.

一般に、再開可能なオンライン インデックス再構築と再開不可能なオンライン インデックス再構築の間に、パフォーマンスの違いはありません。Generally, there is no performance difference between resumable and non-resumable online index rebuild. インデックス再構築操作を一時停止している間に再開可能なインデックスを更新すると、次のようになります。When you update a resumable index while an index rebuild operation is paused:

  • 通常は読み取り専用のワークロードの場合、パフォーマンスに与える影響は大きくありません。For read-mostly workloads, the performance impact is insignificant.
  • 更新の多いワークロードの場合、スループットが低下する可能性があります (弊社テストで 10% 未満の低下)。For update-heavy workloads, you may experience some throughput degradation (our testing shows less than 10% degradation).

一般に、再開可能なオンライン インデックス再構築と再開不可能なオンライン インデックス再構築の間に、最適化の品質の違いはありません。Generally, there is no difference in defragmentation quality between resumable and non-resumable online index rebuild.

オンライン インデックス操作の動作原理How Online Index Operations Work

オンラインでのインデックス操作の実行Perform Index Operations Online

ALTER INDEX (Transact-SQL)ALTER INDEX (Transact-SQL)

CREATE INDEX (Transact-SQL)CREATE INDEX (Transact-SQL)