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

  • Article

神谷 雅紀
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 (@i*RAND()*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 (例外あり)