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

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

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

このトピックについて

作業を開始する準備:

断片化の検出

制限事項と制約事項

セキュリティ
インデックスの断片化を確認するには、次を使用します。

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関数によって返される結果セットには、次の列が含まれます。

コラム	説明
断片化の平均割合	論理断片化の割合 (インデックス内の順序が乱れたページ)。
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 個のオブジェクトによって共有されるため、再構成または再構築後に小さなインデックスの断片化が減らない可能性があります。

インデックスのデフラグメンテーションに関する考慮事項

特定の条件下では、クラスター化インデックスレコードに含まれる物理識別子または論理識別子を変更する必要がある場合、クラスター化インデックスを再構築すると、クラスタリングキーを参照する非クラスター化インデックスが自動的に再構築されます。

すべての非クラスター化インデックスをテーブルに対して自動的に再構築するシナリオ:

テーブルにクラスター化インデックスを作成する
クラスター化インデックスを削除し、テーブルをヒープとして格納する
列を含めるか除外するようにクラスタリングキーを変更する

すべての非クラスター化インデックスをテーブルに自動的に再構築する必要がないシナリオ:

一意のクラスター化インデックスの再構築
一意でないクラスター化インデックスの再構築
パーティション構成をクラスター化インデックスに適用したり、クラスター化インデックスを別のファイルグループに移動したりするなど、インデックススキーマを変更する

制限事項と制約条件

エクステントが 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.

パーティション数が 1,000 を超えるテーブルで、アラインされていないインデックスを作成および再構築することは可能ですが、推奨されません。このような操作を行うと、操作中にパフォーマンスが低下したりメモリが過度に消費される可能性があります。

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

安全

権限

テーブルまたはビューに対する 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] をクリックします。

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

オブジェクトエクスプローラーで、インデックスを再構成するテーブルを含むデータベースを展開します。
[テーブル] フォルダーを展開します。
インデックスを再構成するテーブルを展開します。
[インデックス] フォルダーを右クリックし、[すべて再構成] を選択します。
[ インデックスの再構成 ] ダイアログボックスで、再構成するインデックスに正しい インデックスがあることを確認します。再構成するインデックスグリッドからインデックスを削除するには、インデックスを選択し、Delete キーを押します。
[ ラージオブジェクト列データの圧縮 ] チェックボックスをオンにすると、ラージオブジェクト (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 テーブルで 1 つのインデックスを再構築します。
```
USE AdventureWorks2012;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO
```

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

オブジェクトエクスプローラーで、データベースエンジンのインスタンスに接続します。
標準バーで [新しいクエリ] をクリックします。
次の例をコピーしてクエリに貼り付けます。この例では、キーワード ALLを指定します。これにより、テーブルに関連付けられているすべてのインデックスが再構築されます。 3 つのオプションが指定されています。
```
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)」を参照してください。

こちらもご覧ください

Microsoft SQL Server 2000 インデックスの最適化のベストプラクティス

Last updated on 2017-06-13

次の方法で共有

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

開始する前に

断片化の検出

インデックスのデフラグメンテーションに関する考慮事項

制限事項と制約条件

安全

権限

SQL Server Management Studio の使用

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

Transact-SQL の使用

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

SQL Server Management Studio の使用

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

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

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

Transact-SQL の使用

断片化されたインデックスを整理するには

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

断片化されたインデックスを再構築するには

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

こちらもご覧ください

その他のリソース