SQL Server のクエリ実行時の実行プラン生成の最適化の早期終了について at SE の雑記

SQL Server ベースの環境でクエリ実行をした際の実行プラン生成の最適化の早期終了についてまとめておきたいと思います。

最適化の早期終了が発生するケース

SQL Server でクエリを実行した際の実行プラン生成ですが、次のような 3 段階の最適化がフェーズが提供されています。

クエリの内容に応じて、Search 0 ～ 2 のフェーズまで最適化が行われますが、最適化の状況によっては早期に最適化が終了することがあります。

SQL Server では最適化の早期終了の要因としては次のようなものがあります。

これは、条件がシンプルなクエリ (例: SELECT * FROM LINEITEM WHERE L_ORDERKEY = 1) の場合に発生することがある、クエリ最適化の早期終了となります。

このケースに該当した場合、Search 0 ～ 2 を実施することなく、実行プランの生成を行います。

これは、実行プランを生成する際に、Search 0 / 1 において、一定のコストを下回る実行プランが生成できた場合に、プランの最適化を終了するケースとなります。

SQL Server は実行プランを生成する際に、オプティマイザがいくつものルールを適用しながら最適なプランの探索を行います。

この際、各フェーズにおいて一定のコストを下回る実行プランが生成できた場合には、そこで最適化を停止し、そこまでのルール適用で実行プランを為政するというものとなります。

これは、実行プランを生成する際に、クエリの最適化状況に応じたタスク数を超えた場合に発生するものとなり、SQL Server の互換性レベル変更に伴い行ストアでのバッチモードが増えたことで実行効率が極端に悪化する可能性はあるのか？でも触れたものとなります。

SQL Server のオプティマイザでは、処理の状況に応じて、クエリ最適化で実行できるタスク数の上限があり、その上限に達した場合は最適化をタイムアウトとし、それまでの最適化内容で実行プランの生成を行います。

タイムアウトという名称となっていますが、時間ではなくタスク数となるというのがポイントではないでしょうか。

オプティマイザはこのようなケースで最適化を最後まで実施せず、途中までの最適化内容でクエリの実行プランの生成を行います。

SQL Server では、上記のような最適化の早期終了発生する可能性がありますが、これらの早期終了はトレースフラグで制御ができるようになっています。

これらのトレースフラグを設定することで、シンプルなクエリでも Full のレベル (StatementOptmLevel="FULL") で最適化を行わせることができます。

アンドキュメントなトレースフラグとなるため、運用環境での積極的な利用は推奨されていないものとなりますが、クエリの最適化が早期に終了している場合に、最後まで最適化を実施した場合にどのようなプランが生成されるのかをトレースフラグを活用することで確認することができます。

生成されたプランによってはクエリヒントで補正することで、トレースフラグを使用しなくても Full の最適化が行われた場合の実行プランに使づけることができる可能性がありますので、このような方法があるということも把握しておくと、役に立つときがあるのではないでしょうか。