DO's&DONT's #8: やってはいけないこと - インデックス再構築 (REBUILD) 後のインデックス統計情報更新 (UPDATE STATISTICS)
神谷 雅紀
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 (例外あり)
Comments
Anonymous
May 15, 2013
SQL Server 2012 に関しては、blogs.msdn.com/.../sqk_2d00_server_2d00_2012_2d00_updatestats.aspx も参照して下さい。Anonymous
April 14, 2015
これは参考になりました。まさに週末にインデックス再構築とおまじない的に統計情報の更新を行っておりました。 逆にインデックス再構築の効果を下げるのでやらないほうがいいということがよくわかりました。 ありがとうございます。