インデックスの再編成と再構築
SQL Server データベース エンジンでは、基になるデータに対して挿入、更新、または削除の各操作が行われるたびに、インデックスが自動的にメンテナンスされます。このような変更が長期にわたると、インデックス内の情報がデータベース内に散在 (断片化) することになります。インデックスに、キー値に基づく論理順序とデータ ファイル内の物理順序が一致しないページが存在すると、断片化が発生します。インデックスが大量に断片化されると、クエリのパフォーマンスが低下し、アプリケーションの応答が遅くなる場合があります。詳細については、Microsoft Web サイトを参照してください。
インデックスを再編成するか、インデックスを再構築することにより、インデックスの断片化を解消できます。パーティション構成に基づいて構築されたパーティション インデックスでは、完全なインデックスまたはインデックスの 1 つのパーティションで、再編成または再構成のいずれかを実行できます。
断片化の検出
断片化を解消する方法を決める最初の手順は、断片化の程度を判断するためにインデックスを分析することです。システム関数 sys.dm_db_index_physical_stats を使用して、特定のインデックス、テーブルやインデックス付きビュー上のすべてのインデックス、データベース内のすべてのインデックス、またはすべてのデータベース内のすべてのインデックスの断片化を検出できます。パーティション インデックスの場合は、sys.dm_db_index_physical_stats でもパーティションごとの断片化情報が提供されます。
sys.dm_db_index_physical_stats 関数から返される結果セットに含まれる列を次に示します。
列 |
説明 |
|---|---|
avg_fragmentation_in_percent |
論理的な断片化 (インデックス内で順序が乱れたページ) の割合。 |
fragment_count |
インデックス内の断片化 (物理的に連続したリーフ ページ) の数。 |
avg_fragment_size_in_pages |
インデックス内の 1 つの断片化内の平均ページ数。 |
断片化の程度がわかったら、次の表を使用して、断片化を解消するための最適な方法を決定します。
avg_fragmentation_in_percent 値 |
断片化解消ステートメント |
|---|---|
5 ～ 30% |
ALTER INDEX REORGANIZE |
> 30% |
ALTER INDEX REBUILD WITH (ONLINE = ON)* |
* インデックスの再構築はオンラインでもオフラインでも実行できます。インデックスの再構成は、常にオンラインで実行されます。再編成オプションと同様の可用性を実現するには、インデックスをオンラインで再構築してください。
これらの値は、ALTER INDEX REORGANIZE と ALTER INDEX REBUILD の使い分けの大まかな目安となります。ただし、実際の値は状況によって変わります。それぞれの環境で実際に試して最適なしきい値を特定することが重要です。
断片化のレベルが非常に低い場合 (5% 未満) は、これらのコマンドのいずれも使用しないでください。インデックスの再構成や再構築には、ほとんどの場合、そのようなわずかな断片化を解消するには見合わないコストがかかります。
.gif "注意") 注意 |
|---|
一般に、小さなインデックスの断片化は制御不能である場合がほとんどです。小さなインデックスのページは、混合エクステントに格納されます。混合エクステントは最大 8 つのオブジェクトで共有されるため、インデックスを再構成または再構築しても、小さなインデックスの断片化は解消されない場合があります。混合エクステントの詳細については、「ページとエクステントについて」を参照してください。 |
例
次の例は、sys.dm_db_index_physical_stats 動的管理関数を使用して、Production.Product テーブルのすべてのインデックスの平均断片化を取得します。上記の表から、PK_Product_ProductID を再編成し、他のインデックスを再構築することが推奨の解決策であることがわかります。
USE AdventureWorks;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO
このステートメントでは、次のような結果セットが返されます。
index_id name avg_fragmentation_in_percent
----------- --------------------------- ----------------------------
1 PK_Product_ProductID 15.076923076923077
2 AK_Product_ProductNumber 50.0
3 AK_Product_Name 66.666666666666657
4 AK_Product_rowguid 50.0
(4 row(s) affected)
インデックスの再編成
1 つ以上のインデックスを再編成するには、REORGANIZE 句を指定した ALTER INDEX ステートメントを使用します。このステートメントは、DBCC INDEXDEFRAG ステートメントに置き換わるものです。パーティション インデックスの 1 つのパーティションを再編成するには、ALTER INDEX の PARTITION 句を使用します。
インデックスの再編成により、テーブルやビューの、リーフレベルのクラスタ化インデックスと非クラスタ化インデックスの断片化が解消されます。これは、リーフ ノードの論理順序 (左から右) と一致するように、リーフレベルのページを物理的に並べ替えることによって行われます。ページの順序が一致すると、インデックス スキャンのパフォーマンスが向上します。インデックスに割り当てられた既存のページ内でインデックスが再編成されます。新しいページは割り当てられません。インデックスが複数のファイルにまたがる場合は、ファイルが 1 つずつ再編成されます。ページがファイル間を移動することはありません。
再構成により、インデックス ページの圧縮も行われます。この圧縮によって作成される空のページは、使用できるディスク領域を増やすために削除されます。圧縮は、sys.indexes カタログ ビューの Fill Factor 値に基づいて行われます。
再編成プロセスでは、システム リソースの使用が最小限に抑えられます。また、再編成は自動的にオンラインで実行されます。このプロセスでは、ブロッキング ロックは長時間保持されません。したがって、実行中のクエリまたは更新はブロックされません。
インデックスの断片化が多くない場合は、インデックスを再編成します。断片化のガイドラインについては、前述の表を参照してください。しかし、インデックスが大量に断片化されている場合は、インデックスを再構築することで、適切な結果が得られます。
ラージ オブジェクト データ型の圧縮
既定では、インデックスの再編成時には、1 つ以上のインデックスを再編成する以外に、クラスタ化インデックスや基になるテーブルに含まれるラージ オブジェクト データ型 (LOB) が圧縮されます。image、text、ntext、varchar(max)、nvarchar(max)、varbinary(max)、および xml の各データ型が、ラージ オブジェクト データ型です。このデータを圧縮すると、ディスク領域を効率的に使用できます。
特定のクラスタ化インデックスを再編成すると、クラスタ化インデックスのリーフ レベル (データ行) に含まれているすべての LOB 列が圧縮されます。
非クラスタ化インデックスを再編成すると、インデックス内の非キー (付加) 列である、すべての LOB 列が圧縮されます。
ALL を指定すると、指定したテーブルやビューに関連付けられたすべてのインデックスが再編成され、クラスタ化インデックス、基になるテーブル、または付加列非クラスタ化インデックスに関連付けられたすべての LOB 列が圧縮されます。
LOB 列が存在しない場合、LOB_COMPACTION 句は無視されます。
インデックスの再構築
インデックスを再構築すると、既存のインデックスが削除され、新しいインデックスが作成されます。インデックスを再構築すると、断片化が解消され、指定した Fill Factor 設定または既存の Fill Factor 設定を使用してページを圧縮することにより、ディスク領域が再利用されます。また、必要に応じて新しいページが割り当てられ、インデックス行が連続するページに並べ替えられます。これにより、要求されたデータを取得するために必要なページ読み取り数が減少するため、ディスク パフォーマンスが向上します。
次の方法を使用して、クラスタ化インデックスと非クラスタ化インデックスを再構築できます。
REBUILD 句を指定した ALTER INDEX。このステートメントは、DBCC DBREINDEX ステートメントに置き換わるものです。
DROP_EXISTING 句を指定した CREATE INDEX。
どちらの方法でも同じ機能が実行されますが、次の表に示すような考慮すべき利点と欠点があります。
機能 |
ALTER INDEX REBUILD |
CREATE INDEX WITH DROP_EXISTING |
|---|---|---|
キー列の追加や削除、列順序の変更、または列の並べ替え順の変更による、インデックス定義の変更。* |
不可 |
可** |
インデックス オプションの設定または変更。 |
可 |
可 |
1 つのトランザクションでの複数インデックスの再構築。 |
可 |
不可 |
実行中のクエリまたは更新をブロックせずに、ほとんどの種類のインデックスをオンラインで再構築。 |
可 |
可 |
パーティション インデックスのパーティション再分割。 |
不可 |
可 |
インデックスの他のファイル グループへの移動。 |
不可 |
可 |
一時ディスク領域の追加要求。 |
可 |
可 |
クラスタ化インデックスの再構築による、関連付けられた非クラスタ化インデックスの再構築。 |
不可 キーワード ALL が指定されている場合を除く。 |
不可 インデックス定義が変更される場合を除く。 |
制約を削除および再作成せずに、PRIMARY KEY 制約と UNIQUE 制約を設定するインデックスの再構築。 |
可 |
可 |
1 つのインデックス パーティションの再構築。 |
可 |
不可 |
* インデックス定義に CLUSTERED を指定すると、非クラスタ化インデックスをクラスタ化インデックスに変換できます。この操作は、ONLINE オプションの設定を OFF にして実行する必要があります。ONLINE 設定にかかわらず、クラスタ化インデックスから非クラスタ化インデックスへの変換はサポートされていません。
** 同じ名前、列、および並べ替え順序を使用してインデックスが再作成されると、並べ替え操作が省略されることがあります。再構築操作では、インデックスを構築しながら、行が並べ替えられているかがチェックされます。
また、DROP INDEX ステートメントを使用してインデックスを削除してから、別の CREATE INDEX ステートメントを使用してインデックスを再作成することにより、インデックスを再構築することもできます。別のステートメントとしてこれらの操作を実行することにはいくつか欠点があり、お勧めしません。
再構築の操作時のディスク領域を節減するための非クラスタ化インデックスの無効化
非クラスタ化インデックスが無効になっていると、インデックス データ行は削除されますが、インデックス定義はメタデータに残ります。再構築するとインデックスが有効になります。非クラスタ化インデックスが無効になっていないと、再構築操作では、古いインデックスと新しいインデックスの両方を格納するために十分な一時的なディスク領域が必要になります。ただし、別のトランザクションで非クラスタ化インデックスを無効にし再構築することにより、インデックスを無効にしたことで使用可能になったディスク領域を、その後の再構築操作や他の操作で使用できるようになります。必要な領域は、並べ替え操作で使用するための一時ディスク領域のみです。この一時ディスク領域は、通常、インデックス サイズの 20% 程度です。非クラスタ化インデックスが主キーにある場合、すべてのアクティブな参照側の FOREIGN KEY 制約が自動的に無効になります。このような制約は、インデックスが再構築された後に、手動で有効にする必要があります。詳細については、「インデックスの無効化」および「インデックスと制約を有効にするためのガイドライン」を参照してください。
大きなインデックスの再構築
128 エクステントを超えるインデックスは、論理フェーズと物理フェーズの 2 つの独立したフェーズで再構築されます。論理フェーズでは、インデックスによって使用されている既存のアロケーション ユニットが、割り当て解除に設定されます。その後、データ行がコピーされ、並べ替えられてから、再構築されたインデックスを格納するために作成された新しいアロケーション ユニットに移動されます。物理フェーズでは、バックグラウンドで行われる短いトランザクションで、以前に割り当て解除に設定されたアロケーション ユニットが物理的に削除され、ロックの必要はあまり多くありません。詳細については、「ラージ オブジェクトの削除と再構築」を参照してください。
インデックス オプションの設定
インデックスを再編成するときは、インデックス オプションを指定できません。しかし、インデックスを再構築するときには、ALTER INDEX REBUILD または CREATE INDEX WITH DROP_EXISTING のいずれかを使用して、次のインデックス オプションを設定できます。
PAD_INDEX |
DROP_EXISTING (CREATE INDEX のみ) |
FILLFACTOR |
ONLINE |
SORT_IN_TEMPDB |
ALLOW_ROW_LOCKS |
IGNORE_DUP_KEY |
ALLOW_PAGE_LOCKS |
STATISTICS_NORECOMPUTE |
MAXDOP |
| 注意 |
|---|
並べ替え操作が必要ない場合、または並べ替えをメモリ内で実行できる場合、SORT_IN_TEMPDB オプションは無視されます。 |
また、ALTER INDEX ステートメントの SET 句により、インデックスを再構築しないで、次のインデックス オプションを設定できます。
ALLOW_PAGE_LOCKS |
IGNORE_DUP_KEY |
ALLOW_ROW_LOCKS |
STATISTICS_NORECOMPUTE |
詳細については、「インデックス オプションの設定」を参照してください。
インデックスを再構築または再編成するには
インデックスを削除してから再作成することにより、1 回の操作でインデックスを再構築するには
例
A. インデックスの再構築
次の例では、1 つのインデックスを再構築します。
USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO
B. テーブル上のすべてのインデックスの再構築とオプションの指定
次の例では、キーワード ALL を指定します。この場合、テーブルに関連付けられているすべてのインデックスが再構築されます。3 つのオプションが指定されています。
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO
C. LOB 圧縮を伴うインデックスの再編成
次の例では、1 つのクラスタ化インデックスを再編成します。インデックスのリーフ レベルに LOB データ型が含まれているので、このステートメントではラージ オブジェクト データが含まれているすべてのページも圧縮されます。既定値が ON なので、WITH (LOB_Compaction) オプションを指定する必要はありません。
USE AdventureWorks;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO