DO's&DONT's #3: やらなければいけないこと - 非典型的パラメータ値が存在する場合の再コンパイル

  • Article

神谷 雅紀
SQL Server Escalation Engineer

 

非典型的パラメータの問題

 

非典型的パラメータ値に起因する問題があります。これは、ストアドプロシージャやパラメータ化クエリなどに、典型的ではない、普段使わないような特殊な値のパラメータ値が渡され、その値を用いてコンパイル及び最適化された実行プランがキャッシュされることによって引き起こされる問題です。この問題は、パラメータとして渡された値によって、検索範囲や結果セットが大きく変わる場合に発生します。このような実行プランは、parameter sensitive plan と呼ばれることがあります。また、このような非典型的なパラメーターに起因する問題は、atypical parameter problem と呼ばれることがあります。

 

それによって、何が起こるのか？

 

パフォーマンスの問題が発生します。

以下のようなケースを考えてみましょう。

「休業日のデータを検索した。その後、普段は時間のかからない営業日のデータ検索に時間がかかるようになった。」
「本店で、全支店のデータ検索が行われた。その後、支店別のデータを検索すると、いつもに比べて非常に時間がかかる。」

ストアドプロシージャやクエリが実行される時、まず、それらの実行プランが既にキャッシュされているかどうかが確認されます。キャッシュされていれば、キャッシュされている実行プランを用いて、処理が実行されます。キャッシュされていなければ、指定されているパラメータ値を使用して、コンパイル及び最適化がおこなわれ、実行プランが生成されます。その実行プランはキャッシュされます。
先の例では、キャッシュされている実行プランがなければ、「休業日のデータ検索」のためにストアドプロシージャを実行した時に、「休業日のデータ検索」に最適な実行プランが生成され、その実行プランはキャッシュされます。それ以降、このストアドプロシージャが実行される時には、キャッシュされている「休業日のデータ検索」に最適な実行プランを用いて実行されます。その実行プランが「営業日のデータ検索」にも最適であればパフォーマンスの問題は発生しません。しかし、「休業日のデータ検索」には最適であっても、「営業日のデータ検索」には最適でなければ、言い換えれば、「休業日のデータ検索」の時に指定されたパラメータが非典型的であれば、「営業日のデータ検索」にはパフォーマンスの問題が発生することになります。

 

どのように対応するか？

 

非典型的なパラメータが存在すると予想される場合には、実行ごとにコンパイル及び最適化することを指定します。そのための方法としては、以下の方法があります。

  • ステートメントに OPTION(RECOMPILE) を指定する。 (SQL Server 2005 以降のみ)

SELECT * FROM tabname WHERE colname = 123
OPTION(RECOMPILE)

  • ストアドプロシージャの場合には、 ストアドプロシージャを WITH RECOMPILE 指定で作成する。

CREATE PROCEDURE p (@param1 int) WITH RECOMPILE
AS
SELECT …

  • ストアドプロシージャ実行時に RECOMPILE オプションを指定する。

EXEC usp_p WITH RECOMPILE

非典型的なパラメータが存在するかどうかはどのように確認するか？

 

これを見れば分かるといった明確な方法はありません。そのシステムの作成者や管理者が、テーブルに格納されているデータやそのデータに対するアクセス方法、アクセス傾向から非典型的なパラメータ値が存在するかどうかをを予測します。