可选参数计划优化（OPPO）

2025-06-12

适用于：SQL Server 2025（17.x）预览版Azure SQL 数据库 Microsoft Fabric 中的 SQL 数据库

术语 可选参数 是指参数敏感计划（PSP）问题的特定变体，即查询执行过程中存在的敏感参数值控制是否需要执行查找或扫描表。一个简单的示例类似于：

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

在此示例中，SQL Server 始终选择一个包含扫描表 Table1 的计划，即使 Table1(col1) 上存在索引。由于 NULL 的存在，搜寻计划可能无法实现。查询提示技术（例如 OPTIMIZE FOR）可能不适用于此类 PSP 问题，因为目前没有一个运算符可以在执行期间将索引查找动态更改为扫描。这种运行时的搜寻扫描>组合也可能无效，因为基于该运算符的基数估计可能不准确。结果是，对于具有类似查询模式的更复杂的查询，计划选择效率低下，内存授予过多。

可选参数计划优化（OPPO）功能使用通过参数敏感计划优化改进引入的自适应计划优化（多计划）基础结构，该基础结构从单个语句生成多个计划。这样，该功能就可以根据查询中使用的参数值做出不同的假设。在查询执行期间，OPPO 会选择相应的计划：

如果参数值不为 NULL，则它使用查找计划或比完整扫描计划更最佳的东西。
如果参数值为 NULL，则使用扫描计划。

作为自适应计划优化功能系列（包括参数敏感计划优化）的一部分，OPPO 为多计划功能集的第二个组件提供了解决方案，其中包括动态搜索功能。

等同性谓词
```
WHERE column1 = @p
```

动态搜索

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

术语及其工作原理

术语	DESCRIPTION
调度程序表达式	此表达式基于运行时参数值计算谓词的基数，并将执行路由到不同的查询变体。
调度计划	为原始查询缓存包含调度器表达式的方案。调度程序计划本质上是功能选择的谓词的集合，其中包含一些额外的详细信息。对于选择的谓词而言，调度计划中包含的一些详细信息是高边界值和低边界值。这些值用于将参数值划分为不同的存储桶或范围。调度程序计划还包含用于计算边界值的统计信息。
查询变体	当调度程序计划基于运行时参数值评估谓词的基数时，它会对这些谓词进行分组，并生成单独的子查询来运行。这些子查询称为查询变体。查询变体在计划缓存和查询存储中有自己的计划。换句话说，通过使用不同的查询变体，我们实现了单个查询的多个计划的目标。

例如，假设一个房地产公司的应用程序网页表单，它允许对特定房源的卧室数量进行可选筛选。常见的反模式可能是将可选筛选器表示为：

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

即使使用@bedrooms = 10来嗅探参数，并且我们知道卧室数的基数可能非常低，优化器也不会生成一个在卧室列上现有索引上查找的计划，因为在@bedrooms是NULL的情况下，这不是真正有效的计划。生成的计划不包括索引扫描。

假设这是否可以重写为两个单独的语句。根据参数的运行时值，我们可以评估如下所示的内容：

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

我们可以使用自适应计划优化基础结构来实现此目的，该基础结构允许创建调度程序计划来调度两个查询变体。

与 PSP 优化使用的谓词基数范围类似，OPPO 将系统可用查询提示与计划的查询文本嵌入。此提示对应用程序使用无效，或者如果您尝试自行使用也无效。

继续前面的示例，

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

OPPO 可以生成两个查询变体，这些变体可能在 Showplan XML 中向其添加以下属性：

@bedrooms 是 NULL。查询变体 折叠了 原始查询以实现扫描计划。

SELECT * FROM Properties PLAN PER VALUE（ObjectID = 1234， QueryVariantID = 1， optional_predicate（@bedrooms为 NULL））
@bedrooms 不是 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 子句指定提示。有关详细信息，请参阅查询提示。

提示可以在任何兼容级别下运行，并覆盖 OPTIONAL_PARAMETER_OPTIMIZATION 数据库范围的配置。

DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION查询提示可以直接在查询中指定，也可以通过查询存储提示指定。

扩展事件

optional_parameter_optimization_skipped_reason：当 OPPO 决定查询不符合优化条件时发生。此扩展事件遵循与 PSP 优化使用parameter_sensitive_plan_optimization_skipped_reason事件相同的模式。由于查询可以同时生成 PSP 优化和 OPPO 查询变体，因此应检查这两个事件以了解为什么一个或两个功能都未参与。以下查询显示了跳过 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。

注解

查询变体的 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
谓词_详情	{“Predicates”：[{“Skewness”：1005.53}，{“Skewness”：1989.00}，{“Skewness”：1989.00}]}
查询类型	193

通过

可选参数计划优化 （OPPO）

术语及其工作原理

使用可选参数计划优化

通过查询提示使用可选参数进行计划优化

扩展事件

注解

相关内容

反馈

其他资源

可选参数计划优化（OPPO）