【SQLServer】インデックス再構築・再編成

2021年8月11日2022年12月24日

当ページのリンクには広告が含まれています。

データベースは適切なインデックスを設定することで、検索レスポンスを向上させることが出来ます。しかし、運用していくうちにデータの追加や更新・削除など発生することで、検索レスポンスが遅くなることがあります。

原因はインデックスの断片化です。

断片化とは、データの物理的な順序や論理的な順序がバラバラになることです。

インデックスの断面化を確認する方法

データベース内の断片化が10%以上発生しているテーブルを見る方法は以下のSQLを流してください。

	SELECT   QUOTENAME(S.name) AS SchemaName,
			  QUOTENAME(O.name) AS ObjectName,
			  QUOTENAME(I.name) AS IndexName,
			  PS.avg_fragmentation_in_percent
	FROM     sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') AS PS
				  INNER JOIN sys.objects AS O
					 ON PS.object_id = O.object_id
				  INNER JOIN sys.schemas AS S
					 ON O.schema_id = S.schema_id
				  INNER JOIN sys.indexes AS I
					 ON PS.object_id = I.object_id
						AND PS.index_id = I.index_id
	WHERE    avg_fragmentation_in_percent >= 10.0
			  AND PS.index_id > 0
	ORDER BY 1,4 DESC;

インデックスの断片化を解消する方法

インデックスの断片化を解消する方法としては大きく２つあります。再構築と再構成です。

再構築はオフラインとオンラインがあります。

以下のスクリプトを流した結果、ALTER文が生成されます。そのALTER文を実行すると断片化解消してくれます。

/*-------------------------------------------
再構築・再編成が必要なインデックスリスト取得

１．断片化対象リストを取得（１０％以上）
２．上記リストより３０％未満のものは再編成
３．上記リストより３０％以上のものは再構築
-------------------------------------------*/
DECLARE @IndexName NVARCHAR(128),
		@SchemaName NVARCHAR(128),
		@ObjectName NVARCHAR(128),
		@avg_fragmentation_in_percent NVARCHAR(128),
		@Sql NVARCHAR(128) = '';

DECLARE crDM CURSOR FOR
	SELECT   QUOTENAME(S.name) AS SchemaName,
			  QUOTENAME(O.name) AS ObjectName,
			  QUOTENAME(I.name) AS IndexName,
			  PS.avg_fragmentation_in_percent
	FROM     sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') AS PS
				  INNER JOIN sys.objects AS O
					 ON PS.object_id = O.object_id
				  INNER JOIN sys.schemas AS S
					 ON O.schema_id = S.schema_id
				  INNER JOIN sys.indexes AS I
					 ON PS.object_id = I.object_id
						AND PS.index_id = I.index_id
	WHERE    avg_fragmentation_in_percent >= 10.0
			  AND PS.index_id > 0
	ORDER BY 1,4 DESC;

OPEN crDM;

FETCH NEXT FROM crDM
INTO @SchemaName,@ObjectName,@IndexName,@avg_fragmentation_in_percent;

WHILE @@FETCH_STATUS = 0
IF @avg_fragmentation_in_percent < 30.0
	BEGIN

			  SET @Sql =  N'ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' + @ObjectName + N' REORGANIZE';
PRINT @Sql;
	FETCH NEXT FROM crDM
	INTO @SchemaName,@ObjectName,@IndexName,@avg_fragmentation_in_percent;

	END
ELSE
	BEGIN

			  SET @Sql = N'ALTER INDEX ' + @IndexName + N' ON ' + @SchemaName + N'.' + @ObjectName + N' REBUILD';
PRINT @Sql;
	FETCH NEXT FROM crDM
	INTO @SchemaName,@ObjectName,@IndexName,@avg_fragmentation_in_percent;

	END

CLOSE crDM;
DEALLOCATE crDM;

インデックスのオフライン再構築

インデックスのオフライン再構築は、インデックスを再作成し、古いインデックスを削除することで断片化を解消します。インデックス全体がロック状態になるので、再構築が完了するまでユーザは待ち状態となるので、注意が必要です。

ALTER INDEX インデックス名 ON テーブル名 REBUILD

インデックスのオンライン再構築

インデックスのオンライン再構築は、Enterpriseエディションのみ利用可能です。オフラインとは違い、再構築が完了するまでユーザは待ち状態になることはありません。

ALTER INDEX インデックス名 ON テーブル名 REBUILD WITH ONLINE = ON

インデックスの再構成

インデックスの再構成は、処理中でもインデックスを利用することは可能です。

ALTER INDEX インデックス名 ON テーブル名 REORGANIZE

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

インデックスの再構築と再構成の違いを解説します。

	再構築	再編成
トランザクション	一つのトランザクションでインデックスを完全に再作成	トランザクションが分散されていて、リーフページ間でインデックス行を移動させ断片化を解消
中断	トランザクションはロールバックされ、断片化した状態に戻る	既に完了しているトランザクションの結果は維持される
領域	新しいインデックスと古いインデックスが２つ存在する状態になるため、それらを格納する領域が必要	追加の領域は基本的に必要としない
利用制限	処理中はインデックスをロックするため、使用できない	処理中でもインデックスを利用することは可能