通过

可选参数计划优化 （OPPO）

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

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

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

  • 等同性谓词

    WHERE column1 = @p

  • 动态搜索

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

  • 范围筛选器

    WHERE column1 BETWEEN @p1 AND @p2

术语及其工作原理

术语 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;

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

与 PSPO 使用的 谓词基数范围 类似，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;

若要禁用数据库的表达式的 CE 反馈，请禁用 OPTIONAL_PARAMETER_OPTIMIZATION 数据库范围的配置：

ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

扩展事件

  • optional_parameter_plan_optimization_skipped_reason：当 OPPO 决定查询不符合优化条件时发生。 此扩展事件遵循与 PSPO 使用的parameter_sensitive_plan_optimization_skipped_reason事件相同的模式。 由于查询可以同时生成 PSPO 和 OPPO 查询变体，因此应检查这两个事件，以了解为什么一个或两个功能都未参与。

相关内容