Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
神谷 雅紀
SQL Server Escalation Engineer
インデックスの再構築 (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 | 1 | 4.79367432666172 |
-- インデックスを再構築します。 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 |
圧縮処理により、ファイルの後ろの方にあるデータは前方に移動され、ファイルの後方に空き領域が作り出されます。
その作り出されたファイル後方の空き領域部分を切り捨てることで、データベースファイルサイズが小さくなります。インデックスを再構築した後にデータベースやデータベースファイルの圧縮を行うと、せっかく物理的に並んだデータが再び移動させられ、物理的に並んだ状態ではなくなってしまいます。
つまり、インデックスの再構築により断片化の解消されたインデックスが、データベースの圧縮処理により再び断片化します。
| 更新日 | 更新内容 | 更新者 |
| 2013/2/5 | 図を追加 | 藤野樹 |
- Anonymous
December 02, 2013
The comment has been removed - Anonymous
July 05, 2015
他のAPでindexの再構築後にDBの圧縮をやらない方が良いとありましたが、このご説明で理由が理解できました。 追加ですが、最後の図から考えると、「インデックスの再構築により断片化の解消されたインデックスが、データベースの圧縮処理により再び断片化します。」の後に、再度indexを再構築すると、indexの断片化もなく、不要な空き領域もない状態になるのかなと考えましたが、如何でしょうか？