SQL Server 2008 R2 SP1 (Denali 含む) で追加された統計情報の自動更新に関してのトレースフラグ(TF) の情報を昨日発見しましたので軽くまとめてみたいと思います。
調べていく中で統計情報の自動更新が発生するタイミングできちんと理解できていないことを痛感しました…。
これについてはおいおい調べていくとして、まずはトレースフラグについて簡単にまとめてみたいと思います。
参考にさせていただいた情報はこちらになります。
Changes to automatic update statistics in SQL Server ? traceflag 2371
New SQL 2008 R2 SP1 trace flag adjusts autostats threshold
以前、SAP On SQL Server の中の人のセミナーを受講した際に SP1 でトレースフラグが追加されるというのを聞いていたのですが、リリースノートを軽く見たところ見当たらなかったのですっかり忘れていました…。
本トレースフラグですが、2005 /2008 では使えないようですね。
今回は Denali CTP3 に 300,000 件のデータを入れた状態でテストをしています。
Contents
■統計情報の自動更新の閾値
今回はトレースフラグの効果を試すことを主目的にしていますのでざっくりとした概要レベルで。
統計情報の自動更新ですがデータの基数が多くなるとデータの 20% 程度更新されると自動更新の対象となります。
SQL Server 2000 の情報ですが基本的な考えはこちらに。
SQL Server の統計保守機能 (Autostats)
?2005 はこちら
Microsoft SQL Server 2005 のクエリ オプティマイザが使用する統計情報
■トレースフラグをテスト
今回は、以下のようなクエリを実行してテストをしています。
UPDATE TOP(100) PERCENT AutoStats SET Col2 = RAND() * 100000 SELECT COUNT(*) FROM AutoStats WHERE Col2 > 100 OPTION(RECOMPILE) GO UPDATE TOP(25) PERCENT AutoStats SET Col2 = RAND() * 100000 SELECT COUNT(*) FROM AutoStats WHERE Col2 > 100 OPTION(RECOMPILE) GO |
最初に 100% のデータを更新して、そのあとに 25% のデータを更新しています。
統計情報の自動更新の閾値は 20% 程度ですので、このクエリであれば統計情報の自動更新が 2 回発生するはずですね。
# OPTION(RECOMPILE) を意図的に入れているところが自動更新を理解していないと痛感している個所だったりするのですが。
統計情報の自動更新の発生状況については [auto_stats] の拡張イベントで取得することができますので、このクエリを実行しているときのイベントの発生状況を取得してみます。
統計情報の自動更新が 2 回発生しているのが確認できますね。
25% にしている個所を 15% に変更したときの自動更新の発生状況がこちら。
データ更新の対象が少ないと自動更新が発生していないことが確認できますね。
それでは、SQL Server 2008 R2 SP1 で追加されたトレースフラグ 2371 を設定してみたいと思います。
実際にテストで使用したクエリがこちらになります。
DBCC TRACEON(2371) GO UPDATE TOP(100) PERCENT AutoStats SET Col2 = RAND() * 100000 SELECT COUNT(*) FROM AutoStats WHERE Col2 > 100 OPTION(RECOMPILE) GO UPDATE TOP(15) PERCENT AutoStats SET Col2 = RAND() * 100000 SELECT COUNT(*) FROM AutoStats WHERE Col2 > 100 OPTION(RECOMPILE) GO DBCC TRACEOFF(2371) GO |
トレースフラグを有効にした状態で、先ほどは自動更新が発生しなかった 15% の更新を実行しています。
トレースフラグを設定した場合は 15% の更新でも統計情報の自動更新が発生しているのが確認できますね。
データの基数によって統計情報の閾値を自動調整しているようなので、必ず 20% より下回るということもなさそうですが、今までより高い頻度で自動更新を発生させることができそうですね。
運用で手動でのメンテナンスを視野に入れたほうが良いかと思いますが、統計情報の乖離はこのフラグで減らすことができるかもしれないですね。
[…] […]
統計情報の自動更新に関する考察 « SE の雑記
28 4月 12 at 14:23