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


神谷 雅紀
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

 

image

 

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

 

image

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

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

 

image

 

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

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

 

image

 

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

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

 

image

 

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

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

 

image

 

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

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

 

image

 

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

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

 

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

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

 

image

 

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

 

 

 

更新日 更新内容 更新者
2013/2/5 図を追加 藤野樹
Comments (2)

  1. daisuky より:

    勉強になりました。

    Shrink やろうとして、これが出てきました。

    過去に別件で問い合わせたことがありまして、本件とは全然関係なく、単に圧縮目的でした。

    その当時は、メールで回答を受け取っていまして、今回業務用にもう一度検索しました。

    一方で、こちらの記事をRSSで配信受け取りしていたことで、Shrinkで検索してこれがヒット。

    思わぬ追加学習が発生してよかったです。

    私自身がDB専門家とは程遠いので、この記事がコアな感じ、かつ、短時間で理解しやすい記事に

    仕上がっているのがさらによかったです。図になっているのがいいのでしょうね。

  2. Ken より:

    他のAPでindexの再構築後にDBの圧縮をやらない方が良いとありましたが、このご説明で理由が理解できました。

    追加ですが、最後の図から考えると、「インデックスの再構築により断片化の解消されたインデックスが、データベースの圧縮処理により再び断片化します。」の後に、再度indexを再構築すると、indexの断片化もなく、不要な空き領域もない状態になるのかなと考えましたが、如何でしょうか?

Skip to main content