クエリのパフォーマンスを向上させてリソースの消費を削減するためにインデックスのメンテナンスを最適化する

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance Analytics Platform System (PDW)

この記事は、インデックスのメンテナンスを実行するタイミングと方法を決定するのに役立ちます。 インデックスの断片化やページ密度などの概念、およびクエリのパフォーマンスやリソースの消費量へのそれらの影響について説明します。 インデックスのメンテナンス方法 (インデックスの再構成とインデックスの再構築) について説明し、パフォーマンス向上の可能性とメンテナンスに必要なリソース消費量の間のバランスを取るインデックス メンテナンス戦略を提案します。

概念: インデックスの断片化とページ密度

インデックスの断片化とはどのようなもので、パフォーマンスにどのような影響があるのでしょうか。

• B ツリー (行ストア) インデックスの場合、インデックスのキー値に基づくインデックス内での論理的な順序と、インデックス ページでの物理的な順序が一致しないページがインデックスにあると、断片化が存在します。

  Note

  ドキュメントでは、一般的にインデックスに関して B ツリーという用語が使用されます。 行ストア インデックスで、データベース エンジンによって B+ ツリーが実装されます。 これは、列ストア インデックスやメモリ最適化テーブルのインデックスには適用されません。 詳細については、「SQL Server と Azure SQL のインデックスのアーキテクチャとデザイン ガイド」を参照してください。

• データベース エンジンでは、基になるデータに対して挿入、更新、または削除の各操作が行われるたびに、インデックスが自動的に変更されます。 たとえば、テーブルに行が追加されると、行ストア インデックス内の既存のページが分割されて、新しい行を挿入するための余白が作成される場合があります。 時間が経つに従い、このような変更により、インデックス内の情報がデータベース内に散在 (断片化) するようになる可能性があります。

• フルまたは範囲インデックス スキャンを使用して多数のページを読み取るクエリの場合、インデックスの断片化が大きくなると、データを読み取るために追加の I/O が必要になるため、クエリのパフォーマンスが低下することがあります。 同じ量のデータを読み取るために、少数の大きな I/O 要求ではなく、多数の小さな I/O 要求が必要になります。

• ランダム I/O のパフォーマンスよりシーケンシャル I/O のパフォーマンスの方が優れているストレージ サブシステムの場合、インデックスが断片化すると、断片化されたインデックスを読み取るために必要なランダム I/O が増えるため、パフォーマンスが低下する可能性があります。

ページ密度 ("ページのゆとり" とも呼ばれます) とはどのようなもので、パフォーマンスにどのような影響があるのでしょうか。

• データベース内の各ページは、異なる数の行を含むことができます。 行がページのすべての領域を占める場合、ページ密度は 100% です。 ページが空の場合、ページ密度は 0% です。 密度が 100% のページを、新しい行に対応するために 2 つのページに分割すると、2 つの新しいページの密度は約 50% になります。
• ページの密度が低い場合、同じ量のデータを格納するために必要なページ数が増えます。 これは、このデータの読み取りと書き込みに必要な I/O が増え、このデータをキャッシュするためにより多くのメモリが必要になることを意味します。 メモリに制限があると、クエリに必要なページのキャッシュが減少し、ディスク I/O がさらに増えます。 つまり、ページ密度が低いと、パフォーマンスに悪影響があります。
• インデックスの作成、再構築、または再編成中にページに行を追加データベース エンジン場合、インデックスのフィル係数が 100 (またはこのコンテキストで同等である 0) 以外の値に設定されている場合、ページは完全には塗りつぶされません。 これにより、ページ密度が低下し、同様に I/O のオーバーヘッドが増加して、パフォーマンスが低下します。
• ページ密度が低いと、中間 B ツリー レベルの数が増える可能性があります。 これにより、インデックスのスキャンとシークでリーフ レベルのページを検索するときの CPU と I/O のコストがやや増加します。
• クエリ オプティマイザーによってクエリ プランが編集されるとき、クエリに必要なデータを読み取るために必要な I/O のコストが考慮します。 ページ密度が低いと、読み取るページが増えるため、I/O のコストが高くなります。 これは、クエリ プランの選択に影響を与える可能性があります。 たとえば、時間の経過と共にページ分割によってページ密度が低下すると、クエリは同じでもオプティマイザーによるプランの編集が異なり、パフォーマンスとリソース消費のプロファイルが変化する可能性があります。

インデックスの断片化とページ密度を計測する

断片化とページ密度はどちらも、インデックスのメンテナンスを実行するかどうか、そして使用するメンテナンス方法を判断するときに考慮する必要がある要素の中の 1 つです。

行ストア インデックスと列ストア インデックスでは、断片化の定義が異なります。 行ストア インデックスの場合は、sys.dm_db_index_physical_stats を使用することで、特定のインデックス、テーブルやインデックス付きビューのすべてのインデックス、データベースのすべてのインデックス、またはすべてのデータベースのすべてのインデックスについて、断片化とページ密度がわかります。 パーティション インデックスの場合は、sys.dm_db_index_physical_stats() によってこの情報がパーティションごとに提供されます。

sys.dm_db_index_physical_stats が返す結果セットには、次の列が含まれます。

列ストア インデックスの圧縮された行グループの場合、断片化は、合計行数に対する削除された行の割合として定義されます (パーセントで表されます)。 sys.dm_db_column_store_row_group_physical_stats を使用すると、特定のインデックス、テーブルのすべてのインデックス、またはデータベースのすべてのインデックスについて、行グループあたりの合計行数と削除された行数がわかります。

sys.dm_db_column_store_row_group_physical_stats が返す結果セットには、次の列が含まれます。

列ストア インデックスの圧縮された行グループの断片化は、次の式を使用して計算できます。

100.0*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0)

断片化とページ密度を確認するためのクエリの例については、「例」を参照してください。

インデックスのメンテナンス方法: 再構成と再構築

次のいずれかの方法を使用して、インデックスの断片化を減らし、ページ密度を上げることができます。

• インデックスを再構成する
• インデックスを再構築する

インデックスを再構成する

インデックスの再構成は、インデックスの再構築より消費するリソースが少なくて済みます。 そのため、インデックスの再構築を使用する特別な理由がない限り、それを優先するインデックス メンテナンス方法にする必要があります。 再構成は常にオンライン操作です。 つまり、ALTER INDEX ... REORGANIZE 操作の間、オブジェクト レベルのロックが長時間保持されることはなく、基になるテーブルへのクエリまたは更新を続行できます。

• 行ストアの場合は、データベース エンジンが、リーフ レベル ページのみをリーフ ノードの論理順序 (左から右) に合わせて物理的に並べ替えることにより、テーブルやビュー上にあるクラスター化および非クラスター化インデックスのリーフ レベルが最適化されます。 また、再構成によってインデックス ページが圧縮され、インデックスの FILL FACTOR と同じページ密度になります。 FILL FACTOR 設定を表示するには、sys.indexes を使用します。 構文の例については、行ストアの再構成の例に関する記事を参照してください。
• 列ストア インデックスを使用している場合は、時間が経つと、データの挿入、更新、削除の後で、デルタ ストアが複数の小さな行グループになることがあります。 列ストア インデックスを再構成すると、デルタ ストアの行グループが列ストアの圧縮された行グループに強制的に変換されて、小さな圧縮された行グループが大きな行グループに結合されます。 再構成操作により、列ストアで削除としてマークされされた行も、物理的に削除されます。 列ストア インデックスを再構成するには、データを圧縮するために追加の CPU リソースが必要な場合があります。 操作の実行中は、パフォーマンスが低下する可能性があります。 ただし、データが圧縮されると、クエリのパフォーマンスは改善します。 構文の例については、列ストアの再構成の例に関する記事を参照してください。

インデックスを再構築する

インデックスの再構築では、インデックスを削除し再作成します。 インデックスの種類とデータベース エンジンのバージョンによっては、再構築操作をオフラインまたはオンラインで実行できます。 オフラインのインデックス再構築は通常の場合、オンラインの再構築より時間がかかりませんが、再構築操作の間はオブジェクト レベルのロックが保持され、クエリによるテーブルまたはビューへのアクセスはブロックされます。

オンラインのインデックス再構築では、操作が終了するまでオブジェクト レベルのロックは必要ありませんが、再構築を完了するためにロックを短時間保持する必要があります。 データベース エンジンのバージョンによっては、オンラインのインデックス再構築を再開可能な操作として開始できます。 再開可能なインデックス再構築は、一時停止して、その時点までの進行状況を維持することができます。 再開可能な再構築操作は、一時停止または中断された後で再開するか、再構築を完了する必要がなくなった場合は中止することができます。

Transact-SQL の構文については、ALTER INDEX REBUILD に関する記事を参照してください。 オンラインでのインデックス再構築の詳細については、「オンラインでのインデックス操作の実行」を参照してください。

• 行ストア インデックスの場合、再構築を行うと、インデックスのすべてのレベルで断片化が解消され、指定または現在の FILL FACTOR に基づいてページが圧縮されます。 ALL を指定すると、テーブルのすべてのインデックスが、1 回の操作で削除されて再構築されます。 128 以上のエクステントがあるインデックスを再構築すると、データベース エンジンによるページの割り当て解除とそれに関連するロックの取得が、再構築の完了後まで延期されます。 構文の例については、行ストアの再構築の例に関する記事を参照してください。

• 列ストア インデックスの場合、再構築を行うと、断片化が解消され、デルタ ストア行が列ストアに移動されて、削除対象としてマークされている行が物理的に削除されます。 構文の例については、列ストアの再構築の例に関する記事を参照してください。

  ヒント

  SQL Server 2016 (13.x) 以降では、REORGANIZE によってオンライン操作として基本的な再構築が実行されるため、通常、列ストア インデックスの再構築は必要ありません。

インデックスの再構築を使用したデータ破損からの回復

2008 (10.0.x) 以前のバージョンの SQL Server では、行ストアの非クラスター化インデックスを再構築することで、インデックスでのデータの破損による不整合を修正できる場合がありました。

非クラスター化インデックスをオフラインで再構築することで、非クラスター化インデックスのそのような不整合を修正できます。 一方、オンラインでインデックスを再構築する場合は、オンライン再構築メカニズムにより既存の非クラスター化インデックスを基に再構築が行われるので、不整合が引き継がれるため、非クラスター化インデックスの不整合を修復できません。 インデックスをオフラインで再構築すると、クラスター化インデックス (またはヒープ) のスキャンを強制できることがあり、非クラスター化インデックスの整合性のないデータが、クラスター化インデックスまたはヒープのデータに置き換えられます。

クラスター化インデックスまたはヒープがデータ ソースとして使用されるようにするには、非クラスター化インデックスを再構築するのではなく削除して再作成します。 以前のバージョンと同様に、影響を受けるデータをバックアップから復元することで、不整合から復旧できます。 ただし、非クラスター化インデックスの不整合は、オフラインで再構築するか再作成することで修復できる場合があります。 詳細については、「DBCC CHECKDB (Transact-SQL)」を参照してください。

インデックスと統計の自動管理

1 つまたは複数のデータベースでインデックスの断片化と統計の更新を自動的に管理するには、Adaptive Index Defrag のようなソリューションを使用します。 このプロシージャでは、断片化レベルやその他のパラメーターに基づいてインデックスを再構築または再構成するか、線形しきい値で統計を更新するかが自動的に選択されます。

行ストア インデックスの再構築と再構成に固有の注意点

以下のシナリオでは、テーブル上のすべての行ストア非クラスター化インデックスが自動的に再構築されます。

• CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON) を使用した別のキーでのクラスター化インデックスの再作成など、テーブルでのクラスター化インデックスの作成
• テーブルがヒープとして格納される原因となる、クラスター化インデックスの削除

以下のシナリオでは、同じテーブルでも、すべての行ストア非クラスター化インデックスの自動的な再構築は行われません。

• クラスター化インデックスの再構築
• パーティション構成の適用や、クラスター化インデックスの別のファイル グループへの移動など、クラスター化インデックス ストレージの変更

ALTER INDEX ... REORGANIZE ステートメントで ALL を指定すると、テーブル上のクラスター化、非クラスター化、XML の各インデックスが再構成されます。

小さな行ストア インデックスを再構築または再構成しても、通常、断片化が解消することはありません。 SQL Server 2014 (12.x) またはそれ以前の場合、SQL Server Database Engineは、混合エクステントを使用して領域を割り当てます。 そのため、小さいインデックスのページは混合エクステントに格納されることがあり、そのようなインデックスは暗黙的に断片化されます。 混合エクステントは最大 8 つのオブジェクトで共有されるため、小さなインデックスを再構成または再構築しても、その断片化は解消されない場合があります。

列ストア インデックスの再構築に固有の注意点

列ストアインデックスを再構築するときは、データベース エンジンによって元の列ストア インデックスから、デルタ ストアを含むすべてのデータが読み取られます。 データは新しい行グループに結合され、すべての行グループが列ストアに圧縮されます。 データベース エンジンにより、削除対象としてマークされた行を物理的に削除することで、列ストアが非断片化されます。

テーブル全体ではなくパーティションを再構築する

インデックスが大きいとテーブル全体の再構築には時間がかかり、再構築中にインデックス全体の追加コピーを格納するために十分なディスク領域が必要です。

パーティション テーブルの場合、断片化が一部のパーティションにのみ存在する場合は (たとえば、UPDATE、DELETE、または MERGE ステートメントによって多数の行が影響を受けたパーティションなど)、列ストア インデックス全体を再構築する必要はありません。

データの読み込みまたは変更の後でパーティションを再構築すると、すべてのデータが列ストアの圧縮された行グループに格納されるようになります。 データ読み込みプロセスにより、102,400 行未満のバッチを使用してパーティションにデータが挿入されると、パーティションのデルタ ストアに複数の開かれた行グループが作成されます。 再構築を行うと、すべてのデルタ ストア行が列ストアの圧縮された行グループに移動されます。

列ストア インデックスの再構成に固有の注意点

列ストア インデックスを再構成すると、データベース エンジンによって、デルタ ストア内の閉じられた各行グループが、圧縮された行グループとして列ストアに圧縮されます。 SQL Server 2016 (13.x) 以降および Azure SQL Database の REORGANIZE コマンドでは、次の追加のデフラグ最適化がオンラインで実行されます。

• 行の 10% 以上が論理的に削除されていると、行グループから行が物理的に削除されます。 たとえば、100 万行の圧縮された行グループで 10 万行が削除された場合、データベース エンジンにより、削除された行が除去され、90 万行の行グループが再圧縮されて、ストレージの占有領域が削減されます。
• 1 つまたは複数の圧縮された行グループを結合して、行グループあたりの行数を最大で 1,048,576 行まで増やすことができます。 たとえば、それぞれが 102,400 行の 5 つのバッチを一括挿入すると、5 つの圧縮された行グループが作成されます。 REORGANIZE を実行すると、これらの行グループは、512,000 行の 1 つの圧縮された行グループにマージされます。 この処理は、ディクショナリ サイズまたはメモリに関する制限が存在していないことを前提としています。
• 10% 以上の行が削除対象としてマークされている行グループに対して、データベース エンジンは他の行グループとの結合を試みます。 たとえば、行グループ 1 は圧縮されていて 500,000 行あり、行グループ 21 は圧縮されていて 1,048,576 行あるとします。 行グループ 21 は行の 60% が削除対象としてマークされており、残りは 409,830 行です。 データベース エンジンでは、優先的にこの 2 つの行グループが結合されて、909,830 行の新しい行グループが圧縮されます。

データの読み込みが実行された後、デルタ ストアには複数の小さな行グループが含まれることがあります。 ALTER INDEX REORGANIZE を使用してこれらの行グループを列ストアに強制的に変換した後、小さい圧縮された行グループを、より大きい圧縮された行グループに結合できます。 再構成操作では、削除対象としてマークされた行も、列ストアから削除されます。

インデックスのメンテナンスを実行する前の考慮事項

インデックスの再構成または再構築によって実行されるインデックスのメンテナンスでは、リソースが大量に消費されます。 これにより、CPU 使用率、メモリ使用量、ストレージ I/O が大幅に増加します。 一方で、それによって得られるメリットは、データベースのワークロードやその他の要因により、非常に重要なものから極めて小さいものまで幅があります。

不要なリソース使用を回避するために、インデックスのメンテナンスはむやみに実行しないでください。 代わりに、インデックスのメンテナンスを行うことによるパフォーマンスの利点を、推奨される戦略を使用してワークロードごとに実験的に確認し、それらの利点を達成するために必要なリソース コストとワークロードの影響について検討する必要があります。

インデックスの再構成や再構築によってパフォーマンスが向上する可能性は、インデックスの断片化が大きい場合、またはページ密度が低い場合に、高くなる可能性があります。 ただし、考慮すべき点はこれらだけではありません。 クエリ パターン (トランザクション処理に対して、分析やレポートなど)、ストレージ サブシステムの動作、使用可能なメモリ、データベース エンジンの機能強化などの要因はすべて、影響を与えます。

インデックスの再構築の良い副作用

多くの場合、インデックスの再構築後にはパフォーマンスが向上します。 ただし、多くの場合、これらの向上は、断片化の減少やページ密度の増加には関係ありません。

インデックスの再構築には重要な利点があります。つまり、インデックスのすべての行がスキャンされることにより、インデックスのキー列の統計が更新されます。 これは、UPDATE STATISTICS ... WITH FULLSCAN を実行することと同じです。これにより、統計が最新になり、既定のサンプリングされた統計の更新と比較して品質が向上する場合があります。 統計が更新されると、それらを参照するクエリ プランが再編集されます。 古い統計、不十分な統計サンプリング率、またはその他の理由により、クエリの前のプランが最適でなかった場合、再編集されたプランによってパフォーマンスが向上することがよくあります。

お客様は、多くの場合、この向上はインデックスの再構築自体によるものであり、断片化が減少してページ密度が増加した結果であると、誤って判断します。 実際には、インデックスを再構築する代わりに統計を更新することにより、はるかに少ないリソース コストで同じ利点を実現できることがよくあります。

インデックスのメンテナンスの戦略

Microsoft はお客様に、次のインデックス メンテナンス戦略を検討して採用することをお勧めします。

• インデックスのメンテナンスによってワークロードが常に著しく向上するとは限りません。
• インデックスの再編成または再構築による、ワークロードでのクエリのパフォーマンスに対する具体的な影響を測定してください。 クエリ ストアは、A/B テスト手法を使用して、"メンテナンス前" と "メンテナンス後" のパフォーマンスを測定するのによい方法です。
• インデックスの再構築によってパフォーマンスが向上する場合は、それを統計の更新に置き換えてみてください。 同じように向上する可能性があります。 その場合、インデックスの再構築をそれほど頻繁に、またはまったく行う必要がなく、代わりに定期的な統計の更新で済ますことができる場合があります。 一部の統計については、WITH SAMPLE ... PERCENT または WITH FULLSCAN 句を使用して、サンプリング率を上げる必要がある場合があります (これは一般的ではありません)。
• 時間を追ってインデックスの断片化とページ密度を監視し、これらの値の上昇または下降の傾向とクエリのパフォーマンスの間に、相関関係があるかどうかを確認します。 断片化の上昇またはページ密度の下降によりパフォーマンスが許容できないほど低下する場合は、インデックスを再編成または再構築します。 多くの場合、パフォーマンスが低下しているクエリで使用される特定のインデックスのみの再構成または再構築で十分です。 これにより、データベースにすべてのインデックスを保持するためのリソース コストが上昇するのを防ぎます。
• 断片化およびページ密度とパフォーマンスの相関関係を確立することで、インデックスのメンテナンスの頻度を決定することもできます。 決まったスケジュールでメンテナンスを実行する必要があると、思い込まないでください。 より適切な戦略は、断片化とページ密度を監視し、パフォーマンスの低下が許容範囲を超える前に、必要に応じてインデックスのメンテナンスを実行することです。
• インデックスのメンテナンスが必要であり、リソース コストが許容できると判断した場合は、可能な場合にはリソース配分状況が低い時間帯に (ある場合) メンテナンスを実行します。
• リソース配分状況のパターンは時間の経過と同時に変化する可能性があるため、定期的にテストします。

Azure SQL Database および Azure SQL Managed Instance でのインデックス メンテナンス

Azure SQL Database と Azure SQL Managed Instance では、上記の考慮事項と戦略に加えて、インデックスのメンテナンスのコストと利点を考慮することが特に重要です。 お客様は、以下の点を考慮して、どうしても必要な場合にのみそれを実行する必要があります。

• Azure SQL Database と Azure SQL Managed Instance には、プロビジョニングされた価格レベルに従って、CPU、メモリ、I/O の消費量に上限を設けるよう、リソース ガバナンスが実装されています。 これらの上限は、インデックスのメンテナンスを含むすべてのユーザー ワークロードに適用されます。 すべてのワークロードによる累積リソース使用量がリソースの上限に近づいている場合、再構築または再構成操作を実行すると、リソースの競合のために、他のワークロードのパフォーマンスが低下する可能性があります。 たとえば、インデックスの再構築を同時に実行したため、トランザクション ログの I/O が 100% になり、一括データ読み込みの速度が低下する可能性があります。 Azure SQL Managed Instance では、リソース割り当てが制限された別の Resource Governor ワークロード グループでインデックスのメンテナンスを実行することで、インデックス メンテナンス期間が長くなるのと引き換えに、この影響を減らすことができます。
• コストを削減するため、お客様は多くの場合、データベース、エラスティック プール、マネージド インスタンスを、最小限のリソース ヘッドルームでプロビジョニングします。 価格レベルは、アプリケーションのワークロードに十分なものが選択されます。 アプリケーションのパフォーマンスを低下させることなく、インデックスのメンテナンスによるリソース配分状況の大幅な増加に対応するには、より多くのリソースをプロビジョニングし、コストを増やすことが必要になる場合がありますが、それでアプリケーションのパフォーマンスが向上するとは限りません。
• エラスティック プールでは、リソースはプール内のすべてのデータベース間で共有されます。 特定のデータベースがアイドル状態の場合でも、そのデータベースでインデックスのメンテナンスを実行すると、同じプール内の他のデータベースで同時に実行されているアプリケーションのワークロードに影響を与える可能性があります。 詳細については、「高密度エラスティック プールでのリソース管理」を参照してください。
• Azure SQL データベース と Azure SQL Managed Instance で使用されるほとんどの種類のストレージでは、順次 I/O とランダム I/O の間にパフォーマンスの違いはありません。 これにより、インデックスの断片化がクエリのパフォーマンスに与える影響が軽減されます。
• 読み取りスケールアウトまたは geo レプリケーションのレプリカを使用している場合、プライマリ レプリカでインデックスのメンテナンスが実行されている間に、レプリカでのデータ待機時間が長くなることがよくあります。 インデックスのメンテナンスによるトランザクション ログの生成の増加に耐えられるよう、geo レプリカに十分なリソースがプロビジョニングされていない場合、プライマリからの大きな遅れが発生し、システムによって再シードされる可能性があります。 これにより、再シードが完了するまでレプリカを使用できなくなります。 さらに、Premium と Business Critical のサービス レベルでは、高可用性のために使用されるレプリカが、インデックスのメンテナンスの間に、同じようにプライマリから大きく遅れる場合があります。 インデックスのメンテナンス中またはその直後にフェールオーバーが必要になった場合、予想より時間がかかることがあります。
• プライマリ レプリカでインデックスの再構築が実行されるのと同時に、読み取り可能レプリカで実行時間の長いクエリが実行されると、レプリカで再実行スレッドがブロックされないように、クエリが自動的に終了されることがあります。

Azure SQL Database と Azure SQL Managed Instance では、1 回限りまたは定期的なインデックス メンテナンスが必要になる場合がある、特有ですが一般的ではないシナリオがあります。

• データベースのページ密度を増やし、使用領域を減らすことにより、価格レベルのサイズ制限を超えないようにします。 これにより、サイズ制限が高い上位の価格レベルにスケールアップする必要がなくなります。
• ファイルの縮小が必要になった場合は、ファイルを縮小する前にインデックスを再構築または再構成すると、ページの密度が上がります。 これにより、移動の必要なページ数が減るため、縮小操作が速くなります。 詳細については、以下にアクセスしてください。
  • Azure SQL Database でデータベースのファイル領域を管理する
  • Azure SQL Managed Instance でデータベースのファイル領域を管理する

制限事項と制約事項

128 エクステントを超える行ストア インデックスは、論理フェーズと物理フェーズの 2 つの独立したフェーズで再構築されます。 論理フェーズでは、インデックスによって使用されている既存のアロケーション ユニットが、割り当て解除に設定されます。その後、データ行がコピーされ、並べ替えられてから、再構築されたインデックスを格納するために作成された新しいアロケーション ユニットに移動されます。 物理フェーズでは、バックグラウンドで行われる短いトランザクションで、以前に割り当て解除に設定されたアロケーション ユニットが物理的に削除され、ロックの必要はあまり多くありません。 アロケーション ユニットの詳細については、「ページとエクステントのアーキテクチャ ガイド」を参照してください。

この操作では、同じファイル グループ内の他のファイルではなく、同じファイル上に一時的な作業ページを割り当てなければならないため、ALTER INDEX REORGANIZE ステートメントには、使用可能な領域があるインデックスを含むデータ ファイルが必要です。 ファイル グループに使用可能な空き領域がある場合でも、データ ファイルの領域が不足していると、再構成操作中にエラー 1105: Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup が発生することがあります。

ALLOW_PAGE_LOCKS を OFF に設定した場合、インデックスを再構成することはできません。

SQL Server 2017 (14.x) までは、クラスター化列ストア インデックスの再構築はオフライン操作です。 データベース エンジンでは、再構築が行われている間、テーブルまたはパーティションの排他ロックを取得する必要があります。 NOLOCK、READ COMMITTED スナップショット分離 (RCSI)、またはスナップショット分離を使用しているときでも、再構築の間は、データはオフラインになり使用できません。 SQL Server 2019 (15.x) 以降では、ONLINE = ON オプションを使用してクラスター化列ストア インデックスを再構築できます。

統計に関する制限

• インデックスが作成または再構築されるとき、テーブル内のすべての行がスキャンされて、統計が作成または更新されます。これは、CREATE STATISTICS または UPDATE STATISTICS で FULLSCAN 句を使用することと同じです。 ただし、SQL Server 2012 (11.x) 以降では、パーティション インデックスが作成または再構築されるとき、テーブル内のすべての行をスキャンして統計が作成または更新されることはありません。 代わりに、既定のサンプリング率が使用されます。 テーブル内のすべての行をスキャンしてパーティション インデックスの統計を作成または更新するには、CREATE STATISTICS または UPDATE STATISTICS と FULLSCAN 句を使用します。
• 同様に、インデックスの作成または再構築の操作が再開可能なときは、既定のサンプリング率を使用して統計が作成または更新されます。 統計が作成されたとき、または最後に更新されたときに、PERSIST_SAMPLE_PERCENT 句が ON に設定されていた場合、再開可能なインデックス操作により、保持されているサンプリング率を使用して統計が作成または更新されます。
• インデックスが再構成されるとき、統計は更新されません。

例

Transact-SQL を使用して行ストア インデックスの断片化とページ密度を調べる

次の例では、現在のデータベース内のすべての行ストア インデックスの断片化と平均ページ密度を確認します。 SAMPLED モードを使用して、実行可能な結果を迅速に取得します。 より正確な結果を得るには、DETAILED モードを使用します。 これには、すべてのインデックス ページのスキャンが必要になり、時間がかかることがあります。

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

前のステートメントによって、次のような結果セットが返されます。

schema_name  object_name           index_name                               index_type    avg_fragmentation_in_percent avg_page_space_used_in_percent page_count  alloc_unit_type_desc
------------ --------------------- ---------------------------------------- ------------- ---------------------------- ------------------------------ ----------- --------------------
dbo          FactProductInventory  PK_FactProductInventory                  CLUSTERED     0.390015600624025            99.7244625648629               3846        IN_ROW_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            89.6839757845318               497         LOB_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            80.7132814430442               251         IN_ROW_DATA
dbo          FactFinance           NULL                                     HEAP          0                            99.7982456140351               239         IN_ROW_DATA
dbo          ProspectiveBuyer      PK_ProspectiveBuyer_ProspectiveBuyerKey  CLUSTERED     0                            98.1086236718557               79          IN_ROW_DATA
dbo          DimCustomer           IX_DimCustomer_CustomerAlternateKey      NONCLUSTERED  0                            99.5197553743514               78          IN_ROW_DATA

詳細については、sys.dm_db_index_physical_stats に関する記事をご覧ください。

Transact-SQL を使用して列ストア インデックスの断片化を確認する

次の例では、現在のデータベース内の圧縮された行グループで、すべての列ストア インデックスの平均断片化を確認します。

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       100.0 * (ISNULL(SUM(rgs.deleted_rows), 0)) / NULLIF(SUM(rgs.total_rows), 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS rgs
ON i.object_id = rgs.object_id
   AND
   i.index_id = rgs.index_id
WHERE rgs.state_desc = 'COMPRESSED'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc
ORDER BY schema_name, object_name, index_name, index_type;

前のステートメントによって、次のような結果セットが返されます。

schema_name  object_name            index_name                           index_type                avg_fragmentation_in_percent
------------ ---------------------- ------------------------------------ ------------------------- ----------------------------
Sales        InvoiceLines           NCCX_Sales_InvoiceLines              NONCLUSTERED COLUMNSTORE  0.000000000000000
Sales        OrderLines             NCCX_Sales_OrderLines                NONCLUSTERED COLUMNSTORE  0.000000000000000
Warehouse    StockItemTransactions  CCX_Warehouse_StockItemTransactions  CLUSTERED COLUMNSTORE     4.225346161484279

SQL Server Management Studio を使用してインデックスを管理する

インデックスを再構成または再構築する

1. オブジェクト エクスプローラーで、インデックスを再構成するテーブルが格納されているデータベースを展開します。
2. [テーブル] フォルダーを展開します。
3. インデックスを再構成するテーブルを展開します。
4. [インデックス] フォルダーを展開します。
5. 再構成するインデックスを右クリックし、 [再構成]を選択します。
6. [インデックスの再構成] ダイアログ ボックスで、[再構成するインデックス] グリッドに目的のインデックスが表示されていることを確認し、[OK] を選択します。
7. [ラージ オブジェクトの列データを圧縮する] チェック ボックスをオンにして、ラージ オブジェクト (LOB) データを含むページもすべて圧縮することを指定します。
8. [OK] を選択します。

テーブルのすべてのインデックスを再構成する

1. オブジェクト エクスプローラーで、インデックスを再構成するテーブルが格納されているデータベースを展開します。
2. [テーブル] フォルダーを展開します。
3. インデックスを再構成するテーブルを展開します。
4. [インデックス] フォルダーを右クリックし、 [すべて再構成]を選択します。
5. [インデックスの再構成] ダイアログ ボックスで、 [再構成するインデックス]に目的のインデックスが表示されていることを確認します。 [再構成するインデックス] グリッドからインデックスを削除するには、インデックスを選択し、Del キーを押します。
6. [ラージ オブジェクトの列データを圧縮する] チェック ボックスをオンにして、ラージ オブジェクト (LOB) データを含むページもすべて圧縮することを指定します。
7. [OK] を選択します。

Transact-SQL を使用してインデックスを管理する

インデックスを再構成する

次の例では、AdventureWorks2022 データベースの HumanResources.Employee テーブルの IX_Employee_OrganizationalLevel_OrganizationalNode インデックスが再構成されます。

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

次の例では、AdventureWorksDW2022 データベースの dbo.FactResellerSalesXL_CCI テーブルの IndFactResellerSalesXL_CCI 列ストア インデックスが再構成されます。 このコマンドは、すべての閉じている、または開いている行グループを列ストアに強制的に移動します

-- This command forces all closed and open row groups into columnstore.
ALTER INDEX IndFactResellerSalesXL_CCI
    ON FactResellerSalesXL_CCI
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

テーブルのすべてのインデックスを再構成する

次の例では、AdventureWorks2022 データベースの HumanResources.Employee テーブルのすべてのインデックスが再構成されます。

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

インデックスを再構築する

次の例では、AdventureWorks2022 データベースにある Employee テーブルで単一のインデックスを再構築します。

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

テーブルのすべてのインデックスを再構築する

次の例では、ALL キーワードを使って、AdventureWorks2022 データベース内のテーブルに関連付けられたすべてのインデックスが再構築されます。 3 つのオプションが指定されます。

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON)
;

詳細については、ALTER INDEX に関する記事をご覧ください。

関連するコンテンツ

• SQL Server と Azure SQL のインデックスのアーキテクチャとデザイン ガイド
• オンラインでのインデックス操作の実行
• ALTER INDEX (Transact-SQL)
• Adaptive Index Defrag
• CREATE STATISTICS (Transact-SQL)
• UPDATE STATISTICS (Transact-SQL)
• 列ストア インデックス - クエリ パフォーマンス
• 列ストアを使用したリアルタイム運用分析の概要
• 列ストア インデックス - データ ウェアハウス
• 列ストア インデックスと、行グループのマージ ポリシー