SQL Server/統計情報

• 統計

• Does Updating Statistics Cause a Recompile if No Data Has Changed?

• sys.dm_exec_query_stats (Transact-SQL)
• sp_autostats (TRANSACT-SQL)

• 【統計情報】_WA_SYS_XXXについて

• 統計情報の自動更新に関する考察
• 統計情報のサンプリングの実行プラン

• 統計情報の自動更新・手動更新の使い分け

• SQL Server 統計情報の自動更新
• SQL Server 2008(2012)の統計自動更新について：トレースフラグ2371

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

• トラブルシューティングから学ぶSQL Server統計情報の更新タイミング
  • SQL Database で VIEW SERVER STATE が必要なログインを複数作成する

• どうする？ SQL Server のクエリ パフォーマンスが低下した！

• 「あるタイミングから」処理が遅くなり、再起動しても再現されてしまう（パフォーマンストラブル）

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

• SQL Serverサービス再起動に伴う処理について

• 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')

統計情報の設定 †

• SQL Server の統計情報作成について書いてみた、ちょっとだけマジメにまとめてみた

小規模向け †

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

大規模向け †

• 統計情報の自動更新　：False

• 統計情報を手動で作成
• 統計情報を手動で更新

統計情報の自動更新 †

• 統計の更新

• SQL Server 統計情報の自動更新
• SQL Server 2008(2012)の統計自動更新について：トレースフラグ2371

• 統計情報のサンプリングについて

自動更新の条件 †

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

統計情報の非同期更新 †

• SQL Server 統計の非同期的自動更新

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

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

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

• 参考：【SQL Server】統計情報のヒストグラムと実行プランの予測行数 - 小物SEのメモ帳
• 注意：OFFだと、新たなインデックス追加時などにも統計情報が作成されなくなる。
• Missing Column Index イベントをトレースして統計情報のないIndexが通知を受け取ることができる。

統計情報に関するSQL †

• STATS_DATE (Transact-SQL)
• sys.dm_db_stats_properties (Transact-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;