パラメータスニッフィングについての予防策

Question

表題の件につきましてご質問です。

この度、特定の処理において普段は一瞬で終わる処理が普段の10倍程度時間がかかった現象があって調べていたところ、
遅延発生時と通常時で異なる実行プランが使われていることが分かりました。
統計情報はこまめに更新をかけているので統計情報が古くなっていることは考えづらく、
パラメータスニッフィングが発生しているものと想定しました。
（残念ながらどういう実行プランがキャッシュされていたのかは改善してしまったため確認できず）

このパラメータスニッフィングという現象は、事前対策として何かできることはあるのでしょうか？
同じ現象が他の処理でも発生しないかどうかを確認する必要が出てきたのですが、
全てのプロシージャを確認するにしても検索条件によりけりなのでどのような確認をすればいいのかが思いつきません。

以下のサイトにより
https://www.sqlshack.com/query-optimization-techniques-in-sql-server-parameter-sniffing/
IF文はあまり使用しない方がいい、長いプロシージャは短くするなどの対策方針はありますが、
こういう確認をしていればまず発生しえないと保証できる手順はございますでしょうか？
それともデータによりけりでなかなか想定しづらいので、発生してからの事後対応をするケースが多いのでしょうか？

よろしくお願いします。

Answer 1

tanifujiさん

パラメータスニッフィング自体は、良いことでも悪いことでもなく、SQL Serverの挙動です。パラメータスニッフィングをもしOFFにすることができても、＝一切キャッシュしないということになるかと思いますので、効率が悪すぎてオススメできません。

避けるべきは、パラメータスニッフィングが原因で特定のクエリが（本当はもっと早く終わるプランがあるのに）遅い実行プランで実行されてしまった、という事象です。

→これは事前に防ぎきるのは難しく、実現可能な範囲でいえば遅くなったことを検知し、改善する、というアプローチかと思われます。SQL Server 2016以降であればクエリストアという機能でこれを実現できますが、2014以前でもがんばれば自前でつくれるかもしれません。

まず、パラメータスニッフィングが原因で突然遅くなっているかの判断基準として、「アドホッククエリで実行されていないか」があります。もしストアドプロシージャ、もしくはプリペアドステートメントを利用したクエリで、本来速いクエリが突然遅くなる場合があれば、パラメータスニッフィングが原因の可能性は十分考えられます。

その場合の最も簡単な解決策としては、ステートメントであれば「option (recompile)」をステートメント末尾に加えること。ストアドであれば「with recompile」をつけてALTERすることです。

https://blogs.msdn.microsoft.com/jpsql/2011/02/16/dosdonts-3/

→今回に関連する記事はこちらが詳しいです。

https://docs.microsoft.com/ja-jp/sql/relational-databases/stored-procedures/recompile-a-stored-procedure?view=sql-server-2017

都度リコンパイルさせることで、その時に渡されたパラメータに最適なプランが都度生成されるので、非典型パラメータによるパラメータスニッフィングが原因でクエリが遅くなることは防げます。

ただし、都度コンパイルにより若干のCPU負荷増加が懸念されます。ここはトレードオフになります。

ご自身の環境で許容できる範囲か判断してください。

まとめると、

「ストアドやプリペアドステートメントを使っていて今回の事象が発生したのであれば、都度コンパイルによるCPU負荷増を許容できるのであればoption (recomile)またはwith recompileを使って都度リコンパイルさせる」

です。CPU負荷増が許容できない場合は、クエリを書き替えてシンプルにすることでパラメータスニッフィングによる突発的なスロークエリの発生を起きにくくするというアプローチになってしまいますが、こちらは完璧に防ぎきれるものではないため、option (recompile)をおすすめします。

※もうちょっというと、「プランガイド」を使って実行プランを固定させる方法や、「optimize for ***」オプションを使ってコンパイル時のパラメータを固定化させる方法もあります。

----------------------------

※参考になりましたら投票と回答としてマークをお願いします。

Answer 2

ご意見と色々な情報ありがとうございます。

→これは事前に防ぎきるのは難しく、実現可能な範囲でいえば遅くなったことを検知し、改善する、というアプローチかと思われます。SQL Server 2016以降であればクエリストアという機能でこれを実現できますが、2014以前でもがんばれば自前でつくれるかもしれません。
とのことなので、クエリストアについて調べてみることにいたします。

今回の処理はアドホッククエリではなくプロシージャで発生しているため、やはりパラメータスニッフィングが原因である可能性が高そうです。
ただ1日辺りの実行回数が多いクエリであるため、recomplieではなく optimize for unknownにて今回は対応しようかと考えています。

ありがとうございました。

Answer 3

tanifujiさん

ご確認いただきありがとうございます。

補足ですが、optimize for ***を使う場合は以下の記事が参考になります。

https://blog.engineer-memo.com/2012/01/08/%E3%83%91%E3%83%A9%E3%83%A1%E3%83%BC%E3%82%BF-%E3%82%B9%E3%83%8B%E3%83%83%E3%83%95%E3%82%A3%E3%83%B3%E3%82%B0%E3%81%A8%E3%83%88%E3%83%AC%E3%83%BC%E3%82%B9%E3%83%95%E3%83%A9%E3%82%B04136/

この記事を見ると、optimize for unknownだと、基数推定の結果が実際には存在しないレコード数となるようですね。

このオプションで確実に基数推定の結果は変わるので、結果として生成される実行プランに変化が生じる可能性は十分あるかと思いますが、反面、ベストなプランが生成されなくなる可能性もあります。

***と省略して混乱を招き申し訳なかったのですが、

OPTION (OPTIMIZE FOR (@param1 = N”ZZZZZZ”))

→このような使い方のほうを想定して紹介させていただきました。非典型なパラメータでのコンパイルを防ぐために、典型的パラメータを渡してコンパイルさせる、というものです。

大量に実行されるクエリとのことですが、option recompileをつけてのデプロイ前後でCPU使用率があまり変化なければ、option recompileも十分実用的です。

また、判断の参考として以下を試してみてください。

１．set statistics time on　を実行

２．同一のSSMSのウインドウ上で実行

→「メッセージ」のところに、コンパイルにかかった時間が表示され、この数値が、option recompileとつけることによるオーバーヘッドとなります。

例えば、コンパイルに10msec/実行に1000msecのCPUを使っているという結果となった場合、都度コンパイルすることによるCPU使用時間の上昇は微々たるもの、という判断ができる可能性もあります。

最終的にはデプロイ前後のCPU等のリソース使用状況の変化をみて判断するのが理想的ですが。

このあたりは「必ずこうしておけばいい」という方法を経験上もちあわせていないので、どうしてもクエリごとに判断すべきかなという意見です。

もしかしたら他の方が良いアドバイスをくれるかもしれません。

がんばってください！

Answer 4

CPU負荷を図ったところ、問題が判明しているプロシージャ内の一部クエリにのみrecompileを使った場合、ほとんどCPU負荷の変化がなかったため、今回はこちらで対応することになりました。
情報ありがとうございました。