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

適用対象: SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAnalytics Platform System (PDW)Microsoft Fabric SQL Database

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

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

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

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

  注

  ドキュメントでは、一般にインデックスに関して 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(total_stored_deleted_rows, 0)) / NULLIF(total_rows, 0)

非クラスター化列ストア インデックスの物理的に格納された削除された行の合計数を確認するには、deleted_rowsの sys.dm_db_column_store_row_group_physical_stats 列の値を、内部オブジェクト型のrowsと同じオブジェクト、インデックス、パーティションのsys.internal_partitionsのCOLUMN_STORE_DELETE_BUFFER列の値に追加します。 例については、「 列ストア インデックスの断片化を確認する」を参照してください。

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

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

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

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

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

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

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

SQL Server 2019 (15.x)、Azure SQL Database、および Azure SQL Managed Instance 以降では、組ムーバーは、内部のしきい値によって一定時間存在していた小さい開いているデルタ行グループを自動的に圧縮したり、多数の行が削除された圧縮された行グループをマージしたりするバックグラウンド マージ タスクによって役立ちます。 これにより、時間の経過とともに、列ストア インデックスの品質が向上します。 ほとんどの場合、これにより ALTER INDEX ... REORGANIZE コマンドを発行する必要がなくなります。

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

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

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

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

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

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

  ヒント

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

インデックス再構築を使用してデータの破損を回復する

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

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

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

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

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

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

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

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

次のシナリオでは、同じテーブルのすべての行ストア非クラスター化インデックスが自動的に再構築されるわけではありません。

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

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

通常、小さな行ストア インデックスを再構築または再構成しても、断片化は軽減されません。 SQL Server 2014 (12.x) またはそれ以前の場合、SQL Server データベース エンジンは、混合エクステントを使用して領域を割り当てます。 そのため、小さいインデックスのページは混合エクステントに格納されることがあり、そのようなインデックスは暗黙的に断片化されます。 混合エクステントは最大 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 では、インデックスのメンテナンス期間を延長する代わりに、リソースの割り当てが制限された別の リソース ガバナー ワークロード グループでインデックス メンテナンスを実行することで、この影響を軽減できます。
• コストを削減するため、お客様は多くの場合、データベース、エラスティック プール、マネージド インスタンスを、最小限のリソース ヘッドルームでプロビジョニングします。 価格レベルは、アプリケーションのワークロードに十分なものが選択されます。 アプリケーションのパフォーマンスを低下させることなく、インデックスのメンテナンスによるリソース使用量の大幅な増加に対応するには、より多くのリソースをプロビジョニングし、コストを増やすことが必要になる場合がありますが、それでアプリケーションのパフォーマンスが向上するとは限りません。
• エラスティック プールでは、リソースはプール内のすべてのデータベース間で共有されます。 特定のデータベースがアイドル状態の場合でも、そのデータベースでインデックスのメンテナンスを実行すると、同じプール内の他のデータベースで同時に実行されているアプリケーションのワークロードに影響を与える可能性があります。 詳細については、「高密度エラスティック プールでのリソース管理」を参照してください。
• Azure SQL Database および Azure SQL Managed Instance で使用されるほとんどの種類のストレージでは、シーケンシャル I/O とランダム I/O のパフォーマンスに違いはありません。 これにより、インデックスの断片化がクエリのパフォーマンスに与える影響が軽減されます。
• 読み取りスケールアウトまたは 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 オプションを使用してクラスター化列ストア インデックスを再構築できます。

統計に関する制限

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

例

行ストア インデックスの断片化とページ密度を確認する

次の例では、現在のデータベース内のすべての行ストア インデックスの断片化と平均ページ密度を確認します。 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 に関する記事をご覧ください。

列ストア インデックスの断片化を確認する

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

WITH columnstore_row_group_partition
AS (SELECT object_id,
           index_id,
           partition_number,
           SUM(deleted_rows) AS partition_deleted_rows,
           SUM(total_rows) AS partition_total_rows
    FROM sys.dm_db_column_store_row_group_physical_stats
    WHERE state_desc = 'COMPRESSED'
    GROUP BY object_id, index_id, partition_number),
/* For nonclustered columnstore, include rows in the delete buffer */
 columnstore_internal_partition
AS (SELECT object_id,
           index_id,
           partition_number,
           SUM(rows) AS delete_buffer_rows
    FROM sys.internal_partitions
    WHERE internal_object_type_desc = 'COLUMN_STORE_DELETE_BUFFER'
    GROUP BY object_id, index_id, partition_number)
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,
       crgp.partition_number,
       100.0 * (ISNULL(crgp.partition_deleted_rows + ISNULL(cip.delete_buffer_rows, 0), 0)) / NULLIF (crgp.partition_total_rows, 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
     INNER JOIN columnstore_row_group_partition AS crgp
         ON i.object_id = crgp.object_id
        AND i.index_id = crgp.index_id
     LEFT OUTER JOIN columnstore_internal_partition AS cip
         ON i.object_id = cip.object_id
        AND i.index_id = cip.index_id
        AND crgp.partition_number = cip.partition_number
ORDER BY schema_name, object_name, index_name, partition_number, 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 を使用してインデックスを管理する

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

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

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

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

-- 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);

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

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

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

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

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

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

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

次の例では、AdventureWorks2022 キーワードを使って、ALL データベース内のテーブルに関連付けられたすべてのインデックスが再構築されます。 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)
• アダプティブ インデックスデフラグ
• CREATE STATISTICS (Transact-SQL)
• UPDATE STATISTICS (Transact-SQL)
• 列ストア インデックス - クエリのパフォーマンス
• リアルタイムの運用分析向けに列ストアインデックスの使用を始める
• データウェアハウジングにおける列ストアインデックス
• 列ストア インデックスと、行グループのマージ ポリシー