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

本連載は、「Microsoft SQL Server」で発生するトラブルを「どんな方法で」「どのように」解決していくか、正しい対処のためのノウハウを紹介します。今回は、「あるタイミングで処理遅延が発生し、それが継続して発生するようになってしまった場合の対処例.2」を解説します。

[椎名武史，ユニアデックス株式会社]

連載バックナンバー

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

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

目次

• トラブルの実例
• トラブルの原因
• 解決方法

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

トラブルの実例：ある会社の給与管理システムを運用中。これまで問題なく動作していたが、あるタイミングで突然処理が遅くなった。

　SQL Serverのパフォーマンスログを確認したが、普段より明確に大きな負荷をかける処理は実行していない。問題を切り分けて調査するために個々の処理を再実行してみた結果、遅くなっていた処理が1つだけ見つかった。ただしこの処理は、普段使用するパラメーターの場合は遅いが、別のパラメーターで実行すると問題ないことが分かった。

トラブルの原因を探る

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

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

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

参考リンク *1：sys.dm_exec_cached_plans（Transact-SQL）（Microsoft Developer Network）

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

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

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

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

目次に戻る