SQLServer で突然遅くなった時の対処法 (統計情報更新 WITH FULLSCAN)

背景

いつもは数秒で終わる SQL が何時間もかかる・・。CPU 負荷はそこそこ高い。
sys.dm_exec_requests で last_wait_type を見ると SOS_SCHEDULER_YIELD と出ている。
そして 1 - 2 日もすると自然に収束してしまう・・。

そんな時は効率の悪い実行プランが生成されているのかも？
統計情報更新すると改善されるかもしれません。

クエリ

-- テーブルごと
UPDATE STATISTICS SAMPLE_TABLE;

-- または、全テーブルまとめて
sp_updatestats;

ただしこの統計情報更新、サンプリングの割合というものが存在し、オプションを指定しない場合、既定でサンプリングしたデータが使用されるそうです。
なので上記で改善が見られない場合は、FULLSCAN (SAMPLE 100 PERCENT) でやってみると良いかもしれません。

-- テーブルごと (フルスキャン)
UPDATE STATISTICS SAMPLE_TABLE WITH FULLSCAN;

私の場合、上記をトランザクション系のテーブルに絞って実行してみました。
フルスキャンにすることで更新にかかる時間が 30 分から 2 時間ほどになりましたが、事象発生数が激減しました。

ここまでの道のり

今まで sp_updatestats での統計情報更新や INDEX 断片化の解消、DB のスペックアップ、地道な INDEX 貼りや SQL チューニングに取り組んできましたが、抜本的解決には至らず・・。

半年間悩まされ試行錯誤しましたが、最後は MS (Azure) のサポートの方と何回かやり取りし、解決することができました。
親身になって相談にのって頂き大変感謝しております。

本記事が同じ問題で悩んでいる方の参考になりましたら幸いです。

補足

AUTO_UPDATE_STATISTICS という、テーブル全体の約 20% が更新されると自動で統計情報更新してくれる機能がデフォルトで ON になっているのでユーザー側で統計情報更新をしなくても大丈夫かなと思っていたのですが、やはり使われ方によるようですね。

統計情報の自動更新オプション (AUTO_UPDATE_STATISTICS または AUTO_UPDATE_STATISTICS_ASYNC) が ON に設定されている場合であっても、UPDATE STATISTICS や sp_updatestats を実行して明示的に統計情報を更新する必要がある場合はあります。

参考

• 統計情報の自動更新が ON の時には統計情報を手動で更新する必要はない？
• SQLServer: 現在実行中クエリのリアルタイムトラブルシューティング
  • トラブルの度に大変お世話になりました。ありがとうございます！
• UPDATE STATISTICS (Transact-SQL)
• sp_updatestats (Transact-SQL)