Optional parameter plans (SQL Server 2025)

In one line: at compatibility level 170, SQL Server 2025’s optional parameter plan optimization (OPPO) keeps one plan for “the parameter was given” and another for “it was NULL”, which fixes the classic (@p IS NULL OR col = @p) search. That holds for one optional parameter. With several, it can still reuse a bad plan.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/optional-parameter-plans on GitHub.

What you’ll see

  • At level 170, the single-parameter catch-all behaves. All customers after one customer read 16,286 pages instead of 3,001,752. One customer went from 1,870 to 150, because the plan no longer has to allow for NULL.
  • Two plan variants for one query in sys.query_store_query_variant.
  • Searches with several optional filters that are still sometimes awful. “Neither filter” read 3,189,131 pages, most likely through the date search’s plan.

Why it happens

OPPO compiles separate variants depending on whether the parameter is NULL, the same variant mechanism as 2022’s parameter-sensitive plans. With two optional parameters, the demo recorded two variants, not one per combination. “Date only” and “neither” both have the customer NULL, so they shared a plan.

OPPO only recognises the plain form:

  • COALESCE(@p, col) and ISNULL(@p, col) got no variants: one scan for every call, 100× the reads of the OR form for one customer.
  • Copying the parameter into a local variable (DECLARE @c int = @p) switched it off. Changing the parameter itself first (SET @p = NULLIF(@p, 0)) didn’t.

What to do

  • Run at compatibility level 170 and write optional filters as (@p IS NULL OR col = @p), using the parameter directly.
  • For a search screen with many filters, keep using dynamic SQL with parameters or OPTION (RECOMPILE). OPPO doesn’t cover every combination.
  • If OPPO causes a regression, turn it off for one database with ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF and keep the rest of level 170.

What doesn’t help

  • COALESCE/ISNULL forms. No variants, and they return wrong answers when the column allows NULL.
  • Copying parameters into local variables to dodge sniffing. It also dodges OPPO.

Check your server

-- Compatibility level and the OPPO setting for this database (SQL Server 2025+),
-- and queries that already have plan variants in Query Store.
SELECT name, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();

SELECT name, value
FROM sys.database_scoped_configurations
WHERE name = 'OPTIONAL_PARAMETER_OPTIMIZATION';

SELECT v.parent_query_id, v.query_variant_query_id, v.dispatcher_plan_id
FROM sys.query_store_query_variant AS v;

See it happen

The optional-parameter-plans demo runs the catch-all search at levels 160 and 170, with one and two optional parameters, written with OR, COALESCE, ISNULL and a local variable. It then turns the setting off. On 2022 and earlier it reports SKIPPED.

Related: catch-all queries · SQL Server 2022 query processing · parameter sniffing

Measured on SQL Server 2025 RTM-CU8 (17.0.4075.5), Linux container. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights