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

  • 全般的な情報交換

  • (※ 2013 年 2 月 4 日に Microsoft SQL Server Japan Support Team Blog に公開した情報のアーカイブです。)

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

     

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


    2019年3月28日 11:10
    所有者