Optional search parameters are convenient for application developers and notoriously awkward for the query optimizer. A single stored procedure can support a search screen with a dozen filters, each written in the familiar form Column = @Parameter OR @Parameter IS NULL. When a parameter has a value, an index seek might be ideal. When it is NULL, the filter disappears and a scan might be the only sensible plan.

Before SQL Server 2025, one cached plan had to be correct for both cases. SQL Server 2025 introduces Optional Parameter Plan Optimization (OPPO) for this specific problem. OPPO recognizes eligible optional predicates, builds a dispatcher, and caches separate query variants for different parameter states. It uses the same Multiplan infrastructure as Parameter Sensitive Plan optimization, but the decision is different: PSP separates cardinality ranges for supplied values, while OPPO separates NULL from non-NULL.

This article builds a reproducible search workload, captures the conservative plan used without OPPO, enables the feature, and proves that the dispatcher routes executions to different variants. It then compares OPPO with OPTION (RECOMPILE) and parameterized dynamic SQL. The objective is not simply to turn on a feature. It is to know when SQL Server actually used it and whether its variants are good enough for production.

The optional-predicate problem is not ordinary parameter sniffing

Consider this predicate:

WHERE CityID = @CityID
   OR @CityID IS NULL;

For @CityID = 42, SQL Server can use an index on CityID to retrieve one city's listings. For @CityID = NULL, the predicate is true for every row. A plan containing only a seek on CityID = @CityID would not be logically valid for the second case. That distinction explains why OPTIMIZE FOR is rarely a complete answer. The issue is not merely that one non-NULL value has more rows than another. The predicate itself changes meaning when the parameter becomes NULL.

A statement can qualify for PSP, OPPO, both, or neither. Microsoft documents OPPO as a SQL Server 2025 feature that requires database compatibility level 170. The OPTIONAL_PARAMETER_OPTIMIZATION database-scoped configuration is enabled by default at that level, but I prefer to verify it rather than assume it. See Optional Parameter Plan Optimization.

Build a disposable search workload

Run this lab on SQL Server 2025 in a nonproduction environment. It creates a database and 500,000 property listings. Each of 100 cities receives 5,000 rows, so a supplied city is selective while a NULL city returns the whole table.

USE master;
GO

DROP DATABASE IF EXISTS OPPODemo;
GO

CREATE DATABASE OPPODemo;
GO

ALTER DATABASE OPPODemo SET COMPATIBILITY_LEVEL = 170;
GO

ALTER DATABASE OPPODemo
SET QUERY_STORE = ON
    (
        OPERATION_MODE = READ_WRITE,
        QUERY_CAPTURE_MODE = ALL,
        INTERVAL_LENGTH_MINUTES = 1
    );
GO

USE OPPODemo;
GO

CREATE TABLE dbo.PropertyListing
(
    ListingID INT NOT NULL,
    CityID INT NOT NULL,
    Bedrooms TINYINT NOT NULL,
    ListingPrice DECIMAL(12, 2) NOT NULL,
    ListedDate DATE NOT NULL,
    IsActive BIT NOT NULL,
    Description CHAR(120) NOT NULL,
    CONSTRAINT PK_PropertyListing
        PRIMARY KEY CLUSTERED (ListingID)
);
GO

WITH E1 (N)
AS (SELECT 1
    FROM
    (
        VALUES
            (0),
            (0),
            (0),
            (0),
            (0),
            (0),
            (0),
            (0),
            (0),
            (0)
    ) AS d (N) ),
     E2 (N)
AS (SELECT 1
    FROM E1 AS a
        CROSS JOIN E1 AS b),
     E4 (N)
AS (SELECT 1
    FROM E2 AS a
        CROSS JOIN E2 AS b),
     E6 (N)
AS (SELECT 1
    FROM E4 AS a
        CROSS JOIN E2 AS b),
     Numbers
AS (SELECT TOP (500000)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM E6)
INSERT dbo.PropertyListing
(
    ListingID,
    CityID,
    Bedrooms,
    ListingPrice,
    ListedDate,
    IsActive,
    Description
)
SELECT n,
       1 + CONVERT(INT, (n - 1) % 100),
       1 + CONVERT(TINYINT, n % 5),
       CONVERT(DECIMAL(12, 2), 150000 + (n % 850000)),
       DATEADD(DAY, -CONVERT(INT, n % 730), CONVERT(DATE, '2026-01-01')),
       CONVERT(   BIT,
                  CASE
                      WHEN n % 10 = 0 THEN
                          0
                      ELSE
                          1
                  END
              ),
       REPLICATE(CHAR(65 + n % 26), 120)
FROM Numbers;
GO

CREATE INDEX IX_PropertyListing_CityID
ON dbo.PropertyListing (CityID)
INCLUDE (
            Bedrooms,
            ListingPrice,
            ListedDate,
            IsActive
        );
GO

UPDATE STATISTICS dbo.PropertyListing
WITH FULLSCAN;
GO

CREATE OR ALTER PROCEDURE dbo.usp_SearchListings @CityID INT = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT ListingID,
           CityID,
           Bedrooms,
           ListingPrice,
           ListedDate,
           IsActive
    FROM dbo.PropertyListing
    WHERE CityID = @CityID
          OR @CityID IS NULL;
END;
GO

The included columns are deliberate. A selective city search can be satisfied from the nonclustered index without hundreds of key lookups, while the unfiltered request still has to process all 500,000 rows.

Capture the single-plan baseline

First disable OPPO while leaving compatibility level 170 in place. That isolates the feature rather than changing several optimizer behaviors at once.

ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;
GO

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

SET STATISTICS IO, TIME ON;
GO

EXEC dbo.usp_SearchListings @CityID = 42;
GO

EXEC dbo.usp_SearchListings @CityID = NULL;
GO

SET STATISTICS IO, TIME OFF;
GO

Capture the actual execution plan. The optimizer must produce a plan that remains correct when @CityID is NULL. In a typical run, that means a scan-shaped plan even when the supplied city returns only 1 percent of the table. Exact costs, timings, and access methods can vary by build and hardware; the important evidence is that both executions reuse one plan. Reversing the execution order does not solve the logical restriction:

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

EXEC dbo.usp_SearchListings @CityID = NULL;
EXEC dbo.usp_SearchListings @CityID = 42;
GO

Unlike a simple equality predicate, compiling first for the selective value cannot make the optimizer cache a seek-only plan that would omit rows when the parameter is later NULL.

Enable OPPO and create both variants

Now enable the feature, clear only the test database's procedure cache, and execute both parameter states:

ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;
GO

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
GO

SELECT
    d.name,
    d.compatibility_level,
    d.is_query_store_on,
    s.value AS optional_parameter_optimization
FROM sys.databases AS d
CROSS APPLY
(
    SELECT value
    FROM sys.database_scoped_configurations
    WHERE name = 'OPTIONAL_PARAMETER_OPTIMIZATION'
) AS s
WHERE d.database_id = DB_ID();
GO

SET STATISTICS IO, TIME ON;
GO

EXEC dbo.usp_SearchListings @CityID = 42;
GO

EXEC dbo.usp_SearchListings @CityID = NULL;
GO

SET STATISTICS IO, TIME OFF;
GO

The first execution causes SQL Server to compile a dispatcher and the required variant. The second parameter state causes its corresponding variant to be compiled.

For this lab, the non-NULL variant can simplify the optional expression and use the city index, while the NULL variant can remove the filter and scan. OPPO does not execute both plans and choose the faster result. The dispatcher evaluates the parameter and routes the request before the selected variant runs. Each variant is a normal cached plan with its own runtime behavior.

Prove that OPPO engaged

Do not treat a faster test as proof. Data might already be in memory, a concurrent workload might have changed, or a different plan might have compiled for an unrelated reason. Use three forms of evidence. First, inspect the actual plan's properties or XML. A variant contains a system-generated PLAN PER VALUE hint and an optional_predicate(@CityID IS NULL) annotation. The hint is internal metadata; it is not syntax to copy into application code. Second, query Query Store.

The following report shows the parent/variant relationship, plan type, execution count, and average resource consumption for captured variants:

SELECT
    qv.parent_query_id,
    qv.query_variant_query_id,
    qv.dispatcher_plan_id,
    p.plan_id,
    p.plan_type_desc,
    SUM(rs.count_executions) AS executions,
    CONVERT(decimal(18, 2),
        SUM(rs.avg_duration * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)) AS weighted_avg_duration_us,
    CONVERT(decimal(18, 2),
        SUM(rs.avg_logical_io_reads * rs.count_executions)
        / NULLIF(SUM(rs.count_executions), 0)) AS weighted_avg_logical_reads
FROM sys.query_store_query_variant AS qv
JOIN sys.query_store_plan AS p
  ON p.query_id = qv.query_variant_query_id
LEFT JOIN sys.query_store_runtime_stats AS rs
  ON rs.plan_id = p.plan_id
GROUP BY
    qv.parent_query_id,
    qv.query_variant_query_id,
    qv.dispatcher_plan_id,
    p.plan_id,
    p.plan_type_desc
ORDER BY
    qv.parent_query_id,
    qv.query_variant_query_id,
    p.plan_id;
GO

The weighted averages matter. Query Store can contain several runtime-statistics intervals for one plan, so averaging the averages would give every interval equal weight regardless of execution count. Third, use Extended Events when OPPO does not appear. SQL Server 2025 exposes query_with_optional_parameter_predicate and optional_parameter_optimization_skipped_reason. List the skip-reason map on the build you are testing instead of relying on a copied numeric code:

SELECT map_key, map_value
FROM sys.dm_xe_map_values
WHERE name = N'opo_skipped_reason_enum'
ORDER BY map_key;
GO

Because one statement can engage both PSP and OPPO, Microsoft recommends checking the PSP events as well when diagnosing a missing dispatcher.

Expand the procedure carefully

Real search procedures have more than one optional parameter:

CREATE OR ALTER PROCEDURE dbo.usp_SearchListings
    @CityID int = NULL,
    @Bedrooms tinyint = NULL,
    @MinimumPrice decimal(12, 2) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        ListingID,
        CityID,
        Bedrooms,
        ListingPrice,
        ListedDate,
        IsActive
    FROM dbo.PropertyListing
    WHERE (CityID = @CityID OR @CityID IS NULL)
      AND (Bedrooms = @Bedrooms OR @Bedrooms IS NULL)
      AND (ListingPrice >= @MinimumPrice OR @MinimumPrice IS NULL);
END;
GO

OPPO can identify more than one interesting optional predicate, but do not assume it will materialize every theoretical combination. Ten binary filters would have 1,024 NULL/non-NULL combinations. Unbounded variant creation would merely exchange one performance problem for plan-cache and compilation pressure. Test the combinations that your application actually sends. At minimum, include:

• All parameters NULL.
• Each selective parameter supplied alone.
• The most frequent combination.
• The broadest expensive combination.
• Values at the extremes of the underlying data distribution.

Also review Query Store by variant. Aggregating only at the parent query can hide one variant that performs poorly while the others are healthy.

OPPO versus recompilation and dynamic SQL

OPPO is attractive because it needs no application rewrite and amortizes compilation across executions. It is not automatically the best solution for every search screen.

For recompilation, the change is small:

SELECT
    ListingID,
    CityID,
    Bedrooms,
    ListingPrice,
    ListedDate,
    IsActive
FROM dbo.PropertyListing
WHERE CityID = @CityID
   OR @CityID IS NULL
OPTION (RECOMPILE);

This is often effective for an infrequent administrative search with many combinations. It is less appealing for a procedure executed thousands of times per second. Parameterized dynamic SQL avoids concatenating values into the command text:

DECLARE @sql nvarchar(max) =
N'SELECT
      ListingID,
      CityID,
      Bedrooms,
      ListingPrice,
      ListedDate,
      IsActive
  FROM dbo.PropertyListing
  WHERE 1 = 1';

IF @CityID IS NOT NULL
    SET @sql += N' AND CityID = @pCityID';

EXEC sys.sp_executesql
    @sql,
    N'@pCityID int',
    @pCityID = @CityID;

The values remain parameters, which avoids injection and promotes plan reuse for the same predicate shape. A production generator must still whitelist any dynamic identifiers, sort expressions, or operators; parameters protect values, not arbitrary SQL fragments.

When OPPO is not enough

OPPO answers the NULL versus non-NULL question. The selected non-NULL variant can still face data skew. One city might contain half the listings while most cities contain a few hundred. PSP may further help that equality predicate, but only if the statement is eligible and the statistics expose useful boundaries. Other cases still require design work:

• Leading-wildcard searches such as LIKE '%term%'.
• Expressions or implicit conversions that make predicates non-SARGable.
• Stale or low-quality statistics.
• Joins whose best order changes across filter combinations.
• Very large result sets sent to the application without paging.
• Business rules implemented through complex OR branches that do not match eligible optional patterns.

OPPO cannot turn a missing index into an index, reduce the cost of returning 500,000 rows over the network, or repair a search API that permits unrestricted exports during peak OLTP hours.

A production rollout checklist

I would deploy OPPO through an upgrade workflow rather than by changing compatibility level directly in production:

1. Patch SQL Server 2025 to the cumulative update approved by your organization.
2. Enable and size Query Store before the compatibility-level change.
3. Capture the most common and most expensive parameter combinations.
4. Test compatibility level 170 with production-scale data.
5. Verify the dispatcher and every important variant in ShowPlan and Query Store.
6. Compare logical reads, CPU, duration, memory grants, spills, and waits—not only elapsed time.
7. Monitor Query Store storage and compilation rate after rollout.
8. Keep a query-level rollback available.

To disable OPPO for one problematic statement without editing application code, apply the supported hint through Query Store hints. Substitute the parent query ID from your own Query Store:

EXEC sys.sp_query_store_set_hints
    @query_id = 123,
    @query_hints =
        N'OPTION (USE HINT(''DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION''))';
GO

-- Remove the emergency hint after testing the permanent fix.
EXEC sys.sp_query_store_clear_hints
    @query_id = 123;
GO

Do not paste the example ID into another database. Query Store IDs are local to a database and can change after cleanup or a reset.

Closing

Kitchen-sink search procedures have always forced a compromise. A scan-shaped plan is safe for an unfiltered request but wasteful for a selective one. Recompilation generates a specialized plan but pays the compile cost each time. Dynamic SQL produces good predicate-specific shapes but moves complexity into application or stored-procedure code. OPPO gives SQL Server 2025 a new middle ground. It recognizes optional predicates, caches a dispatcher, and reuses variants specialized for parameter states.

For the lab in this article, that means a seek-capable plan when @CityID is supplied and a scan-capable plan when it is not. The operational rule is the same one that applies to every adaptive optimizer feature: verify, do not infer. Confirm compatibility level 170 and the database-scoped configuration. Find PLAN PER VALUE and optional_predicate in the plan. Measure each variant separately in Query Store. If OPPO declines the statement, capture its skip reason. If its finite set of variants cannot represent the workload, retain recompilation or parameterized dynamic SQL as deliberate alternatives.

OPPO does not make every flexible search procedure fast. It gives eligible searches more than one correct reusable plan—and gives DBAs one fewer reason to accept a full scan for every optional filter.