Edit

Share via

Optional parameter plan optimization (OPPO)

Applies to: Applies to: SQL Server 2025 (17.x) Preview Azure SQL Database SQL database in Microsoft Fabric

The term optional parameters refers to a specific variation of the parameter-sensitive plan (PSP) problem in which the sensitive, parameter value that exists during query execution, controls whether we need to perform a seek into or scan a table. A simple example would be something like:

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

In this example, SQL Server always chooses a plan that scans table Table1, even if there's an index on Table1(col1). A seek plan might not be possible with NULLs. Query hinting techniques, like OPTIMIZE FOR, might not be useful for this type of PSP problem because there isn't currently an operator that dynamically changes an index seek into a scan during execution. This kind of seek->scan combination at runtime might also not be effective, because the cardinality estimates on top of that operator would likely be inaccurate. The result is inefficient plan choices and excessive memory grants for more complex queries with similar query patterns.

The Optional Parameter Plan optimization (OPPO) feature uses the adaptive plan optimization (Multiplan) infrastructure that was introduced with the Parameter Sensitive Plan optimization (PSPO) improvement, which generates multiple plans from a single statement. This allows the feature to make different assumptions depending on the parameter values used in the query. During query execution time, OPPO selects the appropriate plan:

  • where the parameter value isn't NULL, it uses a seek plan or something more optimal than a full scan plan.
  • where the parameter value is NULL, it uses a scan plan.

As part of the adaptive plan optimization feature family, OPPO provides a solution to the second component of the Multiplan feature set, which covers dynamic search capabilities. adaptive plan optimization includes:

  • Equality predicates

    WHERE column1 = @p

  • Dynamic search

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

  • Range filters

    WHERE column1 BETWEEN @p1 AND @p2

Terminology and how it works

Term Description
Dispatcher expression This expression evaluates cardinality of predicates based on runtime parameter values, and routes execution to different query variants.
Dispatcher plan A plan containing the dispatcher expression is cached for the original query. The dispatcher plan is essentially a collection of the predicates that were selected by the feature, with a few extra details. For each predicate that is selected some of the details that are included in the dispatcher plan are the high and low boundary values. These values are used to divide parameter values into different buckets or ranges. The dispatcher plan also contains the statistics that were used to calculate the boundary values.
Query variant As dispatcher plan evaluates the cardinality of predicates based on runtime parameter values, it bucketizes them and generates separate child queries to run. These child queries are called query variants. Query variants have their own plans in the plan cache and the Query Store. In other words, by using different query variants, we achieve the goal of multiple plans for a single query.

As an example, consider an application web form for a realty company that allows for optional filtering on the number of bedrooms for a particular listing. A common antipattern could be to express the optional filter as:

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

Even if parameter @bedrooms = 10 is sniffed by the use of parameter markers, and we know that the cardinality for the number of bedrooms is likely to be very low, the optimizer doesn't produce a plan that seeks on an index which exists on the bedroom column because that isn't a valid plan for the case where @bedrooms is NULL. The generated plan doesn't include a scan of the index.

Imagine if this could be rewritten as two separate statements. Depending on the runtime value of the parameter, we could evaluate something like this:

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

We can achieve this by using the adaptive plan optimization infrastructure, which allows a creation of a dispatcher plan that dispatches two query variants.

Similar to the predicate cardinality range that PSPO uses, OPPO embeds a system usable query hint with the query text of the plan. This hint isn't valid for use by an application or if you attempt to use it yourself.

Continuing with the previous example,

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

OPPO can generate two query variants that might have the following attributes added to them within the Showplan XML:

  • @bedrooms is NULL. The query variant folded the original query to achieve a scan plan.

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

  • @bedrooms isn't NULL

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

Use optional parameter plan optimization

To enable OPPO for a database, the following prerequisites are required:

  • The database must use compatibility level 170.
  • The OPTIONAL_PARAMETER_OPTIMIZATION database-scoped configuration must be enabled.

The OPTIONAL_PARAMETER_OPTIMIZATION database-scoped configuration is enabled by default. This means that a database using compatibility level 170 (the default in SQL Server 2025) uses OPPO by default.

You can ensure that a database uses OPPO in SQL Server 2025 by executing the following statements:

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

To disable CE feedback for expressions for a database, disable the OPTIONAL_PARAMETER_OPTIMIZATION database-scoped configuration:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;

Extended Events

  • optional_parameter_plan_optimization_skipped_reason: Occurs when OPPO decides that a query isn't eligible for optimization. This extended event follows the same pattern as the parameter_sensitive_plan_optimization_skipped_reason event that is used by PSPO. Since a query can generate both PSPO and OPPO query variants, you should check both events to understand why one or neither feature engaged.

Related content