SQL Server 2025 OPPO: Better Plans. Less Duct Tape.

Parameter sniffing has been a recurring theme here lately. If you caught my sp_BlitzCache + AI series, you saw AI correctly diagnose parameter sniffing from a single output. SQL Server 2025 is now taking its own swing at parameter sniffing — automatically, at the engine level. Meet OPPO.

What Is OPPO?

OPPO stands for Optional Parameter Plan Optimization, and it ships in SQL Server 2025 as part of Intelligent Query Processing (IQP). It targets a specific and very common parameter sniffing pattern that has been painful for a long time — the optional parameter predicate. You've written this before:

SELECT *
FROM dbo.Orders
WHERE (CustomerID = @CustomerID OR @CustomerID IS NULL)
AND (StatusCode = @StatusCode OR @StatusCode IS NULL)

This pattern shows up everywhere — search screens, reporting stored procedures, anything that lets the user filter by one, some, or none of several columns. The problem is that the optimizer has always been forced to compile a single plan that works for both cases: when the parameter has a value (seek is right) and when it's NULL (scan is right). It can't do both well. Whichever value got cached first wins. Everything else loses.

OPPO solves this using the same Multiplan infrastructure introduced with Parameter Sensitive Plan Optimization (PSPO) in SQL Server 2022. Instead of a single cached plan, the optimizer now builds a dispatcher plan that evaluates the actual parameter value at runtime and routes to the appropriate query variant — seek when you've got a value, scan when you don't. The right plan for the right situation, automatically. Very nice.

How to Turn It On

OPPO is enabled by default when your database is at compatibility level 170, which is the default for SQL Server 2025. You don't have to do anything. If you want to be explicit about it, or you're managing compatibility levels carefully during an upgrade, here's the syntax:

ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 170;
GO
ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;
GO

Interestingly, it cannot be enabled/used only at the query level like MAXDOP, FORCE ORDER or other query level hints. You can, however, disable it at the query level if you need to opt out a specific query. So, it's all-or-nothing, but you can edit specific queries to ignore it:

SELECT *
FROM dbo.Orders
WHERE (CustomerID = @CustomerID OR @CustomerID IS NULL)
OPTION (USE HINT('DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION'));

Note that OPPO and PSPO are independent of each other. Enabling one does not require the other.

The Part Nobody Is Talking About

Here is something I want to emphasize for you because the marketing language around IQP features may not accurately reflect the reality.

OPPO currently works cleanly when you have exactly one optional parameter. Brent Ozar tested this thoroughly — if you haven't read his writeup on OPPO, you should check it out — and his conclusion is blunt: OPPO helps if your query has exactly one, and only one, optional parameter. Any more than that, and it looks like we're still managing these ourselves. That's not a knock on the feature. It's the same story PSPO told when it shipped in 2022 — limited initial scope, promising foundation, gradual investment over time. OPPO looks like it's following the same trajectory.

The kitchen-sink stored procedure with six optional search parameters? The one that's been the bane of your existence for yeeeears? OPPO doesn't fully solve that yet. What it does solve is the simpler, single-parameter case, and it solves it automatically without code changes, plan guides, or OPTION (RECOMPILE) scattered everywhere.

That's not nothing. That's actually pretty good.

What This Means for Your Upgrade Decision

OPPO alone isn't a reason to upgrade to SQL Server 2025. But it is one more item in a growing list of query improvements that could collectively make a real difference for any environment plagued with parameter sniffing problems.

Here's what to actually do before and after you upgrade:

Before the upgrade

Run sp_BlitzCache against your current environment and pull your top queries by parameter sniffing complaints. Document what you find. If you've been following along here, you already know how to do this — and you know how to hand that output to an AI and get a plain-English diagnosis of what's wrong and why.

After moving to compatibility level 170

Run it again. Compare. You want to know which queries OPPO picked up, which ones PSPO is handling, and which ones are still on you. Query Store could be your friend here. Capture baselines before the compatibility level change, not after.

Watch for plan shifts

OPPO can cause plan shifts on queries that were previously stable. 'Better' is the intent, but 'different' is what you'll see first, and different requires validation. Test your workload under compatibility level 170 in a non-production environment before you cut over.

Don't assume the multi-parameter case is handled

If you have stored procedures with two or more optional parameters, verify OPPO is actually helping them — don't assume. The other workarounds that you've been using — dynamic SQL, OPTION (RECOMPILE), OPTIMIZE FOR — are all still valid and still necessary in those cases.

The Bigger Picture

What I find genuinely interesting about OPPO isn't just the feature itself — it's what it represents. For years, parameter sniffing diagnosis has required a DBA who knows what to look for, a tool like sp_BlitzCache to surface it, and enough experience to connect the symptom to the cause. We just spent six posts exploring how well AI can take that sp_BlitzCache output and walk through the diagnosis. Now the engine itself is starting to detect and route around certain classes of the problem before it ever becomes a symptom.

That's a meaningful shift. It does not replace the DBA, but it does mean that some of the lowest-hanging parameter sniffing fruit gets handled automatically, which frees you up to focus on the harder cases — the ones where the data distribution is genuinely complicated, the procedure design is the real problem, or the fix requires a rewrite rather than a hint.

SQL Server is getting smarter about query execution. Slowly, incrementally, version by version. OPPO is one more step in that direction. It's not finished, but it's definitely real.

More to Read

Optional Parameter Plan Optimization — Microsoft Learn
How SQL Server 2025's Optional Parameter Plan Optimization Works — Brent Ozar
Parameter Sensitive Plan Optimization — Microsoft Learn
sp_BlitzCache + ChatGPT: I'm Convinced — sqlfingers.com