パラメータスニッフィングが問題であることを特定するときは、’recompile’ヒントを実装することが計画の品質に役立つかどうかをテストする必要があ （スニッフィングがどのようなパラメータであるかわからない？ このブログ記事またはこの30分の無料ビデオから学びます。）あなたが決定する必要があります：あなたはどのようなヒントやコマンドを使用し、どこにそれを置くのですか？ 将来的にSQL Serverをパフォーマンス調整できるようになると、どのようなトレードオフが発生しますか？この投稿は、SQL Serverを新しい実行計画を生成するために微調整（または強打）する必要がある一般的なオプションを実行します。 私はそれぞれの方法についていくつかの長所と短所を与え、何が有用で、何を避ける価値があるのかを説明します。

物事を比較的単純に保つために、私はこれが今日のストアドプロシージャにどのように適用されるかを議論しています–この記事では、パラメータ化 これらの分野で大きな質問がある場合は、コメント内の将来の投稿のためにそれを提案すること自由に感じてください。

免責事項：再コンパイルヒントは、誤って使用されたときに火にあなたのCpuを点灯させることによって、あなたのパフォーマ 注意して扱う！

便利な動的管理ビュー

実行プランキャッシュへの影響について説明するときは、次の二つのDmvを参照します。

• sys.dm_exec_query_stats–このDMVは、プロシージャーの一部であるかどうかにかかわらず、SQL Server上のトップステートメントを参照するのに役立ちます。 ここでサンプルクエリをチェックしてくださ
• sys.dm_exec_procedure_stats-このDMVはSQL Server2008以降でのみ使用できます。 ストアドプロシージャの実行メトリックを追跡します。 Books Onlineのサンプルクエリをチェックしてください。

これらのDmvの両方で、キャッシュ内に実行計画を持つことは、実行メトリックを見ることができることにリンクされています：合計実行数、合計 再コンパイル、メモリの負荷、再起動、またはその他のアクションのために実行プランがキャッシュから削除されると、関連する実行メトリクスも削除されます。

実行時の再コンパイルヒントを使用してプロシージャを実行する

私が迅速で簡単なテストのために愛する一つのオプションは、実行時に再コンパイルヒントを使用してストアドプロシージャを呼び出す機能です。 コンパイルされたコードを変更する必要がないので、これは素晴らしいことです。 あなたのヒントは、実行時にやっていることにも適用されます。これを行うには、次のような構文を使用します:

However, there’s a little bit of a gotcha. If you have nested stored procedures, the recompile hint only applies to code in the outermost procedure. ストアドプロシージャ自体内のサブプロシージャへの呼び出しでは、パラメータスニッフィングを取得できます。

だから私はテストのために時々これが好きですが、ストアドプロシージャにネストがある場合は、本当に新鮮な実行計画を得るのに役立たないかも

sp_recompileシステムストアドプロシージャの使用

これは、受信セッションによって悪い計画が繰り返し使用されている場合に、運用インシデント You can force recompilation of plans by running sp_recompile against an object– most common choices are a table or a stored procedure. The command looks like this (a table in this example):

これを実行すると、関連する計画はSQL Serverの実行プランキャッシュからすぐに削除されません。 代わりに、再コンパイルされたオブジェクトを参照するクエリが次回実行されたときに魔法が発生します。 この時点で、sys.dm_exec_query_stats内の既存の実行統計は、プラン内のステートメントに対してリセットされます。

ストアドプロシージャが再コンパイルに使用するテーブルをマークした場合、sys.dm_exec_procedure_statsの実行数は、次回の使用時に必ずしもリセットされません。 ただし、ストアドプロシージャ自体を再コンパイル用にマークすると、次回の使用時にリセットされます。 （これを暗記しようと気にしないでください、ちょうど次の段落を読んでください。）

これには大きな欠点があります。 このコマンドを実行するには、テーブルに対する’alter’権限が必要です。 それはまた完了するためにロックの高レベルを要求する。 忙しいシステムでは、私はこのコマンドがいくつかの厄介なブロッキングチェーンに参加するのを見てきました。 それに注意してください！

これはトラブルシューティングやテストに役立つことがありますが、本番コードの一部にしないでください。

ストアドプロシージャヘッダー内のヒントを再コンパイル

新しいプランを生成するプロシージャがある場合は、プロシージャ内でヒントを使 ほとんどの人が最初に発見するオプションは、ストアドプロシージャのヘッダーで次のように’WITH RECOMPILE’を使用することです:

ストアドプロシージャヘッダーでRECOMPILEを使用することはかなり劇的です。 これは、

• レビューするキャッシュに実行計画がない
• sys.dm_exec_query_statsに記録された実行統計がない
• sys.dm_exec_procedure_statsに記録された実行統計がない

この手順がサーバーに与える影響を特定する必要がある場合は、ある種のトレースまたは拡張イベントセッションを実行し、結果を収集して解釈する必 これは迅速ではなく、トレースを実行するとパフォーマンスに影響を与えます。大きな負の種類。

そのため、私はこの選択があまり好きではありません。

個々のステートメントのヒントを再コンパイル

このオプションはもう少し作業ですが、時間の経過とともにはるかに良いペイオフがあります。 With this option you take the RECOMPILE hint and apply it only to statements in the stored procedure where you’d like to implement the recompile, like this:

ヘッダーではなくプロシージャ内のすべてのステートメントにヒントを使用する必要がある場合でも、これ Procの文レベルでrecompileヒントを置くことによって、あなたは魔法のように得ます:

• キャッシュ内の制限された実行プラン（最後の実行）を確認する
• Sys.dm_exec_query_statsに記録された制限された実行統計最後の実行の統計のみを取得しますが、plan_generation_num列はインクリメントされ、少なくとも何かがアップしていることを洞察します。
• sys.dm_exec_procedure_statsに記録された実行統計

これらのDmvでいくつかの情報を取得するという事実は、時間の経過とともに非常に便利になります。 再コンパイルヒントを必要とするステートメントにのみ適用することも、より責任があります–CPUの書き込みで時間の経過とともに自分自身を燃や

DBCC FREEPROCCACHE–核オプション

これは厳密には再コンパイルのヒントではありませんが、確かに再コンパイルが発生します。 SQL Serverには、「新しい実行計画で新たに開始する」ことを指示するために使用できるコマンドがあります。 通常は次のように使用されます:

This command makes everything start fresh– for every query:

• 新しいプランがコンパイルされるとCPU使用率が上がる可能性があります
• 実行統計はsys.dm_exec_query_statsでクリアされます(すぐに)
• 実行統計はsys.dm_exec_procedure_statsでクリアされます(すぐに)

このコマンドは悪ではありません–それを使用するときのトラブルシューティングに時間と場所があり、パフォーマンス問題の根本原因をすばやく見つけるのに役立ちます。 ただし、キャッシュ全体に影響を与え、パフォーマンスにも影響する可能性があるため、使用には非常に注意する必要があります。 通常のジョブ、自動化されたプロセス、または本番コードでは、このコマンドを使用することはお勧めしません。

SQL Server2008では、このコマンドをもう少し優しく使用できます–”plan handle”または”sql handle”を使用してキャッシュから特定のプランを削除できますが、もちろん、その これは、いくつかのニッチな状況で有用になる可能性がありますが、実際にはあまり出てきません。 また、リソースガバナプールをクリアすることもできますが、リソースガバナプールを使用する必要があります。それはすべてどういう意味ですか？

テストしているときは、ツールキットに’EXEC procedure WITH RECOMPILE’とsp_recompileプロシージャの両方の場所があります。 これらは注意して使用し、特にsp_recompileでの影響の可能性に注意してください。

コードでヒントを実装しているときは、RECOMPILE blindersをオンにしないでください。’OPTIMIZE FOR’ヒントを使用することもできます（時には平凡なものに最適化している 時には、動的sqlも役立ちます。

しかし、再コンパイルヒントを使用する場合は、プロシージャのヘッダーではなく、ステートメントレベルで再コンパイルを維持してください。

詳細については、Parameter Sniffingクラスの基礎をチェックしてください。