「パラメータースニッフィング」によって、あるタイミングから処理が遅くなった（パフォーマンストラブル）：SQL Serverトラブルシューティング（51）（1/2 ページ）

連載バックナンバー

　本連載では、「Microsoft SQL Server（以下、SQL Server）」で発生するトラブルについて、「なぜ起こったか」の理由とともに具体的な対処方法を紹介していきます。

トラブル 38（カテゴリー：処理遅延）：「パラメータースニッフィング」によって、あるタイミングから処理が遅くなった

　「Windows Server 2012 R2」上に「SQL Server 2016 RTM」をインストールした環境を想定して解説します。

トラブルの原因を探る

　SQL Serverでクエリを実行するには、以前に紹介したように実行プランを作成する必要があります。また、クエリを実行するたびに新規に実行プランを作成するのではオーバーヘッドが大きいため、作成された実行プランをメモリにキャッシュして、再利用することで効率を高めています。

　ただし、「パラメーター」と呼ばれる変数を含む実行プランがキャッシュされている場合には、仮にパラメーターがどのような値だとしても、SQL Serverでは同一の処理としてキャッシュされた実行プランを使うように動作します。大抵は誤差の範囲ですが、もしテーブルに格納されているデータが極端に偏っているような場合には、このシステムに最適な実行プランが違ってしまうことになります。

　まず、そのことをチェックしましょう。SQL Serverの内部動作を調査するツール「DMV（Dynamic Management View）」で「dm_exec_cached_plans（*1）」を実行します。dm_exec_cached_plansで、キャッシュされている実行プランの中身を確認できます（図1）。

図1　DMVで「dm_exec_cached_plans」を実行したところ

　dm_exec_cached_plansを実行して中身を確認したところ、平均給与を求めるストアドプロシージャ「sp1」のパラメーター「役職」が「社長」であるという条件で実行プランが作成されていたことが分かりました。

　この会社には、1人の社長と多数の社員や役員が存在します。1人しかいない「社長」に最適な実行プランがキャッシュされているということは、その他多くの一般社員の処理には「効率的ではない実行プランで動作してしまっている」ことになるでしょう。

　今回のトラブル事例では、パラメーターによって最適な実行プランが異なるシステムであること、そして、特定のパラメーターだけに最適な実行プランが使用されたことが原因でした。この現象は、「パラメータースニッフィング」などと呼ばれます。