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

[アーティクル]
11/19/2024

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

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

Note

この記事は、Azure Synapse Analytics ワークスペースの専用 SQL プールには適用されません。 Azure Synapse Analytics の専用 SQL プールでのインデックスメンテナンスの詳細については、「Azure Synapse Analytics で専用 SQL プールテーブルをインデックスする」を参照してください。

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

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

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 のコストが高くなります。これは、クエリプランの選択に影響を与える可能性があります。たとえば、時間の経過と共にページ分割によってページ密度が低下すると、クエリは同じでもオプティマイザーによるプランの編集が異なり、パフォーマンスとリソース消費のプロファイルが変化する可能性があります。

ヒント

多くのワークロードでは、ページ密度を上げる方が、断片化を減らすより、パフォーマンスが大幅に向上します。

ページ密度を不必要に低下させないようにするため、たとえば先頭の列に連続しない GUID 値が含まれる変更頻度の高いインデックスのような、大量のページ分割が発生するインデックスの場合を除き、FILL FACTOR を 100 または 0 以外の値に設定することはお勧めしません。

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

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

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

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

列	説明
`avg_fragmentation_in_percent`	論理的な断片化 (インデックスが順序どおりになっていないページ)。
`avg_page_space_used_in_percent`	平均のページ密度。

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

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

列	説明
`total_rows`	行グループに物理的に格納されている行の数。圧縮された行グループの場合は、削除済みとマークされた行が含まれます。
`deleted_rows`	削除対象としてマークされている圧縮行グループに物理的に格納されている行の数。デルタストア内の行グループの場合は 0。

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

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

ヒント

行ストアと列ストアのどちらのインデックスでも、大量の行が削除または更新された後で、インデックスまたはヒープの断片化とページ密度を確認します。ヒープについては、更新が頻繁に行われる場合、転送レコードの急増を回避するため、定期的に断片化を確認します。ヒープの詳細については、「ヒープ (クラスター化インデックスなしのテーブル)」を参照してください。

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

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

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

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

Note

パーティションインデックスの場合は、すべてのパーティションまたはインデックスの 1 つのパーティションに対して、次のいずれかの方法を使用できます。

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

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

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

Note

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

ヒント

再構成操作をキャンセルした場合、またはそれ以外の理由で中断された場合は、その時点までに行われた進行状況がデータベースに保持されます。大きなインデックスを再構成するには、完了するまで何回も操作を開始および停止することができます。

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

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

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

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

Note

インデックスがオンラインで再構築されている間、インデックス付き列のデータのすべての変更で、インデックスの追加コピーを更新する必要があります。これにより、オンライン再構築中のデータ変更ステートメントのパフォーマンスが、わずかに低下する可能性があります。　

オンラインでの再開可能なインデックス操作が一時停止された場合、このパフォーマンスへの影響は、再開可能な操作が完了するか中止されるまで続きます。再開可能なインデックス操作を完了するつもりがない場合は、一時停止するのではなく中止してください。

ヒント

使用可能なリソースとワークロードのパターンによっては、ALTER INDEX REBUILD ステートメントで指定する MAXDOP の値を既定値より高くすると、CPU の使用率が高くなる代わりに、再構築の時間が短縮される可能性があります。

行ストアインデックスの場合、再構築を行うと、インデックスのすべてのレベルで断片化が解消され、指定または現在の 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) を使用した別のキーでのクラスター化インデックスの再作成など、テーブルでのクラスター化インデックスの作成
テーブルがヒープとして格納される原因となる、クラスター化インデックスの削除

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

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

重要

インデックスが存在するファイルグループがオフラインまたは読み取り専用の場合、インデックスを再構成または再構築することはできません。キーワード ALL を指定した場合、1 つ以上のインデックスがオフラインまたは読み取り専用のファイルグループにあると、ステートメントは失敗します。

インデックスの再構築が行われている間、物理メディアにはインデックスのコピーを 2 つ格納するのに十分な領域が必要です。再構築が完了すると、データベースエンジンによって元のインデックスが削除されます。

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

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

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

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

Note

SQL Server 2019 (15.x) 以降、タプルムーバーは、内部しきい値で指定した所定の期間存在していたと判断された小さい OPEN デルタストアグループを自動的に圧縮したり、大量の行が削除された COMPRESSED 行グループをマージしたりするバックグラウンドマージタスクによってサポートされています。これにより、時間の経過とともに、列ストアインデックスの品質が向上します。列ストアの用語と概念の詳細については、列ストアインデックス: 概要を参照してください。

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

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

パーティションテーブルの場合、断片化が一部のパーティションにのみ存在する場合は (たとえば、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 を使用してこれらの行グループを列ストアに強制的に変換した後、小さい圧縮された行グループを、より大きい圧縮された行グループに結合できます。再構成操作では、削除対象としてマークされた行も、列ストアから削除されます。

Note

Management Studio を使用して列ストアインデックスを再構成すると、圧縮された行グループが結合されますが、すべての行グループが列ストアに強制的に圧縮されることはありません。閉じられた行グループは圧縮されますが、開かれた行グループは列ストアに圧縮されません。すべての行グループを強制的に圧縮するには、COMPRESS_ALL_ROW_GROUPS = ON が含まれる Transact-SQL 例を使用します。

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

インデックスの再構成または再構築によって実行されるインデックスのメンテナンスでは、リソースが大量に消費されます。これにより、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 でデータベースのファイル領域を管理する

ヒント

Azure SQL Database と Azure SQL Managed Instance のワークロードのためにインデックスのメンテナンスが必要であると判断した場合は、インデックスを再構成するか、オンラインでのインデックスの再構築を使用する必要があります。これにより、インデックスの再構築中でも、クエリのワークロードはテーブルにアクセスできます。

さらに、操作を再開可能にすることで、データベースの計画的または計画外のフェールオーバーによって中断された場合に、最初から再開することを回避できます。インデックスが大きい場合は、再開可能なインデックス操作を使用することが特に重要です。

ヒント

オフラインインデックス操作は、通常、オンライン操作より早く完了します。シーケンシャル ETL プロセスの一部としてデータをステージングテーブルに読み込んだ後など、操作中にクエリによってテーブルがアクセスされない場合は、これを使用する必要があります。

制限事項と制約事項

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 オプションを使用してクラスター化列ストアインデックスを再構築できます。

警告

固定されていないインデックスをパーティションが 1, 000 個以上あるテーブルに作成または再構築することは可能ですが、サポートされていません。このような操作を行うと、操作中にパフォーマンスが低下したりメモリが過度に消費される可能性があります。パーティションの数が 1,000 個を超えた場合は、固定されたインデックスのみを使用することをお勧めします。

統計に関する制限

インデックスが作成または再構築されるとき、テーブル内のすべての行がスキャンされて、統計が作成または更新されます。これは、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 を使用してインデックスを管理する

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

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

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

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

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

Note

Transact-SQL を使用してインデックスを再構築または再構成する他の例については、「ALTER INDEX の例 - 行ストアインデックス」および「ALTER INDEX の例 - 列ストアインデックス」を参照してください。

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

次の例では、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 に関する記事をご覧ください。

次の方法で共有

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

制限事項と制約事項

統計に関する制限

例

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

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

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

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

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

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

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

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

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

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

フィードバック

その他のリソース

次の方法で共有

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

制限事項と制約事項

統計に関する制限

例

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

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

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

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

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

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

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

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

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

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

関連するコンテンツ

フィードバック

その他のリソース

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

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

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

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

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

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