実行プランを読む - 活用編 (その 2) - クエリパフォーマンス悪化の原因

  • Article

 

神谷 雅紀
Escalation Engineer

今回は、急に実行プランが変わったと思われる場合に、確認すべき実行プラン中の項目をひとつ紹介します。

 

同じクエリの実行プランが変わるということは、最適化時 (実行プラン選択時) に参照されるデータが変わったということです。参照データのひとつであるクエリ内のパラメータ値は、ParameterCompiledValue で確認可能です。

 

ParameterCompiledValue

パラメータ化クエリやストアドプロシージャの実行時に、XML 実行統計 (SET STATISTICS XML ステートメント や Showplan XML Statistics Profile イベント) を採取すると、最適化時に使用されたパラメータ値と実行時に使用されたパラメータ値が含まれています。キャッシュされている実行プランが再利用された場合、これらの値は異なる可能性があります。

例
<ParameterList>
<ColumnReference Column="@p1" ParameterCompiledValue="(10)" ParameterRuntimeValue="(50000)" />
</ParameterList>

ParameterCompiledValue がこの実行プラン選択時に指定されていたパラメータ値です。ここに示される値を用いて、実行プランが変わった原因がパラメータ値にあるのかどうかを切り分けることができます。

DBCC FREEPROCCACHE で対象クエリの実行プランをプロシージャキャッシュから削除した後に、ParameterCompiledValue に示されているパラメータ値でクエリを実行した場合には想定しない実行プランが生成され、ParameterCompiledValue に示されている値ではない典型的な値で実行した場合には想定どおりの実行プランが生成されるようであれば、問題の実行プランとなった原因はパラメータ値にあることになります。

この確認を行う際に注意しなければならないのは、クエリを実行する接続の SET オプションです。 SET オプションが異なれば、同じクエリを実行しても、実行プランは異なります。実行プラン選択時に指定されていた SET オプションは、XML 実行統計内の StatementSetOptions に示されます。

例
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />

 

ParameterCompiledValue に示されているパラメータ値が、通常は指定されないような非典型的なパラメータ値である場合には、DO's&DONT's #3: やらなければいけないこと - 非典型的パラメータ値が存在する場合の再コンパイル で取り上げた問題が発生するため、その問題を避けるための対応が必要です。

尚、最適化時に参照されるもうひとつのデータである統計情報が変わったかどうかは、stats_date 関数や DBCC SHOW_STATISTICS で統計情報の更新日時を確認すれば分かります。