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

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

  • 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

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

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: 2020-02-24 (月) 15:14:02 (100d)