インデックスの再編成と再構築

断片化の検出

断片化を解消する方法を決める最初の手順は、断片化の程度を判断するためにインデックスを分析することです。システム関数 sys.dm_db_index_physical_stats を使用して、特定のインデックス、テーブルやインデックス付きビュー上のすべてのインデックス、データベース内のすべてのインデックス、またはすべてのデータベース内のすべてのインデックスの断片化を検出できます。パーティション インデックスの場合は、sys.dm_db_index_physical_stats でもパーティションごとの断片化情報が提供されます。

sys.dm_db_index_physical_stats 関数から返される結果セットに含まれる列を次に示します。

断片化の程度がわかったら、次の表を使用して、断片化を解消するための最適な方法を決定します。

* インデックスの再構築はオンラインでもオフラインでも実行できます。インデックスの再構成は、常にオンラインで実行されます。再編成オプションと同様の可用性を実現するには、インデックスをオンラインで再構築してください。

これらの値は、ALTER INDEX REORGANIZE と ALTER INDEX REBUILD の使い分けの大まかな目安となります。ただし、実際の値は状況によって変わります。それぞれの環境で実際に試して最適なしきい値を特定することが重要です。

断片化のレベルが非常に低い場合 (5% 未満) は、これらのコマンドのいずれも使用しないでください。インデックスの再構成や再構築には、ほとんどの場合、そのようなわずかな断片化を解消するには見合わないコストがかかります。

注意
一般に、小さなインデックスの断片化は制御不能である場合がほとんどです。小さなインデックスのページは、混合エクステントに格納されます。混合エクステントは最大 8 つのオブジェクトで共有されるため、インデックスを再構成または再構築しても、小さなインデックスの断片化は解消されない場合があります。混合エクステントの詳細については、「ページとエクステントについて」を参照してください。

例

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 値に基づいて行われます。

再編成プロセスでは、システム リソースの使用が最小限に抑えられます。また、再編成は自動的にオンラインで実行されます。このプロセスでは、ブロッキング ロックは長時間保持されません。したがって、実行中のクエリまたは更新はブロックされません。

インデックスの断片化が多くない場合は、インデックスを再編成します。断片化のガイドラインについては、前述の表を参照してください。しかし、インデックスが大量に断片化されている場合は、インデックスを再構築することで、適切な結果が得られます。

ラージ オブジェクト データ型の圧縮

インデックスの再構築

インデックスを再構築すると、既存のインデックスが削除され、新しいインデックスが作成されます。インデックスを再構築すると、断片化が解消され、指定した Fill Factor 設定または既存の Fill Factor 設定を使用してページを圧縮することにより、ディスク領域が再利用されます。また、必要に応じて新しいページが割り当てられ、インデックス行が連続するページに並べ替えられます。これにより、要求されたデータを取得するために必要なページ読み取り数が減少するため、ディスク パフォーマンスが向上します。

次の方法を使用して、クラスタ化インデックスと非クラスタ化インデックスを再構築できます。

• REBUILD 句を指定した ALTER INDEX。このステートメントは、DBCC DBREINDEX ステートメントに置き換わるものです。

• DROP_EXISTING 句を指定した CREATE INDEX。

どちらの方法でも同じ機能が実行されますが、次の表に示すような考慮すべき利点と欠点があります。

* インデックス定義に CLUSTERED を指定すると、非クラスタ化インデックスをクラスタ化インデックスに変換できます。この操作は、ONLINE オプションの設定を OFF にして実行する必要があります。ONLINE 設定にかかわらず、クラスタ化インデックスから非クラスタ化インデックスへの変換はサポートされていません。

** 同じ名前、列、および並べ替え順序を使用してインデックスが再作成されると、並べ替え操作が省略されることがあります。再構築操作では、インデックスを構築しながら、行が並べ替えられているかがチェックされます。

また、DROP INDEX ステートメントを使用してインデックスを削除してから、別の CREATE INDEX ステートメントを使用してインデックスを再作成することにより、インデックスを再構築することもできます。別のステートメントとしてこれらの操作を実行することにはいくつか欠点があり、お勧めしません。

再構築の操作時のディスク領域を節減するための非クラスタ化インデックスの無効化

非クラスタ化インデックスが無効になっていると、インデックス データ行は削除されますが、インデックス定義はメタデータに残ります。再構築するとインデックスが有効になります。非クラスタ化インデックスが無効になっていないと、再構築操作では、古いインデックスと新しいインデックスの両方を格納するために十分な一時的なディスク領域が必要になります。ただし、別のトランザクションで非クラスタ化インデックスを無効にし再構築することにより、インデックスを無効にしたことで使用可能になったディスク領域を、その後の再構築操作や他の操作で使用できるようになります。必要な領域は、並べ替え操作で使用するための一時ディスク領域のみです。この一時ディスク領域は、通常、インデックス サイズの 20% 程度です。非クラスタ化インデックスが主キーにある場合、すべてのアクティブな参照側の FOREIGN KEY 制約が自動的に無効になります。このような制約は、インデックスが再構築された後に、手動で有効にする必要があります。詳細については、「インデックスの無効化」および「インデックスと制約を有効にするためのガイドライン」を参照してください。

大きなインデックスの再構築

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

インデックス オプションの設定

インデックスを再編成するときは、インデックス オプションを指定できません。しかし、インデックスを再構築するときには、ALTER INDEX REBUILD または CREATE INDEX WITH DROP_EXISTING のいずれかを使用して、次のインデックス オプションを設定できます。

注意
並べ替え操作が必要ない場合、または並べ替えをメモリ内で実行できる場合、SORT_IN_TEMPDB オプションは無視されます。

また、ALTER INDEX ステートメントの SET 句により、インデックスを再構築しないで、次のインデックス オプションを設定できます。

詳細については、「インデックス オプションの設定」を参照してください。

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

ALTER INDEX (Transact-SQL)

インデックスを削除してから再作成することにより、1 回の操作でインデックスを再構築するには

CREATE INDEX (Transact-SQL)

例

A. インデックスの再構築

USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO

B. テーブル上のすべてのインデックスの再構築とオプションの指定

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

C. LOB 圧縮を伴うインデックスの再編成

USE AdventureWorks;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

関連項目

参照

概念

その他の技術情報