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

[アーティクル]
06/25/2013

このトピックでは、SQL Server 2012 で SQL Server Management Studio または Transact-SQL を使用して、断片化したインデックスを再構成または再構築する方法について説明します。 SQL Server データベースエンジンでは、基になるデータに対して挿入、更新、または削除の各操作が行われるたびに、インデックスが自動的にメンテナンスされます。このような変更が長期にわたると、インデックス内の情報がデータベース内に散在 (断片化) することになります。インデックスに、キー値に基づく論理順序とデータファイル内の物理順序が一致しないページが存在すると、断片化が発生します。インデックスが大量に断片化されると、クエリのパフォーマンスが低下し、アプリケーションの応答が遅くなる場合があります。

インデックスを再構成するか、インデックスを再構築することにより、インデックスの断片化を解消できます。パーティション構成に基づいて構築されたパーティションインデックスでは、完全なインデックスまたはインデックスの 1 つのパーティションで、再構成または再構築のいずれかを実行できます。インデックスの再構築では、インデックスを削除し再作成します。この操作では、断片化をなくし、指定されているか既に存在する FILL FACTOR 設定に基づいてページを圧縮することによりディスク領域を取り戻した後、連続するページにインデックス行を再び並べ替えます。 ALL を指定した場合、テーブル上のすべてのインデックスが、1 回のトランザクションで削除され再構築されます。インデックスの再構成では、最小のシステムリソースが使用されます。この操作では、リーフレベルページをリーフノードの論理順序 (左から右) に合わせて物理的に並べ替えることにより、テーブルやビュー上にあるクラスター化および非クラスター化インデックスのリーフレベルをデフラグします。再構成により、インデックスページの圧縮も行われます。圧縮は既存の FILL FACTOR 値に基づいて行われます。

このトピックの内容

作業を開始する準備:

断片化の検出

制限事項と制約事項

セキュリティ
インデックスの断片化を確認するために使用するもの:

SQL Server Management Studio

Transact-SQL
インデックスを再構成または再構築するために使用するもの:

SQL Server Management Studio

Transact-SQL

作業を開始する準備

断片化の検出

断片化を解消する方法を決める最初の手順は、断片化の程度を判断するためにインデックスを分析することです。システム関数 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% 未満) は、これらのコマンドのいずれも使用しないでください。インデックスの再構成や再構築には、ほとんどの場合、そのようなわずかな断片化を解消するには見合わないコストがかかります。

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

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

制限事項と制約事項

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

セキュリティ

権限

テーブルまたはビューに対する ALTER 権限が必要です。実行するには、固定サーバーロール sysadmin または、固定データベースロール db_ddladmin および db_owner のメンバーである必要があります。

[トップに戻る] リンクで使用される矢印アイコン [先頭に戻る]

SQL Server Management Studio の使用

インデックスの断片化を確認するには

オブジェクトエクスプローラーで、インデックスの断片化を確認するテーブルが格納されているデータベースを展開します。
[テーブル] フォルダーを展開します。
インデックスの断片化を確認するテーブルを展開します。
[インデックス] フォルダーを展開します。
断片化を確認するインデックスを右クリックし、[プロパティ] を選択します。
[ページの選択] の [断片化] を選択します。

[断片化] ページでは、次の情報を取得できます。
- [ページのゆとり]
  インデックスページのゆとりの平均をパーセントで示します。 100% は、インデックスページが完全にいっぱいであることを示します。 50% は、平均して各インデックスページが半分埋まっていることを示します。
- [全体の断片化]
  論理的な断片化の割合です。この値は、順番に格納されないインデックス内のページ数を示します。
- [行の平均サイズ]
  リーフレベルの行の平均サイズです。
- [奥行]
  インデックス内のレベルの数 (リーフレベルを含む) です。
- [転送されたレコード]
  別のデータの場所への転送ポインターを持つ、ヒープ内の転送されたレコード数です (この状態は、更新中に、新しい行を格納できる十分なスペースが元の場所にない場合に発生します)。
- [非実体行]
  削除のマークが付けられている行の中で、まだ削除されていない行の数です。これらの行は、サーバーが使用中でないときにクリーンアップスレッドによって削除されます。この値には、スナップショット分離トランザクションが未完了であるために保持される行は含まれません。
- [インデックスの種類]
  インデックスの種類です。指定できる値は、[クラスター化]、[非クラスター化]、および [プライマリ XML] です。テーブルは、ヒープ (インデックスなし) としても格納できますが、その場合は、この [インデックスのプロパティ] ページは開きません。
- [リーフレベルの行]
  リールレベルの行の数です。
- [行の最大サイズ]
  リーフレベルの最大行サイズです。
- [行の最小サイズ]
  リーフレベルの最小行サイズです。
- [ページ数]
  データページ数の合計です。
- [パーティション ID]
  インデックスを含む B ツリーのパーティション ID です。
- [バージョンの非実体行]
  スナップショットトランザクションが未完了であるために保持されるゴーストレコードの数です。

[トップに戻る] リンクで使用される矢印アイコン [先頭に戻る]

Transact-SQL の使用

インデックスの断片化を確認するには

オブジェクトエクスプローラーで、データベースエンジンのインスタンスに接続します。
[標準] ツールバーの [新しいクエリ] をクリックします。

次の例をコピーしてクエリウィンドウに貼り付け、[実行] をクリックします。

USE AdventureWorks2012;
GO
-- Find the average fragmentation percentage of all indexes
-- in the HumanResources.Employee table. 
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), OBJECT_ID(N'HumanResources.Employee'), 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_Employee_BusinessEntityID                          0
2           IX_Employee_OrganizationalNode                        0
3           IX_Employee_OrganizationalLevel_OrganizationalNode    0
5           AK_Employee_LoginID                                   66.6666666666667
6           AK_Employee_NationalIDNumber                          50
7           AK_Employee_rowguid                                   0

(6 row(s) affected)

詳細については、「sys.dm_db_index_physical_stats (Transact-SQL)」を参照してください。

[トップに戻る] リンクで使用される矢印アイコン [先頭に戻る]

SQL Server Management Studio の使用

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

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

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

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

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

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

[トップに戻る] リンクで使用される矢印アイコン [先頭に戻る]

Transact-SQL の使用

デフラグされたインデックスを再構成するには

オブジェクトエクスプローラーで、データベースエンジンのインスタンスに接続します。
[標準] ツールバーの [新しいクエリ] をクリックします。

次の例をコピーしてクエリウィンドウに貼り付け、[実行] をクリックします。

USE AdventureWorks2012; 
GO
-- Reorganize the IX_Employee_OrganizationalLevel_OrganizationalNode index on the HumanResources.Employee table. 

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee
REORGANIZE ; 
GO

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

オブジェクトエクスプローラーで、データベースエンジンのインスタンスに接続します。
[標準] ツールバーの [新しいクエリ] をクリックします。

次の例をコピーしてクエリウィンドウに貼り付け、[実行] をクリックします。

USE AdventureWorks2012; 
GO
-- Reorganize all indexes on the HumanResources.Employee table.
ALTER INDEX ALL ON HumanResources.Employee
REORGANIZE ; 
GO

デフラグされたインデックスを再構築するには

オブジェクトエクスプローラーで、データベースエンジンのインスタンスに接続します。
[標準] ツールバーの [新しいクエリ] をクリックします。
次の例をコピーしてクエリウィンドウに貼り付け、[実行] をクリックします。次の例では、Employee テーブルで単一のインデックスを再構築します。
```
```Transact-SQL
USE AdventureWorks2012;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO
```
```

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

オブジェクトエクスプローラーで、データベースエンジンのインスタンスに接続します。
[標準] ツールバーの [新しいクエリ] をクリックします。
次の例をコピーしてクエリウィンドウに貼り付けます。この例では、キーワード ALL を指定しています。この場合、テーブルに関連付けられているすべてのインデックスが再構築されます。 3 つのオプションが指定されています。
```
```Transact-SQL
USE AdventureWorks2012;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO
```
```

詳細については、「ALTER INDEX (Transact-SQL)」を参照してください。

[トップに戻る] リンクで使用される矢印アイコン [先頭に戻る]

次の方法で共有

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

作業を開始する準備

断片化の検出

制限事項と制約事項

セキュリティ

権限

SQL Server Management Studio の使用

インデックスの断片化を確認するには

Transact-SQL の使用

インデックスの断片化を確認するには

SQL Server Management Studio の使用

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

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

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

Transact-SQL の使用

デフラグされたインデックスを再構成するには

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

デフラグされたインデックスを再構築するには

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

関連項目

その他の技術情報

その他のリソース