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

対象者: SQL Server 2025 (17.x) Azure SQL DatabaseSQL database in Microsoft Fabric

省略可能なパラメーター プランの最適化 (OPPO) を使用すると、省略可能なパラメーターを含むクエリのクエリ プランの品質が向上します。 これらのクエリでは、最適な実行プランは、実行時にパラメーター値が NULL かどうかによって異なります。 省略可能なパラメーターという用語は、パラメーター依存プラン (PSP) の問題の特定のバリエーションを指します。この問題では、実行時のパラメーター値によって、クエリでシークまたはスキャンが必要かどうかが決定されます。

概要

省略可能なパラメーターを使用するクエリには、多くの場合、パラメーター値が指定されたかどうかに基づいてフィルターを条件付きで適用する述語が含まれます。 一般的なパターンは次のとおりです。

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

@p IS NOT NULL場合、多くの場合、col1のインデックス シークが最も効率的な実行プランです。 @p IS NULLすると、述語はTRUEに評価され、スキャンがより適切な場合があります。 OPPO がない場合、SQL Server データベース エンジンは、両方のケースで有効な 1 つの実行プランをコンパイルしてキャッシュする必要があります。 シークベースのプランは @p IS NULL場合は無効であるため、オプティマイザーでは、多くの場合、すべての実行に対して保守的なスキャン ベースのプランが選択されます。 この選択により、非効率的なプランの選択と、選択的な実行に対する過剰なリソース使用量が発生する可能性があります。

プランは両方のパラメーター状態に対して正しいままである必要があるため、このシナリオでは、 OPTIMIZE FOR などの従来のヒント手法は有効ではありません。

OPPO は、パラメーターセンシティブ プラン (PSP) 最適化で導入されたアダプティブ プラン最適化 (マルチプラン) インフラストラクチャを使用します。 このインフラストラクチャは、1 つのステートメントに対して複数の実行プランを生成してキャッシュします。これにより、OPPO はクエリで使用されるパラメーター値に基づいて異なる想定を行うことができます。

用語とそのしくみ

OPPO は、 パラメーターの機密性の高いプランの最適化でも使用されるアダプティブ プラン最適化 (マルチプラン) フレームワークに基づいています。 マルチプランを使用すると、データベース エンジンは 1 つのクエリに対して複数の実行プランを生成してキャッシュできます。

データベース エンジンは、適格な省略可能なパラメーター パターンを検出すると、次を作成します。

• ディスパッチャー プラン
• 特定のパラメーター値の状態に合わせて最適化された 1 つ以上のクエリ バリアント

実行時:

• データベース エンジンは、パラメーター値を評価します。
• マルチプラン ディスパッチャーは、適切なクエリバリアントを選択します。
• 選択したクエリバリアントが実行されます。

データベース エンジンは、クエリバリアントを選択すると、実際のパラメーター値に基づいて述語を簡略化します。 次の式について考えてみましょう。

@p1 IS NULL

この例では、式は、選択したバリアントの 定数の結果 に簡略化されます。 この定数の結果フォールディングにより、オプティマイザーは、単一の再利用可能なプランでは無効な実行プランを生成できます。

この方法でプランを選択することで、OPPO を使用すると、クエリの書き換えや手動クエリ ヒントを必要とせずに、さまざまなパラメーター状態に対して効率的に実行できます。

OPPO と PSP の最適化は、パラメーター関連のプランに関するさまざまな問題に対処します。

• PSP の最適化では、等価述語または範囲述語の推定カーディナリティの差に基づいてプランが選択されます。

• OPPO は、パラメーター値が NULLかどうかに基づいてプランを選択します。

1 つのクエリは、関係する述語に応じて、両方またはいずれかの機能の恩恵を受ける可能性があります。

サポートされているクエリ パターン

省略可能なパラメーター プランの最適化は、パラメーター NULL チェックが実行プランの有効性に影響するクエリに適用されます。 たとえば、特定のリスティングでベッドルーム数を任意に絞り込むことができる、不動産会社のアプリケーションのウェブフォームを考えてみましょう。 OPPO は、次のような非結合の省略可能なパラメーター述語に適用されます。

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;

この機能を実現するには、マルチプラン インフラストラクチャを使用します。これにより、クエリバリアントをディスパッチするディスパッチャー プランを作成できます。

OPPO は、システム生成 PLAN PER VALUE クエリ ヒント (optional_predicate) をプラン メタデータに埋め込み、各クエリ バリアントをパラメーターの状態に関連付けます。 このヒントは、システムによって生成され、プランのクエリ テキスト内に埋め込まれます。 このヒントは、アプリケーションで使用したり、手動で適用したりすることはできません。

前の例に進みます。

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 (17.x) の既定値) を使用するデータベースでは、既定で OPPO が使用されます。

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

ALTER DATABASE [<database-name-placeholder>]
SET COMPATIBILITY_LEVEL = 170;

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;

データベースの OPPO を無効にするには、 OPTIONAL_PARAMETER_OPTIMIZATION データベース スコープの構成を無効にします。

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

クエリ ヒントを活用して省略可能なパラメーターのプランを最適化する

DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION クエリ ヒントを使用して、特定のクエリの OPPO を無効にします。 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 イベントと同じパターンに従います。 PSP 最適化の改善に使用できる追加のフィールドが含まれています。

  次のフィールドが表示されます。

  • 機能が興味深いとされる述語の数。
  • 注目すべき述語に関するJSON形式での詳細
  • OPPO が述語またはその複数形に対してサポートされているかどうか。

Remarks

• クエリバリアントの 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拡張イベントからの出力例:

  Field	Value
  optional_parameter_optimization_supported	True
  optional_parameter_predicate_count	3
  predicate_details	{"Predicates":[{"Skewness":1005.53},{"Skewness":1989.00},{"Skewness":1989.00}]}
  query_type	193

クエリの適格性と制限事項

OPPO は、マルチプラン最適化の対象となるクエリにのみ適用されます。 この機能は、次のようなシナリオでは適用されません。

• パラメーターの代わりにローカル変数を使用するクエリ
• を使用してコンパイルされたクエリ OPTION (RECOMPILE)
• で実行されるクエリ SET ANSI_NULLS OFF
• 自動パラメーター化ステートメント

関連コンテンツ

• クエリ処理アーキテクチャ ガイド
• 実行プランの再コンパイル
• パラメーターと実行プランの再利用
• 単純なパラメーター化
• 強制パラメーター化
• クエリ ヒント (Transact-SQL)
• SQL データベースでのインテリジェントなクエリ処理
• パラメーターの感度
• データベース スコープの構成変更 (Transact-SQL)