locked
DO's&DONT's #8: やってはいけないこと - インデックス再構築 (REBUILD) 後のインデックス統計情報更新 (UPDATE STATISTICS) RRS feed

  • 全般的な情報交換

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

    神谷 雅紀
    SQL Server Escalation Engineer

     

    DO's&DONT's #7: やらない方がいいこと - インデックス再構築 (REBUILD) 後のデータファイル圧縮 (SHRINK) で、インデックス再構築後のデータベース圧縮を話題にしましたが、似たようなものとして、インデックス再構築後のインデックス統計情報更新があります。

    インデックス再構築後に、再構築したインデックスの統計情報を更新すると、それは意味がないか、統計情報の精度を落とします。

     

    なぜ?

     

    DO's&DONT's #7: やらない方がいいこと - インデックス再構築 (REBUILD) 後のデータファイル圧縮 (SHRINK) でも触れていますが、インデックスの再構築によりインデックスは再作成されます。インデックスが再構築される時、同時にそのインデックスの統計情報も作成されます。ここで作成される統計情報は、データサンプル率 100% で作成されます。インデックス作成時には、すべての行が読み取られるため、その読み取られた行を使って、同時に統計情報も作成されるためです。

    これは、テーブルにデータがある状態で、新規にインデックスを作成した場合も同様です。

    この状態で再度統計情報を更新することは、仮に 100% のサンプル率で更新したとしても、同じことを繰り返す分だけ無駄です。また、統計情報の更新 (UPDATE STATISTICS や sp_updatestats の実行) を行う時、統計を作成するために参照されるデータは、明示的にサンプル率 100% と指定していない限り、既定では、ランダムに読み取られた少数のデータです。ある程度大きなテーブルでは、テーブル全体の行の数パーセントです。せっかく全データを使って作成された統計情報を破棄してまで、少数のデータで統計情報を作り直す必要はありません。サンプルデータは多い方が精度の高い統計になります。

    尚、インデックス再構築によって更新される統計は、更新対象インデックスの統計であって、それ以外の統計 (更新対象ではないインデックスや列の統計) は更新されません。

     

    実際に見てみましょう

     

     

     

    use tempdb
    go

    -- テーブルを作成します。

    create table dbo.stat_test (c1 int, c2 nvarchar(10))
    go

    -- インデックスを作成します。

    create index ind_stat_test on dbo.stat_test(c1)
    go

    -- データを入れます。

    declare @i int
    set @i=0
    while (@i<1000000)
    begin
      insert into dbo.stat_test values (@iRAND()1000+@i, N'X')
      set @i+=1
    end

    -- 統計情報をみて見ます。

    dbcc show_statistics([dbo.stat_test], ind_stat_test)
    go

       

    今は統計情報がないので、すべて NULL です。(この出力は、DBCC SHOW_STATISTICS の最初の結果です。)

    Name Updated Rows Rows
    Sampled
    Steps Density Average
    key
    length
    String
    Index
    Filter
    Expression
    Unfiltered
    Rows
    ind_stat_test NULL NULL NULL NULL NULL NULL NULL NULL NULL

     

     

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

    alter index ind_stat_test on dbo.stat_test rebuild
    go

    -- 統計情報をみて見ます。

    dbcc show_statistics([dbo.stat_test], ind_stat_test)
    go

       

    サンプル行数は、テーブルの行数と同じ、 つまり、サンプル率 100% です。

    Name Updated Rows Rows
    Sampled
    Steps Density Average
    key
    length
    String
    Index
    Filter
    Expression
    Unfiltered
    Rows
    ind_stat_test 07 22 2011  4:44PM 1000000 1000000 42 0.9989809 4 NO NULL 1000000

     

     

    -- 統計情報を更新します。

    update statistics dbo.stat_test ind_stat_test
    go

    -- 統計情報をみて見ます。

    dbcc show_statistics([dbo.stat_test], ind_stat_test)
    go

       

    統計情報を更新することで、 サンプル行数が 1000000 から 445150 へ減ってしまいました。

    Name Updated Rows Rows
    Sampled
    Steps Density Average
    key
    length
    String
    Index
    Filter
    Expression
    Unfiltered
    Rows
    ind_stat_test 07 22 2011  4:44PM 1000000 445150 51 0.9995664 4 NO NULL 1000000

     

    適用対象バージョン : SQL Server 2005, 2008, 2008 R2, 2012 (例外あり), 2014 (例外あり), 2016 (例外あり)


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