SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

統計情報の自動更新に関する考察

leave a comment

統計情報の自動更新の挙動について自分なりに考えるためのメモになります。
検証しながら試してはいますが正確に挙動を把握できているとは言い難いので考察というタイトルにしています。

Microsoft SQL Server Japan Support Team Blog さんのブログの 統計情報の自動更新が ON の時には統計情報を手動で更新する必要はない? を読んで、以前自分で書いた SQL Server 2008 R2 SP1 で追加された統計情報の自動更新に関してのトレースフラグ でリコンパイルをする必要があったことにつながるのかなと思っています。

■統計情報の自動更新のタイミング


Support Team さんのブログで、統計情報の自動更新によって統計が更新されるタイミングについて

おおよそテーブルの 20% に相当するデータが更新されると、そのデータの統計は自動更新の対象になります。
この更新条件が満たされた状態で統計情報を参照する UPDATE, INSERT, DELETE または MERGE ステートメントが実行されると、AUTO_UPDATE_STATISTICS が ON の場合はそのステートメントの一部として統計情報が更新されます。AUTO_UPDATE_STATISTICS_ASYNC が ON の場合は、そのステートメントによって統計情報更新のための非同期実行タスクがポストされます。この場合、統計情報はそのステートメントとは無関係に更新され、そのステートメント自身は更新前の統計情報を参照します。

条件を満たすことになる更新系ステートメントの実行時に統計情報が更新される、もしくは、条件が満たされた時点でバックグラウンドタスクにより統計情報が更新されるといった誤解をしている方を稀に見かけますが、正しくは、前述のとおり、統計情報を更新するのは、条件が満たされた状態の統計情報を最初に参照しようとしたステートメントです。

という記載があります。

記事内の参考情報として記載されている以下のリンクを読むと、

Microsoft SQL Server 2005 のクエリ オプティマイザが使用する統計情報
Statistics Used by the Query Optimizer in Microsoft SQL Server 2008

一連の INSERT、DELETE、UPDATE の任意の組み合わせがテーブルに対して実行された後に、対象の列またはインデックスにおける本当のデータ分布が統計情報に反映されないことがあります。SQL Server クエリ オプティマイザで、テーブル内の特定の列の統計情報が必要になったときに、その統計情報が作成または更新されて以降実質的な更新処理が進行している場合、SQL Server は (auto update statistics を使用して) 列の値をサンプリングすることによって、統計情報を自動的に更新します。統計情報の自動更新は、クエリの最適化またはコンパイル済みプランの実行によってトリガされ、クエリ内で参照されている列のサブセットのみが更新の対象になります。AUTO_UPDATE_STATISTCS_ASYNC がオフの場合はクエリのコンパイル前に統計情報が更新され、AUTO_UPDATE_STATISTCS_ASYNC がオンの場合は非同期に更新されます。

クエリが初めてコンパイルされるとき、オプティマイザで特定の統計情報オブジェクトが必要になり、その統計情報オブジェクトが存在する場合、その日付が古ければ統計情報オブジェクトが更新されます。クエリが実行されてそのプランがキャッシュに置かれると、プランで必要な統計情報がチェックされ、日付が古いかどうかが確かめられます。日付が古い場合はそのプランがキャッシュから削除され、クエリの再コンパイル時に統計情報が更新されます。また、プランで必要ないずれかの統計情報が変更された場合にも、プランがキャッシュから削除されます。

という記載があります。

統計情報の更新日が以下の状態で、
image

全データの更新を行います。
image

データの母数にもよりますが 20% 以上の更新をしていますので、統計情報の自動更新の対象となるはずですね。

クエリ実行のタイミングで統計情報の自動更新が発生しているかを拡張イベントの [auto_stats] を使用して確認してみます。
image

大量の更新をしても自動更新は発生していません。

統計情報の更新日を確認してみると、
image

先ほどと同じ日付となっています。
このことから統計情報の自動更新の対象となったタイミングと実際に統計情報が更新されるタイミングについては同期していないいと考えることができます。

続いて更新を行った後にすでにコンパイルされている SELECT を実行してみます。
# 統計が使われるように統計が作成されている列を使った Seek 操作になるようにしています。
SELECT を実行する前の統計情報の更新日は以下のようになっています。
image

image

この状態でも統計情報の自動更新のイベントは発生していません。
image

続いて、先程の SELECT に RECOMPILE オプションを設定してみます。
image

今回は自動更新のイベントが発生しました。
image

統計の自動更新のきっかけとなった SQL としては RECOMPILE を指定したクエリが表示され、STATS_DATE で確認をした統計情報も更新が行われています。
image

全件更新 → プランキャッシュノクリア (DBCC FREEPROCCACHE) → SELECT (RECOMPILE なし) という操作を行った場合も統計情報の自動更新が発生します。
image

このことから、統計情報の自動更新によって実際に統計上のの更新が行われるタイミングはコンパイル / リコンパイルと関連性がありそうです。
統計情報の自動更新はコンパイルが発生した際に、統計情報を更新するというフラグを立てるための動作になっているのではないでしょうか。また、統計情報の自動更新の対象となってもリコンパイルの対象とはなっていないようです。
そのため、大量のデータが更新されても統計情報の更新が行われていないという場合は、対象の統計を使用しているクエリのコンパイルが行われていないという状況になっているのではないでしょうか。

なお、統計の更新が発生した場合、対象のインデックスに対してスキャン操作が行われているようです。
# 統計情報を作成するために対象のインデックスのデータを検索しているためだと思います。
image

 

統計情報の自動更新はデータ数にもよりますが一般的には 20 % のデータ更新をした場合が対象となります。
そのため、閾値に達しない更新で統計情報を更新したい場合には、[sp_updatestats] [UPDATE STATISTICS] を使用して手動の更新を行う必要があります。
以下のクエリでは 10% の更新を行っています。
image

この場合、統計情報の自動更新の閾値には達しませんので、統計情報を更新したい場合には手動で更新を行います。
 image
手動による統計情報の更新は閾値に関係なく統計情報を最新の状態にしますので、現状に合わせた統計情報が作成されます。

SQL Server 2005 でのパフォーマンス問題のトラブルシューティング によると、再コンパイルの対象は

  • スキーマ変更

  • 統計変更

  • コンパイルの延期

  • SET オプションの変更

  • 一時テーブルの変更

  • RECOMPILE クエリ ヒントまたは OPTION (RECOMPILE) を使用したストアド プロシージャの作成

となるようなので統計の変更によるクエリの再コンパイルが発生しそうなのですが私の調査方法だとうまく取得ができませんでした…。
# SQL Server 2000 の情報になりますが、[INF] SP:Recompile イベントで再コンパイルの原因を特定する方法 にも統計の変更が再コンパイルの理由に含まれているので、UPDATE STATISTICS をした後には再コンパイルイベントが発生しそうなものなのですが。

[sys.dm_xe_map_values] にもリコンパイルの理由として [Statistics Changed] が含まれていますので、統計情報を更新した際の再コンパイルがうまく拾えていないのは私のデモシナリオの問題だと思います。

なお、SQL Server 2012 では再コンパイルが発生する理由としては以下がトリガーとなるようです。
image

Schema changed

Statistics changed

Deferred compile

Set option change

Temp table changed

Remote rowset changed

For browse permissions changed

Query notification environment changed

PartitionView changed

Cursor options changed

Option (recompile) requested

Parameterized plan flushed

Test plan linearization

Plan affecting database version changed

 

 

統計情報の更新されるタイミングとしてインデックスの再構築 (REBUILD) がありますが、インデックスの再構築は [Schema changed] となるようで、インデックス再構築後のクエリ実行はリコンパイル対象となっていました。
この場合は最新の統計を使用したプランが生成されるかと思います。
# 再構成 (REORGANIZE) では統計情報は更新されていませんでした。
image

統計情報の更新とクエリのコンパイル、再コンパイルに関してはきちんと挙動をつかめていないなと痛感しました。
このあたりは基本的な動作になると思いますがまだまだ理解が追い付いていないことがたくさんですね。

Written by masayuki.ozawa

4月 28th, 2012 at 2:23 pm

Posted in SQL Server

Tagged with

Leave a Reply

*