現在オフラインです。再接続するためにインターネットの接続を待っています

SQL Server の統計保守機能 (Autostats)

この記事は、以前は次の ID で公開されていました: JP195565
概要
AutoStats は新たに導入された統計保守機能ですが、以下の操作を実行すると運用環境で望ましくないオーバーヘッドが発生することがあります。
  • 負荷の高い運用時間帯に、統計情報の更新を実行する。

    または
  • 特定の時点で、必要以上の数の UPDATE STATISTICS プロセスを起動する。
この資料の目的は、Autostats の生成、およびデータベースのテーブルに対する UPDATE STATISTICS の実行がどのような条件で発生するかについて詳しく説明することです。

SQL Server 2000 の Autostats の詳細については、以下の MSDN Web サイトで「Microsoft SQL Server 2000 のクエリ オプティマイザで使用する統計」を参照してください。

: Microsoft SQL Server 2005 を使用している場合、SQL Server 2005 のクエリ オプティマイザによる統計情報の使用方法については、以下のマイクロソフトのホワイト ペーパーを参照してください。
詳細

一般的な情報

SQL Server はコストベースのオプティマイザを使用しているため、テーブルやインデックスで提供される統計情報に極度に依存する可能性があります。SQL Server は、正確かつ最新の統計情報がない場合、特定のクエリの最適な実行プランを判断することが困難になることがあります。

コストベースで判断を行うオプティマイザを支援するために、SQL Server の各テーブルに保持される統計情報には、以下のものがあります。
  • テーブルの行数
  • テーブルが使用するページ数
  • 最後に統計情報が更新されてから、テーブルのキーに対して行われた変更の数
インデックスに対して保存される付加情報は、以下の内容を含んでいます。これらは、各インデックスごとに保持されます。
  • 最初の列の等高ヒストグラム
  • すべての列プレフィックスの密度
  • キー平均長
新たにインデックスが作成されると、必ずインデックスの統計情報が自動的に作成されます。さらに、インデックスと同様に、インデックス以外のその他の列についても、統計情報を作成、維持できるようになります。

統計情報をできる限り最新に保つために、SQL Server では AutoStats が導入されています。AutoStats は、テーブルに対して行われた変更を SQL Server が監視する機能を使用して、変更が特定のしきい値に達したときに、テーブルの統計情報を自動的に更新できるようにする機能です。さらに、SQL Server には auto-create-statistics が導入されています。これにより、サーバーが特定のクエリを正確に最適化するために必要となるすべての統計情報が自動的に生成されます。

AutoStats の生成が近づいている時期を判断する

既に述べたように、AutoStats は "変更のしきい値" に達すると、該当のテーブルの統計情報を自動的に更新します。sysindexes.rowmodctr 列には、時間の経過と共に、クエリ プロセッサの意思決定プロセスにいずれ悪影響をもたらす可能性のあるテーブルに対して行われた変更の総数が保存されます。このカウンタは、以下のイベントが発生するたびに更新されます。
  • 1 行の挿入が行われる。
  • 1 行の削除が行われる。
  • インデックスが設定された列に対して更新が行われる。
: TRUNCATE TABLE は rowmodctr を更新しません。

テーブルの統計情報が更新されると、rowmodctr の値は 0 にリセットされます。そして、テーブルの統計スキーマのバージョンが更新されます。

さらに、ストアド プロシージャの実行プランがキャッシュから取得され、そのプランが統計情報に依存する状況では、統計スキーマのバージョンが現在のバージョンと比較されます。新たな統計情報を利用できる場合は、ストアド プロシージャのプランが再コンパイルされます。

統計情報の自動更新の基本的なアルゴリズムは、以下のとおりです。
  • テーブルの基数が 6 未満で、そのテーブルが tempdb データベース内に存在する場合は、テーブルが 6 回変更されるたびに自動更新します。
  • テーブルの基数が 6 よりも大きく、500 以下の場合は、500 回変更されるたびに状態を更新します。
  • テーブルの基数が 500 より大きい場合、(500 + テーブルの 20% の) 変更が行われたときに統計を更新します。
  • テーブル変数では、基数を変更しても統計の自動更新のトリガになりません。
: 厳密な意味では、SQL Server はテーブル内の行数として基数を数えます。

: 基数以外に、述語の選択も AutoStats の生成に影響します。つまり、基数 < 500 の場合に 500 回の変更が行われるたび、または基数 > 500 の場合にテーブルの 20% の変更が行われるたびに、統計が更新されないことがあります。述語の選択によってスケールアップ ファクタ (値 1 ~ 4 の範囲、1 と 4 を含みます) が生成され、このファクタとアルゴリズムに応じた変更数の積が、AutoStats の生成に実際に必要な変更の数になります。

上記のアルゴリズムを、以下のような表形式にまとめることができます。
_________________________________________________________________________________ テーブルの種類  | 空状態        | 空のときのしきい値 |空以外のときのしきい値_________________________________________________________________________________ パーマネント    | < 500 行     | 変更数 >= 500    | 変更数 >= 500 + (基数の 20% )___________________________________________________________________________ 一時            | < 6 行        | 変更数 >= 6      | 変更数 >= 500 + (基数の 20%)___________________________________________________________________________ テーブル 変数           | 基数の変更は、AutoStats の生成のタイミングには影響しません。___________________________________________________________________________
以下の 2 つの例を使用して、この概念に基づいた動作を説明します。

例 1

23 の行と 2 つのインデックスを持つ、pubs データベースの authors テーブルを考えます。UPKCL_auidind は一意なクラスタ化インデックスで、1 つの au_id 列にインデックスが設定されています。aunmind は非クラスタ化複合インデックスで、au_lname 列と au_fname 列に作成されています。このテーブルが持つ行数は 500 より少ないため、テーブル データに対して 500 回の変更が行われると、AutoStats が開始されます。変更は、500 回またはそれ以上の挿入、削除、au_lname などのインデックスが設定された列に対する変更、またはそれらの任意の組み合わせのいずれかになります。

したがって、各更新に基づいてインクリメントされる sysindexes.rowmodctr の値を監視することにより、UPDATE STATISTICS が開始される時期を予測できます。500 回に達するかそれを超えると、UPDATE STATISTICS が開始されると予想できます。

例 2

2 つ目のテーブルとして t2 を考えます。この t2 の基数は 1,000 です。500 行を超えるテーブルの場合は、(500 + 20%) の変更が行われると、SQL Server が UPDATE STATISTICS を行います。計算すると、1,000 の 20% は 200 です。したがって、約 700 回の変更がテーブルに対して行われると、AutoStats が開始されると予測できます。

Autostats の判断を自動化する

AutoStats が実行されるときの判断を自動化するために、sysindexes テーブルに問い合わせを行い、テーブル変更の回数がどの時点で AutoStats が開始される点に達するかを識別できます。以下は、この判断を行うための基本的なアルゴリズムです。
   if (sysindexes.rows > 500)      if (sysindexes.rows * 0.20 >= sysindexes.rowmodctr && production      hours) //500 change leeway         begin            disable autostats            log autostats disable         end      else         begin            stats ok         end   else      if (sysindexes.rowmodctr >= 425) //75 change leeway         begin            disable autostats            log autostats disable         end				

以下のようにスケジュールを設定することにより、後でジョブを実行できます。
  • 日中、UPDATE STATISTICS を無効に設定したテーブルすべてに対して、UPDATE STATISTICS を実行します。

    および
  • UPDATE STATISTICS を実行した時点で各テーブルの変更カウンタが 0 にリセットされるので、AutoStats を再度有効にします。

テーブルに対して UPDATE STATISTICS を実行するかどうかを制御する

AutoStats に問題があると判明したとき、この問題に対する最も明白な解決方法は、統計情報を自動生成しないことです。データベース管理者は統計情報の自動生成を行わないようにすることにより、負荷の少ない時間帯に UPDATE STATISTICS を実行するようスケジュールを設定できます。自動生成を無効にするには、UPDATE STATISTICS ステートメントまたは sp_autostats ストアド プロシージャのいずれかを使用できます。UPDATE STATISTICS ステートメントの構文を以下に示します。
   UPDATE STATISTICS <table>...with NORECOMPUTE				

sp_autostats ストアド プロシージャの構文を以下に示します。
sp_autostats <table_name>, <stats_flag>, <index_name>
ここで <stats_flag> には "on" または "off" のいずれかを指定します。

sp_dboption を使用して、データベースごとに UPDATE STATISTICS や CREATE STATISTICS が自動的に発生しないようにすることもできます。
sp_dboption <dbname>,'auto update statistics', <on | off>

または

sp_dboption <dbname>,'auto create statistics', <on | off>

UPDATE STATISTICS プロセスの同時実行数を制御する

現時点では、特定のテーブルの AutoStats を無効にすることを除いて、同時に実行される自動 UPDATE STATISTICS ステートメントの数を構成することはできません (これは、DCR 51539 としてファイリングされました)。ただし、サーバーは、同時に実行される UPDATE STATISTICS プロセスの数をプロセッサごとに 4 に制限します。

Autostats が実行されようとする瞬間を判断する

AutoStats の結果として統計依存のストアド プロシージャが再コンパイルされる時点をレポートするために、トレース フラグ 205 を使用できます。このトレース フラグにより、エラー ログに次のメッセージが書き込まれます。
1998-10-15 11:10:51.98 spid9 Recompile issued : ProcName: sp_helpindex
LineNo: 75 StmtNo: 29
トレース フラグ 205 が有効な場合、統計が更新されるときに、トレース フラグ 8721 により以下の AutoStats のメッセージもひとまとめに出力されます。まとまりの始めのメッセージは、0 より大きい RowModCnt 値で区別できます。UPDATE STATISTICS の後にまとまりの終わりのメッセージは RowModCnt が 0 になります。
1998-10-15 11:38:43.68 spid8 Schema Change: Tbl Dbid: 7 Objid:
133575514 RowModCnt: 60500 RowModLimit: 60499
このメッセージでは、"RowModCnt" はテーブルに対する変更の総数です。"RowModLimit" はしきい値で、この値を超えると、UPDATE STATISTICS ステートメントがそのテーブルに対して実行されます。

トレース フラグ 8721 を有効にすることもできます。このフラグでは、AutoStats が実行されたときに、エラー ログに情報が出力されます。以下はそのメッセージ例です。
1998-10-14 16:22:13.21 spid13 AUTOSTATS: UPDATED Tbl: [authors]
Rows: 23 Mods: 501 Bound: 500 Duration: 47ms UpdCount: 2
このメッセージでは、"Mods" はテーブルに対する変更の総数です。"Bound" は変更のしきい値で、"Duration" は要求された UPDATE STATISTICS ステートメントが完了するのに要した時間の総計です。"UpdCount" は UPDATE STATISTICS の数です。

SQL Server プロファイラを使用することで、UPDATE STATISTICS ステートメントが実行されようとするその時点を識別できます。これを行うには、以下の手順を実行します。
  1. プロファイラのメニューで [ツール] をクリックし、[オプション] をクリックします。
  2. [全般] タブの [イベント] で、[すべてのイベント クラス] をクリックします。
  3. 新たなトレースを定義し、[イベント] タブで [その他] イベントの Auto-Update Stats サブ イベントを選択します。
注 : AutoStats によって多くの統計情報が更新されようとしている場合、多数のメッセージがエラー ログに書き込まれます。運用環境やその他の重要なサーバーでこれらのトレース フラグを使用する前に、十分にテストを繰り返すことをお勧めします。

スキーマ ロック

SQL Server は 2 種類のスキーマ ロックを使用しており、SQL Server がテーブルの統計情報を更新するときにその両方が使用されます。
   Sch-S: スキーマ安定度ロック   ----------------------------   このロックは、セッションがスキーマ要素に   スキーマ安定度ロックを保持している間、   テーブルやインデックスなどのスキーマ要素が削除されないことを保証します。   Sch-M-UPD-STATS: スキーマ修正ロック   -----------------------------------------   これは、ブロックされないロックです。   特定の時点で 1 つのテーブルに対して 1 つの自動 UPDATE STATISTICS プロセスだけを   実行することを保証するために、システムが使用します。sp_lock ストアド プロシージャは   このロックを、type = TAB、resouce = UPD-STATS、mode = SCH-M としてレポートします。				
sp_lock を実行するか、syslockinfo テーブルを SELECT することで、これらのロックを表示できます。
prodsql indices non-clustered stat kick off errorlog SQL Server Profiler
プロパティ

文書番号:195565 - 最終更新日: 08/27/2007 01:15:32 - リビジョン: 7.2

  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Developer Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Personal Edition
  • Microsoft SQL Server 2000, Workgroup Edition
  • Microsoft SQL Server 2000 Desktop Engine (Windows)
  • Microsoft SQL Server 2000 Enterprise Edition 64-bit
  • kbinfo KB195565
フィードバック