DO's&DONT's #7: やらない方がいいこと - インデックス再構築 (REBUILD) 後のデータファイル圧縮 (SHRINK)

[moematsuo]

インデックスの再構築 (ALTER INDEX REBUILD または DBCC DBREINDEX) を行う目的が、ページ密度を向上させて、データベースファイル内の使用領域サイズを小さくすることであれば、インデックス再構築後にデータベース圧縮を行っても、その目的は損なわれません。しかし、インデックスを再構築する目的が、論理断片化を解消し、検索パフォーマンスを向上させることであれば、インデックス再構築後にデータベース圧縮を行うと、その目的は達せられなくなります。

• ページ密度 (Page Density) : ページ内でデータが占める割合。100% の場合、それ以上そのページにはデータが入らない、空き領域がない状態。ただし、行サイズとページサイズの関係上、100% になることはほとんどない。(例 : 1 行 100 バイトの場合、1 ページには、8060 バイトのデータが入るため、ギッシリ詰め込んでも 8060%100=60 と 60 バイトの空きは必ずできてしまう。)
• 論理断片化 (Logical Fragmentation / Logical Scan Fragmentation) 率 : ページの物理的な順番と論理的なリンクが異なる割合。ページ番号はファイルの先頭から順に 0, 1, 2 ... と振られているが、論理的なページのリンクが 81 の次に 48 など、ページ番号順になっていない割合。

なぜ？

インデックスを再構築すると、インデックスは再作成され、各インデックスページは FILLFACTOR の設定に従ってデータで埋められます。また、データは、可能な限り物理的な順番に並ぶように配置されます。その結果、ページ密度が高くなり、論理断片化が解消します。
データベースやデータベースファイルの圧縮 (DBCC SHRINKDATABASE や SHRINKFILE) を実行すると、ファイルの後ろの方にあるデータは前方に移動され、ファイルの後方に空き領域が作り出され、その作り出されたファイル後方の空き領域部分を切り捨てることで、データベースファイルサイズが小さくなります。
インデックスを再構築した後にデータベースやデータベースファイルの圧縮を行うと、せっかく物理的に並んだデータが再び移動させられ、物理的に並んだ状態ではなくなってしまいます。つまり、インデックスの再構築により断片化の解消されたインデックスが、データベースの圧縮処理により再び断片化します。

実際に見てみましょう

----------------------------------------------

-- データベースを作成します。

create database shrinktest go

use shrinktest go

-- あとでファイルの前方に空きを作るために、ダミーのテーブルを作成します。 -- 1 ページに1 行入る計算です。

create table dbo.dummy (c1 int primary key clustered, c2 nvarchar(4000)) go

declare @i int set @i=0 while (@i<3000) begin   insert into dbo.dummy values (@i, REPLICATE(N'X',4000))   set @i+=1 end

-- 断片化を発生させるためのテーブルを作成します。

create table dbo.tab (c1 int primary key clustered, c2 nvarchar(800)) go

-- 偶数キー値のデータを入れます。

declare @i int set @i=0 while (@i<100) begin   insert into dbo.tab values (@i, REPLICATE('X',800))   set @i+=2 end

-- 奇数キー値のデータを入れます。 -- これにより断片化が発生します。

declare @i int set @i=1 while (@i<100) begin   insert into tab values (@i, REPLICATE('X',800))   set @i+=2 end go

-- 断片化の状況を見てみましょう

select index_type_desc, alloc_unit_type_desc, index_depth, index_level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent from sys.dm_db_index_physical_stats(db_id('shrinktest'),object_id('tab'),NULL, DEFAULT,'DETAILED') go

断片化率 63.3333333333333%、ページ密度 66.5678280207561% です。

index_type_ desc	alloc_unit_ type_desc	index_ depth	index_ level	avg_ fragmentation_ in_percent	fragment_ count	avg_ fragment_ size_ in_pages	page_ count	avg_page_space_ used_in_percent
CLUSTERED INDEX	IN_ROW_DATA	2	0	63.3333333333333	20	1.5	30	66.5678280207561
CLUSTERED INDEX	IN_ROW_DATA	2	1	0	1	1

-- インデックスを再構築します。

alter table tab rebuild go

-- インデックス再構築後の断片化の状況を見てみましょう。

select index_type_desc, alloc_unit_type_desc, index_depth, index_level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent from sys.dm_db_index_physical_stats(db_id('shrinktest'),object_id('tab'),NULL, DEFAULT,'DETAILED') go

論理断片化が 15% に低下し、ページ密度が 99.864096861873% まで上昇しました。

index_type_ desc	alloc_unit_ type_desc	index_ depth	index_ level	avg_ fragmentation_ in_percent	fragment_ count	avg_ fragment_ size_ in_pages	page_ count	avg_page_space_ used_in_percent
CLUSTERED INDEX	IN_ROW_DATA	2	0	15	5	4	20	99.864096861873
CLUSTERED INDEX	IN_ROW_DATA	2	1	0	1	1	1	3.18754633061527

-- ダミーのテーブルを削除して、ファイル内に空き領域を作成します。

drop table dbo.dummy go

-- データファイル圧縮を実行します。

dbcc shrinkfile('shrinktest') go

-- データファイル圧縮後の断片化の状況を見てみましょう。

select index_type_desc, alloc_unit_type_desc, index_depth, index_level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent from sys.dm_db_index_physical_stats(db_id('shrinktest'),object_id('tab'),NULL, DEFAULT,'DETAILED') go

ページ密度は変化ありませんが、断片化率が 90% まで上昇してしまいました。インデックス再構築する前よりも断片化が進んでしまったことになります。

index_type_ desc	alloc_unit_ type_desc	index_ depth	index_ level	avg_ fragmentation_ in_percent	fragment_ count	avg_ fragment_ size_ in_pages	page_ count	avg_page_space_ used_in_percent
CLUSTERED INDEX	IN_ROW_DATA	2	0	90	19	1.05263157894737	20	99.864096861873
CLUSTERED INDEX	IN_ROW_DATA	2	1	0	1	1	1	3.18754633061527

圧縮処理により、ファイルの後ろの方にあるデータは前方に移動され、ファイルの後方に空き領域が作り出されます。

その作り出されたファイル後方の空き領域部分を切り捨てることで、データベースファイルサイズが小さくなります。インデックスを再構築した後にデータベースやデータベースファイルの圧縮を行うと、せっかく物理的に並んだデータが再び移動させられ、物理的に並んだ状態ではなくなってしまいます。

つまり、インデックスの再構築により断片化の解消されたインデックスが、データベースの圧縮処理により再び断片化します。

--------------

この記事は以下のBlogの画像のリンク切れを修正し、再掲したものになります。

DO's&DONT's #7: やらない方がいいこと - インデックス再構築 (REBUILD) 後のデータファイル圧縮 (SHRINK) | Microsoft Docs