省略可能なパラメータープランの最適化 (OPPO)

2025-06-17

適用対象:適用対象: SQL Server 2025 (17.x) Preview Azure SQL Database Microsoft Fabric の SQL データベース

省略可能なパラメーターという用語は、クエリの実行中に存在する機密性の高いパラメーター値によって、テーブルにシークを実行するかスキャンする必要があるかを制御する、パラメーター依存プラン (PSP) の問題の特定のバリエーションを指します。簡単な例を次に示します。

SELECT column1,
       column2
FROM Table1
WHERE column1 = @p
      OR @p IS NULL;

この例では、Table1にインデックスがある場合でも、SQL Server はテーブル Table1(col1)をスキャンするプランを常に選択します。 NULL を使用した場合、シークプランが不可能なことがあります。現在、インデックスシークを実行中にスキャンに動的に変更する演算子がないため、 OPTIMIZE FORなどのクエリヒント手法は、この種の PSP の問題には役立たない可能性があります。実行時にこの種のシーク>スキャンの組み合わせも有効でない場合があります。これは、その演算子に基づくカーディナリティの見積もりが不正確になる可能性があるためです。その結果、似たクエリパターンを持つより複雑なクエリに対する非効率的なプランの選択と過剰なメモリ許可が得られます。

省略可能なパラメーター計画の最適化 (OPPO) 機能は、パラメーターの機密性の高いプランの最適化の改善に伴って導入されたアダプティブプラン最適化 (マルチプラン) インフラストラクチャを使用します。これは、1 つのステートメントから複数のプランを生成します。これにより、この機能では、クエリで使用されるパラメーター値に応じて異なる想定を行うことができます。クエリの実行時に、OPPO は適切なプランを選択します。

パラメーター値が IS NOT NULL場合は、シークプランまたはフルスキャンプランよりも最適なものを使用します。
パラメーター値が NULL場合、スキャンプランが使用されます。

OPPO は、パラメーターの機密性の高いプランの最適化を含むアダプティブプラン最適化機能ファミリの一部として、動的検索機能をカバーするマルチプラン機能セットの 2 番目のコンポーネントにソリューションを提供します。

等値述語
```
WHERE column1 = @p
```

動的検索

WHERE column1 = @p1 OR @p1 IS NULL
  AND column2 = @p2 OR @p2 IS NOT NULL

用語とそのしくみ

任期	説明
ディスパッチャー式	この式は、ランタイムパラメーター値に基づいて述語のカーディナリティを評価し、実行をさまざまなクエリバリアントにルーティングします。
ディスパッチャープラン	ディスパッチャー式を含むプランは、元のクエリに対してキャッシュされます。ディスパッチャープランは、本質的には、いくつかの追加の詳細とともに、機能によって選択された述語の集まりです。ディスパッチャー計画に含まれる詳細の一部として、選択された述語ごとに高境界値と低境界値があります。これらの値は、パラメーター値を異なるバケットまたは範囲に分割するために使用されます。ディスパッチャー計画には、境界値の計算に使用された統計も含まれています。
クエリバリアント	ディスパッチャープランは、ランタイムパラメーター値に基づいて述語のカーディナリティを評価すると、それらをバケット化し、実行する個別の子クエリを生成します。これらの子クエリは、クエリバリアントと呼ばれます。クエリバリアントには、プランキャッシュとクエリストアに独自のプランがあります。つまり、異なるクエリバリアントを使用することで、1 つのクエリに対して複数のプランの目標を達成します。

たとえば、特定のリスティングでベッドルーム数を任意に絞り込むことができる、不動産会社のアプリケーションのウェブフォームを考えてみましょう。一般的なアンチパターンは、省略可能なフィルターを次のように表現する場合があります。

SELECT * FROM Properties
WHERE bedrooms = @bedrooms
      OR @bedrooms IS NULL;

パラメーター @bedrooms = 10 がパラメーターマーカーを使用してスニッフィングされ、寝室の数のカーディナリティが非常に低い可能性が高いことがわかっていても、オプティマイザーは、ベッドルーム列に存在するインデックスをシークするプランを生成しません。これは、 @bedrooms が NULL場合の有効なプランではないのでです。生成されたプランには、インデックスのスキャンは含まれません。

これが 2 つの別個のステートメントとして書き換えられるとします。パラメーターのランタイム値に応じて、次のように評価できます。

IF @bedrooms IS NULL
    SELECT * FROM Properties;
ELSE
    SELECT * FROM Properties
    WHERE bedrooms = @bedrooms;

これを実現するには、アダプティブプラン最適化インフラストラクチャを使用します。これにより、2 つのクエリバリアントをディスパッチするディスパッチャープランを作成できます。

PSP 最適化で使用される述語カーディナリティ範囲と同様に、OPPO では、プランのクエリテキストと共にシステムで使用できるクエリヒントが埋め込まれます。このヒントは、アプリケーションで使用したり、自分で使用しようとしたりするには有効ではありません。

前の例に進みます。

SELECT * FROM Properties
WHERE bedrooms = @bedrooms
      OR @bedrooms IS NULL;

OPPO では、Showplan XML 内で次の属性が追加される可能性がある 2 つのクエリバリアントを生成できます。

@bedrooms は NULLです。クエリバリアントは、スキャンプランを実現するために元のクエリを 折りたたんだ 。

SELECT * FROM Properties PLAN PER VALUE(ObjectID = 1234, QueryVariantID = 1, optional_predicate(@bedrooms is NULL))
@bedrooms IS NOT NULL

SELECT * FROM Properties WHERE bedrooms = @bedrooms PLAN PER VALUE(ObjectID = 1234, QueryVariantID = 2, optional_predicate(@bedrooms is NULL))

オプションパラメーターのプラン最適化を使用する

データベースに対して OPPO を有効にするには、次の前提条件が必要です。

データベースは互換性レベル 170 を使用する必要があります。
OPTIONAL_PARAMETER_OPTIMIZATIONデータベーススコープの構成を有効にする必要があります。

OPTIONAL_PARAMETER_OPTIMIZATIONデータベーススコープの構成は、既定で有効になっています。つまり、互換性レベル 170 (SQL Server 2025 では既定) を使用するデータベースでは、既定で OPPO が使用されます。

次のステートメントを実行することで、データベースで SQL Server 2025 で OPPO が使用されていることを確認できます。

ALTER DATABASE [<database-name-placeholder>] SET COMPATIBILITY_LEVEL = 170;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;

データベースの省略可能なパラメーター計画の最適化を無効にするには、 OPTIONAL_PARAMETER_OPTIMIZATION データベーススコープの構成を無効にします。

ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

クエリヒントを使用して、オプションのパラメータープランを最適化する

DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION クエリヒントを使用して、特定のクエリの省略可能なパラメータープランの最適化を無効にすることができます。ヒントは、 USE HINT 句を使用して指定する必要があります。詳細については、 Query ヒントを参照してください。

ヒントは任意の互換性レベルで動作し、OPTIONAL_PARAMETER_OPTIMIZATION データベーススコープの構成をオーバーライドします。

DISABLE_OPTIONAL_PARAMETER_OPTIMIZATIONクエリヒントは、クエリ内で直接指定することも、クエリストアヒントを使用して指定することもできます。

拡張イベント

optional_parameter_optimization_skipped_reason: クエリが最適化の対象でないと OPPO が判断したときに発生します。この拡張イベントは、PSP 最適化で使用されるparameter_sensitive_plan_optimization_skipped_reason イベントと同じパターンに従います。クエリでは PSP 最適化と OPPO クエリバリアントの両方を生成できるため、両方のイベントを確認して、1 つまたは両方の機能が関与する理由を理解する必要があります。次のクエリは、PSP がスキップされた可能性のあるすべての理由を示しています。

SELECT map_value
FROM sys.dm_xe_map_values
WHERE [name] = 'opo_skipped_reason_enum'
ORDER BY map_key;

query_with_optional_parameter_predicate: 拡張イベントは、PSP 最適化で使用されるquery_with_parameter_sensitivity イベントと同じパターンに従います。これには、機能が興味深いと検出された述語の数、興味深い述語に関する json 形式の詳細、および OPPO が述語または述語に対してサポートされているかどうかの表示で構成される PSP 最適化の改善に使用できる追加のフィールドが含まれています。

注釈

クエリのバリアント向けの ShowPlan XML は次の例のようになります。選択された述語は、それぞれの情報が PLAN PER VALUE に追加されており、optional_predicate ヒントが含まれています。

<Batch>
  <Statements>
    <StmtSimple StatementCompId="4" StatementEstRows="1989" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="170" StatementSubTreeCost="0.0563916" StatementText="SELECT PropertyId, AgentId, ListingPrice, ZipCode, SquareFootage, &#xD;&#xA;           Bedrooms, Bathrooms, ListingDescription&#xD;&#xA;    FROM dbo.Property &#xD;&#xA;    WHERE (@AgentId IS NULL OR AgentId = @AgentId)&#xD;&#xA;      AND (@ZipCode IS NULL OR ZipCode = @ZipCode)&#xD;&#xA;      AND (@MinPrice IS NULL OR ListingPrice &gt;= @MinPrice)&#xD;&#xA;      AND (@HasDescription IS NULL OR &#xD;&#xA;           (@HasDescription = 1 AND ListingDescription IS NOT NULL) OR&#xD;&#xA;           (@HasDescription = 0 AND ListingDescription IS NULL)) option (PLAN PER VALUE(ObjectID = 1269579561, QueryVariantID = 7, optional_predicate(@MinPrice IS NULL),optional_predicate(@ZipCode IS NULL),optional_predicate(@AgentId IS NULL)))" StatementType="SELECT" QueryHash="0x2F701925D1202A9F" QueryPlanHash="0xBA0B2B1A18AF1033" RetrievedFromCache="true" StatementSqlHandle="0x09000033F4BE101B2EE46B1615A038D422710000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="1269579561" StatementParameterizationType="1" SecurityPolicyApplied="false">
      <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
      <Dispatcher>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@MinPrice] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@MinPrice" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@ZipCode] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@ZipCode" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
        <OptionalParameterPredicate>
          <Predicate>
            <ScalarOperator ScalarString="[@AgentId] IS NULL">
              <Compare CompareOp="IS">
                <ScalarOperator>
                  <Identifier>
                    <ColumnReference Column="@AgentId" />
                  </Identifier>
                </ScalarOperator>
                <ScalarOperator>
                  <Const ConstValue="NULL" />
                </ScalarOperator>
              </Compare>
            </ScalarOperator>
          </Predicate>
        </OptionalParameterPredicate>
      </Dispatcher>
      <QueryPlan DegreeOfParallelism="1" CachedPlanSize="40" CompileTime="1" CompileCPU="1" CompileMemory="376" QueryVariantID="7">

query_with_optional_parameter_predicate拡張イベントからの出力例

フィールド	価値
オプションのパラメータ最適化がサポートされています	正しい
オプションパラメータ述語数	3
述語の詳細	{"述語":[{"歪度":1005.53},{"歪度":1989.00},{"歪度":1989.00}]}
クエリタイプ	193

次の方法で共有

省略可能なパラメーター プランの最適化 (OPPO)

用語とそのしくみ

オプションパラメーターのプラン最適化を使用する

クエリ ヒントを使用して、オプションのパラメーター プランを最適化する

拡張イベント

注釈

関連コンテンツ

フィードバック

その他のリソース

省略可能なパラメータープランの最適化 (OPPO)

クエリヒントを使用して、オプションのパラメータープランを最適化する