統計情報がらみのトラブル

統計情報を更新しても改善されない場合は、一度再起動してみる

  • SQL Serverが狂った統計情報を使い続けている可能性あり
  • SQL Serverを再起動することで、更新された統計情報が使用されるようになって、大体改善される

再起動しなくても、キャッシュをクリアすれば良い

DBCC FREEPROCCACHE (Transact-SQL)

DBCC FREEPROCCACHE

DBCC FREESYSTEMCACHE (Transact-SQL)

  • DBCC FREESYSTEMCACHE を実行すると、SQL Server のインスタンスのプラン キャッシュが消去される
  • プラン キャッシュが消去されると、後続のすべての実行プランが再コンパイルされ、場合によっては、クエリ パフォーマンスが一時的に急激に低下する
  • プラン キャッシュ内のキャッシュストアがクリアされるたびに、"SQL Server は、'DBCC FREEPROCCACHE' 操作または 'DBCC FREESYSTEMCACHE' 操作により、'%s' キャッシュストア (プラン キャッシュの一部) のキャッシュストア フラッシュを %d 個検出しました。" という情報メッセージが SQL Server エラー ログに記録される
  • このメッセージは、5 分以内にキャッシュがフラッシュされる限り、5 分間隔でログに記録される
DBCC FREESYSTEMCACHE('ALL')

統計情報の設定

小規模向け

  • 統計情報の自動更新 :True
  • 統計情報の非同期更新:True

大規模向け

  • 統計情報の自動更新 :False
  • 統計情報を手動で作成
  • 統計情報を手動で更新

統計情報の自動更新

自動更新の条件

  • テーブル行数が0から非0に推移した
  • 前回統計情報が更新された際のテーブル行数が500以下で、統計情報収集対象列が501回以上更新された場合
  • 前回統計情報が更新された際のテーブル行数が501以上で、統計情報収集対象列が500+(テーブル行数の20%)回以上変更された
  • 一時テーブルの場合は、上述の基準に加え、テーブル行数が6行を超えた

統計情報の非同期更新

  • 非同期自動更新のメリット
    • オンライン系のシステム (OLTP) と相性がいい
    • 統計を同期的に更新しないため、タイムアウトが発生する確率が減る
  • 非同期自動更新のデメリット
    • データウェアハウスなど大規模データをバッチで回すような場合に性能が劣化する
    • シングルユーザモードの利用が出来なくなる

統計情報自動更新OFFでのシステム運用

  • 統計情報の自動更新がONの状態で、オンライン中に統計情報の更新が発生すると、性能的に問題が出ることがある。
  • しかし、統計情報の自動更新をOFFにした場合、結局、統計情報が実データと乖離した際に問題が発生する。
  • 従って、統計情報更新のOFF運用は以下のようになると考える。
    • サーバ・メンテナンス時間帯に統計情報の更新をONにして統計情報を更新する。
    • STATS_DATE関数で、統計の最終更新日を確認できる。
    • サーバ・メンテナンス時間帯に問題(乖離)を発見して手動更新する(難しい)。

統計情報に関するSQL

統計情報の確認

DBCC SHOW_STATISTICS([テーブル名],[統計情報名])
  • このコマンドを実行すると以下が出力される
    • ヘッダー情報
      • 統計情報名
      • 統計情報の最終更新日
      • テーブルの行数
      • 統計情報作成時に使用されたサンプル行数
    • 密度情報
    • ヒストグラム
      • RANGE_HI_KEY ... ステップの上限キー値
      • RANGE_ROWS ... ステップ範囲内の行数。RANGE_HI_KEYは含まない
      • EQ_ROWS ... RANGE_HI_KEYと全く同じ値の行数
      • DISTINCT_RANGE_ROWS ... ステップ範囲内の一意なキー値の数。RANGE_HI_KEYは含まない
      • AVG_RANGE_ROWS ... ステップ範囲内の一意な値ごとの平均行数

最終更新日時・更新回数の確認

SELECT
    OBJECT_NAME(dsp.object_id) 
    , dsp.object_id 
    , ss.name 
    , ss.auto_created
    , ss.user_created
    , ss.no_recompute
    , dsp.last_updated 
    , dsp.rows
    , dsp.rows_sampled 
    , dsp.steps 
    , dsp.unfiltered_rows 
    , dsp.modification_counter 
FROM
    sys.stats AS ss 
    CROSS APPLY 
    sys.dm_db_stats_properties(ss.object_id, ss.stats_id) AS dsp 
WHERE
    OBJECT_SCHEMA_NAME(dsp.object_id, DB_ID()) <> 'sys'
ORDER BY
    dsp.last_updated desc

統計情報更新

EXEC sp_updatestats;

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2021-01-01 (金) 17:34:36 (16d)