SQL Server のクエリオプティマイザーを学習するための資料 at SE の雑記

SQL Server のクエリの最適化を行うための、コンポーネントであるクエリオプティマイザーについて学習しようと思った際にはどのような資料を確認すればよいでしょうか？

公式のドキュメントや、公開されているドキュメントでかなり Deep なものがあり、この機会に公開情報をまとめておきたいと思います。

SQL Server のクエリオプティマイザーの情報というと「実行プラン」から確認するというようなイメージを持つことがあるかもしれませんが、Tree / Memo / 変換ルールというような情報を確認することで、クエリオプティマイザーに一歩踏み込んだ学習をすることができます。

Contents

1 公式ドキュメント
2 公開ドキュメント
3 Tree / Memo からクエリオプティマイザーの情報を確認する
- 3.1 Logical operator tree の確認
- 3.2 Physical operator tree の確認
4 変換ルール
5 まとめ

公式ドキュメント

MS から公開されているドキュメントとしては次のようなものがあります。

SQL Server の内部動作については SQL Server ガイドというドキュメントツリーで公開されており、内部アーキテクチャを確認したい場合には、このドキュメントツリー内の情報から確認をすることが多いです。

この中で、SQL Server のクエリの実行がどのように行われるかについては、クエリ処理アーキテクチャガイドで解説が行われており、このドキュメントが公式ドキュメントとしては一番詳細に情報が記載されているものではないでしょうか。

クエリを実行する際には、Transact-SQL をクエリオプティマイザーが実行された SQL を解析し、実行プランを生成するというのは、よく見る話かと思います。
クエリ処理アーキテクチャガイドではこのような動作についても解説が行われています。

オプティマイザが生成したクエリの実行プラン (実行計画) については、実行プランの表示と保存 / プラン表示の論理操作と物理操作のリファレンスの情報から確認することができます。

クエリチューニングを行う際には、実行プランの表示を行い、効率の悪い箇所をチューニングする必要があります。

実行プランの各操作がどのような内容を示しているかを確認するには、前述のドキュメントを確認します。

実行プランの中には、様々な情報が含まれています。
次の画像は、実行プランの情報の一部を抜粋したものになります。

CompileCPU や CompileMemory のような情報がありますが、単純な数値だけですと、「これらの情報の単位は何なのか？」を判断することができません。

実行プランは XML で表現されており、XML のスキーマの定義については Showplan Schema で公開されています。
このスキーマの情報には、次のような記載があります。

New Runtime information: 
DegreeOfParallelism EffectiveDegreeOfParallelism: 
Max parallelism used by columnstore index build MemoryGrant (in kilobytes) 

New compile time information: mem fractions 
CachedPlanSize (in kilobytes) 
CompileTime (in milliseconds) 
CompileCPU (in milliseconds) 
CompileMemory (in kilobytes) Parameter values used

これらの情報から、各項目の単位を確認することができますので、実行プランの分析を行う際には、これらの情報も合わせて確認をすると良いのではないでしょうか。

公開ドキュメント

公式ドキュメントについては、前の段落で紹介したようなものがあります。

クエリの処理を把握するためには、公式ドキュメントの内容でも足りると思いますが、「クエリオプティマイザーを学習する」という観点ですと、私は、公式ドキュメントでは不足しているのではないかと考えています。

SQL を実行する際には、次のような流れで処理が行われます。

クエリオプティマイザーの動作を学習するためには、Query Optmization のフェーズの情報をどれだけ確認できるかが重要です。

公式のドキュメントでは記載は少ないのですが、Undocumented なトレースフラグ (or クエリトレース) を使用することで、クエリ最適化の情報を取得するという手法があります。

この手法については、海外ではたくさんの記事が公開されているのですが、First Step としては、次の記事から確認をすると良いかと思います。

(First Step と書いていますが、内容を理解するためには、SQL Server の中級者程度の理解は必要となり。初学者向けの内容ではありません)

クエリオプティマイザーの動作については、PASS Summit 2010 Keynote David DeWitt を最初に確認すると良いのではないでしょうか。

これは、PASS Summit 2010 で David DeWitt が使用したセッション資料になりますが、この資料の中の次のスライドが、クエリオプティマイザーの理解をするための最初のポイントとなります。

SQL Server では、論理操作 (Logical Operators) と物理操作 (Physical Operators) の 2 種類の操作があります。

これらの操作については、プラン表示の論理操作と物理操作のリファレンスで、次のように記載されています。

論理演算子

論理操作は、ステートメントの処理に使用される関係代数操作を表します。つまり、論理操作は、どのような操作を実行する必要があるかを、概念的に示します。

物理操作

物理操作では、論理操作によって示される操作が実装されます。それぞれの物理操作は、操作を実行するオブジェクトまたはルーチンです。たとえば、一部の物理操作は、テーブル、インデックス、またはビューから、列や行にアクセスします。他の物理操作は、計算、集計、データ整合性チェック、結合などの他の操作を実行します。物理操作には、それぞれ関連するコストがかかります。

論理操作 / 物理操作については、実行プランからも確認することができます。

論理操作は、「結合」というような「どのような操作」を行うかを表し、Inner Join のような操作が該当します。

物理操作は、「論理操作をどのように実施するか」を表し、Nested Loops のような Join をどのように処理するのかが該当します。

このような論理操作を表す Logical operator tree / 物理操作を表す Physica operator tree の情報を確認することがクエリオプティマイザーを学習するためには重要となります。

実行プランから確認できるのは、クエリオプティマイザーが最適化した後の Physical operator tree となり、その前段となる Logical operator tree については、実行プランから確認することはできません。

それでは、どのような方法をとれば、これらの Tree の情報を詳細に確認することができるでしょうか？

その答えが、More Undocumented Query Optimizer Trace Flags / Secrets of the Query Optimizer Revealed の記事で公開されている内容です。

SQL Server では、公式ドキュメントでは記載れていない Undocumented なトレースフラグが存在しています。

このドキュメント化されていないトレースフラグを使用することで、Tree や Memo ( メモ / Memo Structure : 検索データ構造体であり、SQL Server のクエリオプティマイザによって生成 / 分析された代替案を格納されるために使用される) と呼ばれる、クエリオプティマイザーを理解するために重要な情報を確認することができます。

Tree / Memo からクエリオプティマイザーの情報を確認する

本投稿の最後として、Tree / Memo の情報を確認してみましょう。

詳細については、More Undocumented Query Optimizer Trace Flags / Secrets of the Query Optimizer Revealed の記事を参考にしていただければと思います。

今回、使用するクエリは、次のようなシンプルなクエリです。

SELECT COUNT(*) FROM CUSTOMER
INNER JOIN NATION ON N_NATIONKEY = C_NATIONKEY

このクエリをベースとして、David DeWitt の次のスライドの内容の流れを見ていきます。

Logical operator tree の確認

クエリを実行すると Parser により構文のチェックが行われ、構文に問題がなければ、Binding により、オブジェクトの存在や、クエリを実行したユーザーがアクセスできるオブジェクトなのかというような判断が行われます。

構文 / オブジェクトの妥当性が確認できると、Logical operator tree が生成されます。

それでは実際に Logical operator tree を見ていきましょう。

SELECT COUNT(*) FROM CUSTOMER
INNER JOIN NATION ON N_NATIONKEY = C_NATIONKEY
OPTION(RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8606)

先程のクエリに「OPTION(RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8606)」を追加して実行しています。

クエリオプティマイザーの最適化を動作させるためには、オプティマイザーを動作させる必要がありますので、クエリのコンパイルを発生させていています。

これは、確認をしたいクエリの初回コンパイルが発生するようでしたら明示的に指定することは不要です。

(RECOMPILE オプションを設定すると、実行プランが想定のものから変化する可能性がありますので、パラメータークエリのようなものを確認する場合は、該当のクエリがキャッシュされていない状態にするのが望ましいです)

オプティマイザーによる最適化が動作する状態が作れれば、Logical operator tree の情報を出力することができます。

この情報を出力するのが「QUERYTRACEON 8606」です。

このトレースフラグを指定することで、Logical operator tree の情報を出力することができます。

「QUERYTRACEON 3604」も合わせて指定を行うことで、Logical operator tree の情報を SSMS のメッセージに出力することができます。

実際の出力内容がこちらになります。

****************************************
*** Input Tree: ***
        LogOp_Project COL: Expr1005 
            LogOp_GbAgg OUT(COL: Expr1005 ,)
                LogOp_Select
                    LogOp_Join
                        LogOp_Get TBL: CUSTOMER CUSTOMER TableID=965578478 TableReferenceID=0 IsRow: COL: IsBaseRow1001 
                        LogOp_Get TBL: NATION NATION TableID=885578193 TableReferenceID=0 IsRow: COL: IsBaseRow1003 
                        ScaOp_Const TI(bit,ML=1) XVAR(bit,Not Owned,Value=1)
                    ScaOp_Comp x_cmpEq
                        ScaOp_Identifier QCOL: [tpch].[dbo].[NATION].N_NATIONKEY
                        ScaOp_Identifier QCOL: [tpch].[dbo].[CUSTOMER].C_NATIONKEY
                AncOp_PrjList 
                    AncOp_PrjEl COL: Expr1005 
                        ScaOp_AggFunc stopCount Transformed
                            ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=0)
            AncOp_PrjList 
*******************
*** Simplified Tree: ***
        LogOp_GbAgg OUT(COL: Expr1005 ,)
            LogOp_Join
                LogOp_Get TBL: CUSTOMER CUSTOMER TableID=965578478 TableReferenceID=0 IsRow: COL: IsBaseRow1001 
                LogOp_Get TBL: NATION NATION TableID=885578193 TableReferenceID=0 IsRow: COL: IsBaseRow1003 
                ScaOp_Comp x_cmpEq
                    ScaOp_Identifier QCOL: [tpch].[dbo].[CUSTOMER].C_NATIONKEY
                    ScaOp_Identifier QCOL: [tpch].[dbo].[NATION].N_NATIONKEY
            AncOp_PrjList 
                AncOp_PrjEl COL: Expr1005 
                    ScaOp_AggFunc stopCount Transformed
                        ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=0)
*******************
*** Join-collapsed Tree: ***
        LogOp_GbAgg OUT(COL: Expr1005 ,)
            LogOp_Join
                LogOp_Get TBL: CUSTOMER CUSTOMER TableID=965578478 TableReferenceID=0 IsRow: COL: IsBaseRow1001 
                LogOp_Get TBL: NATION NATION TableID=885578193 TableReferenceID=0 IsRow: COL: IsBaseRow1003 
                ScaOp_Comp x_cmpEq
                    ScaOp_Identifier QCOL: [tpch].[dbo].[CUSTOMER].C_NATIONKEY
                    ScaOp_Identifier QCOL: [tpch].[dbo].[NATION].N_NATIONKEY
            AncOp_PrjList 
                AncOp_PrjEl COL: Expr1005 
                    ScaOp_AggFunc stopCount Transformed
                        ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=0)
*******************
*** Tree Before Project Normalization ***
        LogOp_GbAgg OUT(COL: Expr1005 ,)
            LogOp_Join
                LogOp_Get TBL: CUSTOMER CUSTOMER TableID=965578478 TableReferenceID=0 IsRow: COL: IsBaseRow1001 
                LogOp_Get TBL: NATION NATION TableID=885578193 TableReferenceID=0 IsRow: COL: IsBaseRow1003 
                ScaOp_Comp x_cmpEq
                    ScaOp_Identifier QCOL: [tpch].[dbo].[CUSTOMER].C_NATIONKEY
                    ScaOp_Identifier QCOL: [tpch].[dbo].[NATION].N_NATIONKEY
            AncOp_PrjList 
                AncOp_PrjEl COL: Expr1005 
                    ScaOp_AggFunc stopCount Transformed
                        ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=0)
*****************************************
*** Tree After Project Normalization ***
        LogOp_GbAgg OUT(COL: Expr1005 ,)
            LogOp_Join
                LogOp_Get TBL: CUSTOMER CUSTOMER TableID=965578478 TableReferenceID=0 IsRow: COL: IsBaseRow1001 
                LogOp_Get TBL: NATION NATION TableID=885578193 TableReferenceID=0 IsRow: COL: IsBaseRow1003 
                ScaOp_Comp x_cmpEq
                    ScaOp_Identifier QCOL: [tpch].[dbo].[CUSTOMER].C_NATIONKEY
                    ScaOp_Identifier QCOL: [tpch].[dbo].[NATION].N_NATIONKEY
            AncOp_PrjList 
                AncOp_PrjEl COL: Expr1005 
                    ScaOp_AggFunc stopCount Transformed
                        ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=0)

Logical operator tree の生成ですが、いくつかのステップで実行されます。

この中では Join Elimination 等も行われるのですが、今回ポイントとしたいのは「論理操作」です。

前段で「論理操作は、「結合」というような「どのような操作」を行うかを表し、Inner Join のような操作が該当します。」と記載しました。

今回表示した内容では、次の箇所が該当します。

LogOp_Join
    LogOp_Get TBL: CUSTOMER CUSTOMER TableID=965578478 TableReferenceID=0 IsRow: COL: IsBaseRow1001 
    LogOp_Get TBL: NATION NATION TableID=885578193 TableReferenceID=0 IsRow: COL: IsBaseRow1003 
    ScaOp_Comp x_cmpEq
        ScaOp_Identifier QCOL: [tpch].[dbo].[CUSTOMER].C_NATIONKEY
        ScaOp_Identifier QCOL: [tpch].[dbo].[NATION].N_NATIONKEY

論理操作として Join (LogOp_Join : 論理操作 : 結合) が実行され、どの項目を使用して結合するかは判断されていることが確認できますね。

実行プランの論理操作は Inner Join となっており、結合を行うということは QUERYTRACEON 8606 の出力内容からも確認できます。

この段階では「物理操作」については、生成されておらず、Logical operator tree の情報からは、「どのような操作で該当の結合を行うか (Nested Loop / Hash Match / Merge)」までは、確認を行うことはできません。

どのような物理操作を行うかはクエリオプティマイザーにより最適化されます。

Physical operator tree の確認

Physical operator tree はオプティマイザによって生成された最終的な物理操作の Tree となるかと思います。

今回確認をするのは、Physical operator tree を生成する際の、クエリオプティマイザーの動作になると思いますので、Physical operator tree の確認というのは厳密には誤りだと思うのですが、「どのような物理操作が行われるか？」の情報を次に確認してみます。

先ほどのクエリを次のように変更して実行します。

SELECT COUNT(*) FROM CUSTOMER
INNER JOIN NATION ON N_NATIONKEY = C_NATIONKEY
OPTION(RECOMPILE, QUERYTRACEON 3604, QUERYTRACEON 8615)

「QUERYTRACEON 8615」を指定することで「Final Memo Structure」の情報を出力することができます。

****************************************
--- Final Memo Structure ---
Group 29: Card=7.5e+06 (Max=8.25e+06, Min=0)
   0 LogOp_GetIdx (Distance = 1)
Group 28: Card=1 (Max=1, Min=0)
   1 PhyOp_Range 1 ASC  2.0  Cost(RowGoal 0,ReW 0,ReB 24,Dist 25,Total 25)= 0.0070775 (Distance = 5)
   0 LogOp_SelectIdx 27 2 (Distance = 4)
Group 27: Card=26 (Max=10000, Min=0)
   0 LogOp_GetIdx (Distance = 4)
Group 26: Card=25 (Max=10000, Min=0)
   9 PhyOp_ExecutionModeAdapter 26.2  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 147.572 (Distance = 0)
   8 PhyOp_HashJoinx_jtInner 4.7 25.5 2.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 147.577 (Distance = 3)
   6 PhyOp_ExecutionModeAdapter 26.8  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 147.577 (Distance = 0)
   2 PhyOp_Applyx_jtInner 25.4 28.1  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 147.572 (Distance = 4)
   1 LogOp_Join 25 4 2 (Distance = 3)
   0 LogOp_Join 4 25 2 (Distance = 2)
Group 25: Card=25 (Max=8.25e+06, Min=0)
   8 PhyOp_ExecutionModeAdapter 25.1  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 165.105 (Distance = 0)
   7 PhyOp_Sort 25.5  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 147.569 (Distance = 0)
   6 PhyOp_ExecutionModeAdapter 25.7  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 147.569 (Distance = 0)
   5 PhyOp_HashGbAgg 3.4 16.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 147.565 (Distance = 3)
   4 PhyOp_ExecutionModeAdapter 25.5  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 147.565 (Distance = 0)
   1 PhyOp_HashGbAgg 3.2 16.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 165.105 (Distance = 3)
   0 LogOp_GbAgg 3 16 (Distance = 2)
Group 24: Card=1 (Max=1, Min=1)
   4 PhyOp_ExecutionModeAdapter 24.1  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 147.572 (Distance = 0)
   3 PhyOp_ComputeScalar 22.6 23.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 147.572 (Distance = 2)
   2 PhyOp_ExecutionModeAdapter 24.3  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 147.572 (Distance = 0)
   1 PhyOp_ComputeScalar 22.2 23.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 147.572 (Distance = 2)
   0 LogOp_Project 22 23 (Distance = 1)
Group 23: 
   0 AncOp_PrjList  13.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0 (Distance = 1)
Group 22: Card=1 (Max=10000, Min=1)
   6 PhyOp_ExecutionModeAdapter 22.2  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 147.572 (Distance = 0)
   5 PhyOp_HashGbAgg 26.9 21.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 147.581 (Distance = 3)
   4 PhyOp_ExecutionModeAdapter 22.5  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 147.581 (Distance = 0)
   2 PhyOp_StreamGbAgg 26.2 21.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 147.572 (Distance = 3)
   1 LogOp_GbAgg 26 21 (Distance = 2)
   0 LogOp_GbAgg 17 21 (Distance = 1)
Group 21: 
   0 AncOp_PrjList  20.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0 (Distance = 1)
Group 20: 
   0 AncOp_PrjEl  19.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0 (Distance = 1)
Group 19: 
   0 ScaOp_AggFunc  18.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 2 (Distance = 1)
Group 18: 
   0 ScaOp_Identifier   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 1 (Distance = 1)
Group 17: Card=4 (Max=8.25e+06, Min=0)
   2 PhyOp_HashGbAgg 5.2 16.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 150.449 (Distance = 2)
   1 PhyOp_ExecutionModeAdapter 17.2  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 150.449 (Distance = 0)
   0 LogOp_GbAgg 5 16 (Distance = 1)
Group 16: 
   0 AncOp_PrjList  15.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0 (Distance = 1)
Group 15: 
   0 AncOp_PrjEl  14.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0 (Distance = 1)
Group 14: 
   0 ScaOp_AggFunc  6.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 2 (Distance = 1)
Group 13: 
   0 AncOp_PrjEl  12.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0 (Distance = 0)
Group 12: 
   0 ScaOp_Convert  11.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 2 (Distance = 0)
Group 11: 
   0 ScaOp_Identifier   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 1 (Distance = 0)
Root Group 10: Card=1 (Max=1, Min=1)
   6 PhyOp_RestrRemap 24.3  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 147.572 (Distance = 2)
   4 PhyOp_ExecutionModeAdapter 10.6  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 147.572 (Distance = 0)
   2 PhyOp_RestrRemap 24.1  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 147.572 (Distance = 2)
   1 LogOp_RestrRemap 24 (Distance = 1)
   0 LogOp_GbAgg 5 9 (Distance = 0)
Group 9: 
   0 AncOp_PrjList  8.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0 (Distance = 0)
Group 8: 
   0 AncOp_PrjEl  7.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0 (Distance = 0)
Group 7: 
   0 ScaOp_AggFunc  6.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 2 (Distance = 0)
Group 6: 
   0 ScaOp_Const   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 1 (Distance = 0)
Group 5: Card=7.5e+06 (Max=8.25e+06, Min=0)
   9 PhyOp_ExecutionModeAdapter 5.2  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 150.065 (Distance = 0)
   2 PhyOp_HashJoinx_jtInner 4.7 3.4 2.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 150.065 (Distance = 2)
   1 LogOp_Join 4 3 2 (Distance = 1)
   0 LogOp_Join 3 4 2 (Distance = 0)
Group 4: Card=26 (Max=10000, Min=0)
   8 PhyOp_ExecutionModeAdapter 4.5  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.00331585 (Distance = 0)
   7 PhyOp_Range 1 ASC   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0033106 (Distance = 1)
   6 PhyOp_ExecutionModeAdapter 4.7  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.00331063 (Distance = 0)
   5 PhyOp_Range 1 ASC   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0033106 (Distance = 1)
   4 PhyOp_Range 4 ASC   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0033106 (Distance = 1)
   3 PhyOp_ExecutionModeAdapter 4.4  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.00331063 (Distance = 0)
   2 PhyOp_Range 4 ASC   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 0.0033106 (Distance = 1)
   0 LogOp_Get (Distance = 0)
Group 3: Card=7.5e+06 (Max=8.25e+06, Min=0)
   11 PhyOp_ExecutionModeAdapter 3.2  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 131.539 (Distance = 0)
   4 PhyOp_Range 1 ASC   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 130.024 (Distance = 1)
   3 PhyOp_ExecutionModeAdapter 3.4  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 130.032 (Distance = 0)
   2 PhyOp_Range 1 ASC   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 130.024 (Distance = 1)
   0 LogOp_Get (Distance = 0)
Group 2: 
   0 ScaOp_Comp  0.0 1.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 3 (Distance = 0)
Group 1: 
   0 ScaOp_Identifier   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 1 (Distance = 0)
Group 0: 
   0 ScaOp_Identifier   Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 1 (Distance = 0)

これは Memo と呼ばれる情報の最終的な結果となり、オプティマイザーが最適化を行う際の物理操作の情報が記載されているものです。(今回は Final Memo Structure を表示していますが、Logical operator tree を入力として使用した直後の状態の Initial Memo Structure という情報も存在しています)

先ほどと同じで「結合」について注目してみたいと思います。

論理操作としては「LogOp_Join」が結合を示す情報でした。

物理操作の結合について次の情報が結合方法を示す、最初の情報となります

Group 5: Card=7.5e+06 (Max=8.25e+06, Min=0)
   9 PhyOp_ExecutionModeAdapter 5.2  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 150.065 (Distance = 0)
   2 PhyOp_HashJoinx_jtInner 4.7 3.4 2.0  Cost(RowGoal 0,ReW 0,ReB 0,Dist 0,Total 0)= 150.065 (Distance = 2)
   1 LogOp_Join 4 3 2 (Distance = 1)
   0 LogOp_Join 3 4 2 (Distance = 0)

論理操作として、「LogOp_Join」が含まれており、その中で「PhyOp_HashJoinx_jtInner」という操作が含まれていることが確認できます。

この情報から、クエリオプティマイザーが Join という論理操作に対しての物理操作の代替案の一つとして「Hash Join」を提案したということが確認できます。

クエリオプティマイザーは Logical operator tree と等価になるような Physical operator tree を生成し、どのような物理操作を行った方がコストが低くなるかの最適化を行います。

この最適化の動作が Memo と呼ばれ、トレースフラグを使用することで Memo の情報を出力することができ、これによりオプティマイザーがどのような判断を行っているのかを確認することができます。

変換ルール

オプティマイザーには「変換ルール」という概念も存在しています。

今回はざっくりとした紹介にとどめておこうかと。

変換ルールは、「sys.dm_exec_query_transformation_stats」という DMV で確認をできるのですが、今回のケースであれば、「Apply Rule: JNtoHS – JN -> HS」という変換ルールがオプティマイザーの最適化のルールの一つとして使用されたことにより Hash Join が最適化の候補に入っています。

それでは、次のようなクエリを実行してみるとどうなるしょうか。

SELECT COUNT(*) FROM CUSTOMER
INNER JOIN NATION ON N_NATIONKEY = C_NATIONKEY
OPTION(RECOMPILE, QUERYRULEOFF JNtoHS)

このクエリでは、「QUERYRULEOFF JNtoHS」というヒントを指定しています。

これにより、クエリオプティマイザーが最適化する際に使用する変換ルールから Hash Join を取り除いて最適化を行うことになります。

実際の実行プランがこちらになるのですが、Inner Join の物理操作として Hash ではなく Nested Loop が採用されるようになりましたね。

Join の物理操作としては、「JNtoSM」(Sort Merge Join : Merge Join) や「JNtoIdxLookup」(Index による Nested Loop) という変換ルールの適用が施行され、「JNtoIdxLookup」の方がコストが低いと判断されて採用されているのかと思います。

それでは、さらに次のようにクエリを書き換えてみます。

SELECT COUNT(*) FROM CUSTOMER
INNER JOIN NATION ON N_NATIONKEY = C_NATIONKEY
OPTION(RECOMPILE,QUERYTRACEON 3604, QUERYTRACEON 8619, QUERYRULEOFF JNtoHS, QUERYRULEOFF JNtoIdxLookup)

「QUERYRULEOFF JNtoIdxLookup」を指定しているので、実行プランの想像は付くかと思いますが、このケースの場合は Merge Join が採用されます。

ただし、結合対象の情報の取得としては、Index Scan が使用されるようになっているため、効率はかなり悪いですが。

クエリオプティマイザーは最適化を行う際に、論理操作をどのような物理操作に変換するかをコストを考慮しながら最適化します。

通常は使用することはないかと思いますが、SQL Server では、論理操作の変換についてもある程度は制御ができるようになっており、クエリオプティマイザーについて学習を行うことで、この変換の制御についても学ぶことができます。

まとめ

本投稿で記載したような情報を基にすると、「実行プランからしか見れないと思っていたクエリオプティマイザーの情報」を様々な観点で確認をすることができます。

今回は QUERYTRACE 系を使用しましたが、これは、DBCC TRACEON でも制御を行うことができます。

(今回はクエリ単位で指定できればよかったので、クエリに QUERYTRACE のヒントを使用していますが、セッション単位で制御したい場合は DBCC から実行する等の使い分けができます)

残念ながら PaaS の環境では、QUERYTRACE / DBCC の仕様が制限されているケースが多く、PaaS ベースの SQL Server ではこれらの情報を取得することは難しいかと思います。

PaaS の SQL Server も基本のデータベースエンジンは Box (セルフインストールタイプ) の SQL Server と同じですので、PaaS の環境で確認ができない場合は、オンプレミスの環境に SQL Server をインストールし、類似のデータで確認をすることで PaaS で実行しているクエリの情報を追うということもできるのではないでしょうか。

今まで、ストレージエンジン回りの情報を中心に追っており、クエリオプティマイザーの情報は最低限しかおっていなかったのですが、調べだすと様々な情報が公開されており、様々な検証が行われています。

David DeWitt が公開されている情報については 10 年以上前の情報となり「俺は今までこの情報を見たことが無かったのか… orz」と落ち込み、海外の SQL Server を専門にしているエンジニアの方も 10 年以上前からこれらの情報を解析していたのを見ると、追加で「俺は海外のエンジニアの方と比較して SQL Server のことを何も理解していないな… orz」と追い打ちを喰らい、さらに暗澹たる気持ちになったりもしました…。

小さなことからコツコツと勉強して、海外の SQL Server のコンサルを専門とされている方に少しでも追いつけるようになりたいなと思った今日この頃です。

SE の雑記