SQL Server では インテリジェントなクエリ処理 として、バージョン / 互換性レベルに応じてクエリ最適化が自動的に行われる機能が実装されています。
この中で、「互換性レベル 150 以上」で有効化される「行ストアでのバッチモード」があります。この最適化機能に起因して、クエリの実行効率が極端に悪化する可能性があるのかについて、確認した情報をまとめておきたいと思います。
SQL Server のクエリ処理のアーキテクチャを把握するためのドキュメント
クエリ オプティマイザーの基本的なアーキテクチャ
SQL Server のクエリ処理の根幹部分は、クエリ オプティマイザーとなるのではないでしょうか。
「クエリの実行効率について確認」をする場合には、「どのようにして、その実行プランが生成されたのか」を調査する必要があり、この調査には、クエリ オプティマイザーの挙動について確認をする必要があります。
クエリ オプティマイザーについて確認が必要となった場合には、次のドキュメントを確認することになります。
「1.」は Learn で公開されている アーキテクチャ ガイド のクエリに関してのドキュメントとなります。
アーキテクチャガイドでは様々な構成要素についての実装が公開されており、クエリ オプティマイザーの基本的なアーキテクチャを確認する際には、このドキュメントの内容を参照することが多いです。
「2.」は、Microsoft Reserch で公開されているクエリ オプティマイザーについての解説となります。
Learn のドキュメントでは触れていない Memo の情報についても触れられており、Learn のドキュメントでは、把握できないクエリ オプティマイザーの詳細な情報についても確認をすることができます。
バッチモードについて
クエリ処理アーキテクチャ ガイド の実行モードに記載されていますが、バッチモードは SQL Server がデータにアクセスするときの処理モードの一つであり、「行モード」「バッチモード」のいずれかが使用されます。
行ストアでのバッチ モード に動作が記載されていますが、確認したい内容によっては、列ストアインデックスのドキュメントを確認する必要もあります。
バッチモードは当初は列ストアインデックスで実装されたものであるため、バッチモードの挙動については、列ストアインデックスのドキュメント の確認が必要なケースもあります。この、ドキュメント内の バッチ モード実行 についての記載を確認する機会も多いです。
- バッチモードで処理される行数 (最大 900 行)
- バッチモードで実行される演算子
これら 2 種類の情報については列ストアインデックスのドキュメントから確認したほうが詳細を把握することができます。
行ストアでのバッチモードについては、BATCH_MODE_ON_ROWSTORE で無効化することも可能であり、互換性レベルを 150 以上にした場合でも DB レベルで明示的に無効化することも可能です。
クエリ オプティマイザーのタイムアウトについて
SQL Server のクエリ オプティマイザーによるクエリプランの生成には、オプティマイザー タイムアウトという概念があり、詳細については次のドキュメントに記載されています。
実行プランに「 StatementOptmEarlyAbortReason="TimeOut"」が表れている場合、オプティマイザーがクエリ最適化を行うためのタスク数の閾値を超えたため、最適化を最後まで完了せずに途中で終了させたことを示しています。
タイムアウトが発生した場合、実行プラン生成のための最適化が最後まで完了していないため、最適なプランが選択されていない可能性があります。
オプティマイザーのタイムアウトは時間ではなくタスク数で閾値が決められており、通常は、50 万程度のタスクが生成されると、タイムアウトになる可能性があるようです。
プラン最適化
SQL Server のクエリの最適化は次の 3 段階に分かれており、Trivial Plan でない場合は、このステップでクエリの最適化が行われていきます。
- Search 0: Transaction Processing (TP)
- Search 1: Quick Plan (QP)
- Search 2: Full Optmization
クエリが Trivial Plan で実行プランが生成されたのかは、実行プラン内のプロパティからも確認できますが、 sys.query_store_plan にも記録されているため、クエリストアが有効になっている場合は、クエリストアからも確認をすることができます。
Search 0 / 1 / 2 については、どの探索深度まで最適化が行われているかについては、sys.dm_exec_query_optimizer_info で確認ができます。
また、Search 0 / 1 で最適なプラン (各フェーズの閾値の以下プランが生成できた) を見つけられた場合は、次の探索までは進まずに現在の探索で最適化が終了することもあります。
プランの最適化については、次の TF で制御することができるため、最後まで最適化した場合のどのようなプランが生成されるのかを把握したい場合などはこれらの TF を活用できます。
- TF8757: Trivial Plan によるプラン最適化スキップの無効化
- TF8780: タイムアウトによるプラン最適化の中断を無効
- TF8671: GoodEnoughPlanFound によるプラン最適化の早期終了の無効化
クエリ最適化に伴うルールの確認
バッチモードでの実行についての判断に影響しそうな最適化ルールがあるかについては、次の情報から確認することができます。
- sys.dm_exec_query_transformation_stats
- DBCC SHOWONRULES
このルールからバッチに関係しそうなルールが存在するかを確認してみると、次のようなルールが存在しているようでした。
- GenKeyBatch
- EnforceBatch
- BuildKeyBatchApply
- ExpandNAryJoinToBatchSnowflake
Batch を含むルールの確認を実施しているため、今回対象としているバッチモードと関連性があるのかについて、厳密な確認はできていません。
バッチモードによるクエリ最適化への影響
クエリ最適化については、上述のような情報をもとにして、各情報がバッチモードに影響を与える内容なのかを整理していく必要があるかと思います。
「クエリ オプティマイザーのタイムアウト」については、バッチモードが増えることにより「タイムアウトに達する可能性」は高くなるのではないでしょうか・
バッチモードが有効な場合、クエリ最適化で実行されるタスクが多くなるため、クエリ オプティマイザーのタイムアウトの発生については、高くなる可能性はあるのではないでしょうか。
バッチモードが実装されていなかった互換性レベルで、タイムアウトの閾値のタスク数に該当するかどうかのボーダーラインだったクエリについては、バッチモードにより比較対象となるタスクが増えることでタイムアウトが発生する可能性があるのではないでしょうか。
(バッチモードにより探索空間に変化があることは 行ストアでバッチ モードを使用すると何が変わるでしょうか? にも記載されており、私が確認できたのはタスクの増加傾向ですが減少についても考慮をする必要があるかと)
Seek/Scan / 使用されるインデックス / 結合方法の判断の観点で、バッチモードが有効なことが大きく影響を与える可能性があるかは判断が難しいのではと考えています。
行モード / バッチモードを比較すると、いくつかの演算子ではコストが低くなるということはあるようです。
Hash Join / Sort の CPU コストはバッチモードで実行されることで CPU の推定コストが下がり、バッチモードの採用の有無でプランへの影響が発生する可能性はあるようです。
しかし、Seek/Scan や使用されるインデックスの判断については、パラメータースニッフィングのほうがプランに与える影響は大きいのではないでしょうか。