SQL SERVER 2025 Optional parameter plan optimization (OPPO) What is and benchmarks. Internal part 1 series
Before we dive into today's topic, if you missed my previous post you can take a look at SQL SERVER 2025 CU5 (Cumulative Update 5) Is OUT! A Hot Take on the New Features for DBAs
👉 If you found this deep-dive helpful, feel free to check out the ads—your support helps me keep creating high-quality SQL Server content for the community.
SQL SERVER 2025 Optional Parameter Plan Optimization (OPPO)
What It Is and Benchmarks — Internal Part 1 Series
⚡ SQL Server 2025 finally attacks one of the oldest performance killers: optional parameter queries and unstable execution plans.
🧠 In this post you will learn how OPPO works internally, why it matters for parameter-sensitive workloads, and how to benchmark it yourself with real T-SQL tests.
🧠 TL;DR BOX
✔️ SQL Server 2025 OPPO automatically creates multiple execution plan variants for optional parameter queries ⚡
✔️ OPPO is part of Intelligent Query Processing and reduces parameter-sensitive plan issues 💣
✔️ Queries using patterns like (@Param IS NULL OR Column = @Param) can now get optimized plans 🚀
✔️ Benchmarks show dramatic improvements in CPU usage, logical reads, and execution stability 🔍
Hi SQL SERVER Guys and Gals,
For more than 20 years, optional parameters have been one of the biggest silent performance killers in SQL Server applications.
The classic pattern:
WHERE Column = @Parameter
OR @Parameter IS NULL
looks innocent.
💣 But internally it creates a nightmare for the optimizer.
SQL Server must generate ONE plan trying to satisfy BOTH scenarios:
- Highly selective seek queries
- Large scan queries
That usually means:
- Bad cardinality estimates
- Wrong join strategies
- Excessive memory grants
- CPU spikes
- Plan cache instability
🧠 What OPPO Really Is
Optional Parameter Plan Optimization (OPPO) is a new SQL Server 2025 Intelligent Query Processing feature designed to solve parameter-sensitive optional predicates automatically.
⚡ Instead of generating a single compromised execution plan, SQL Server can now generate multiple specialized plans.
This behavior is conceptually similar to Parameter Sensitive Plan (PSP) optimization introduced previously, but focused specifically on optional predicates.
Official Microsoft documentation:
💣 The Old Problem Before OPPO
Consider this classic stored procedure:
CREATE OR ALTER PROCEDURE dbo.SearchOrders
(
@CustomerID INT = NULL
)
AS
BEGIN
SELECT
SalesOrderID,
CustomerID,
OrderDate,
TotalDue
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID
OR @CustomerID IS NULL;
END
GO
🧠 This query mixes:
- Selective searches
- Full table scans
inside the SAME execution plan.
⚡ Result:
- Bad plan reuse
- Parameter sniffing instability
- Huge performance variance
🔍 DIAGNOSIS — Why SQL Server Struggled
Before OPPO, SQL Server had to estimate:
- How many rows would match
- Whether to use seek or scan
- How much memory to allocate
💣 But optional predicates destroy selectivity precision.
One cached plan becomes catastrophic for other executions.
This is one of the most common hidden causes of:
- CPU pressure
- Memory grant inflation
- Random slow queries
- Execution plan instability
🧪 QUERY — Benchmark Without OPPO
Run this benchmark manually.
-- 🔍 Benchmark Setup
ALTER DATABASE CURRENT
SET COMPATIBILITY_LEVEL = 160;
GO
ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = OFF;
GO
DBCC FREEPROCCACHE;
GO
SET STATISTICS IO, TIME ON;
GO
EXEC dbo.SearchOrders @CustomerID = 11000;
GO
EXEC dbo.SearchOrders @CustomerID = NULL;
GO
⚡ Observe:
- Logical reads
- CPU time
- Plan reuse behavior
💣 Usually one scenario becomes dramatically slower.
🚀 Enabling OPPO
Now enable SQL Server 2025 OPPO.
-- 🚀 Enable OPPO
ALTER DATABASE CURRENT
SET COMPATIBILITY_LEVEL = 170;
GO
ALTER DATABASE SCOPED CONFIGURATION
SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;
GO
DBCC FREEPROCCACHE;
GO
⚡ Compatibility level 170 is required.
🧪 QUERY — Benchmark WITH OPPO
SET STATISTICS IO, TIME ON;
GO
EXEC dbo.SearchOrders @CustomerID = 11000;
GO
EXEC dbo.SearchOrders @CustomerID = NULL;
GO
🧠 You should now observe:
- Different plan variants
- Better cardinality estimates
- Reduced logical reads
- Lower CPU usage
- More stable execution times
⚡ Internal Behavior
Internally, SQL Server now recognizes optional predicates as parameter-sensitive patterns.
Instead of forcing one universal plan, it can create:
- A seek-oriented plan
- A scan-oriented plan
🧠 SQL Server dynamically routes execution to the best plan variant depending on runtime parameter values.
This dramatically improves plan quality.
📊 Results Comparison
✅ CL 170 (OPPO Enabled)
Selective Query (CustomerID = 11000)
Logical Reads: 4
CPU: 0 ms
Elapsed Time: 15–20 msFull Scan Query (CustomerID = NULL)
Rows Returned: 20164
Logical Reads: 87
CPU: 0 ms
Elapsed Time: 78–79 ms
Execution Plan whit OPPO. Same Query with different paramemeters has different plan! WOW!
💣 CL 160 (OPPO Disabled)
Selective Query (CustomerID = 11000)
Logical Reads: 39
CPU: 0 ms
Elapsed Time: 13–14 msFull Scan Query (CustomerID = NULL)
Rows Returned: 20164
Logical Reads: 40365
CPU: 31 ms
Elapsed Time: 88–89 msExecution Plan without oppo. We have the same plane if a query has different parameters.
🔥 Key Differences
| Metric | CL 170 (OPPO) | CL 160 | Improvement |
|---|---|---|---|
| Logical Reads | 87 | 40365 | 🔥 ~460x reduction |
| CPU Time | 0 ms | 31 ms | 💣 Eliminated |
| Elapsed Time | ~79 ms | ~89 ms | Similar (small dataset) |
🧠 Deep Analysis
✅ CL 170 (OPPO Enabled)
- SQL Server creates multiple execution plans
- Index Seek used for selective queries
- Efficient Scan used for full dataset queries
- Accurate cardinality estimation
- Minimal logical reads
💣 CL 160 (OPPO Disabled)
- Single execution plan reused for all scenarios
- Compromised plan quality
- Massive unnecessary logical reads
- Higher CPU usage
- Poor scalability under load
💣 Important Caveats
OPPO is NOT magic.
There are still scenarios where dynamic SQL or manual query branching is superior.
Examples:
- Very complex predicates
- Multi-column optional filters
- Highly skewed distributions
- Extreme data volatility
🧠 But for many legacy stored procedures, OPPO is a huge win with zero code changes.
🚀 My REAL Strategy
After 25 years tuning SQL Server systems, here is my practical rule:
⚡ OPPO is one of the biggest real-world optimizer improvements since Parameter Sensitive Plan optimization.
But never blindly trust automation.
My workflow:
- Enable Query Store first
- Benchmark before/after
- Watch memory grants carefully
- Validate plan stability under concurrency
- Check plan cache growth
💣 Some workloads improve massively. Others may create additional plan cache pressure.
Always validate under production-like load.
📊 TAKEAWAY
- ⚡ OPPO solves one of SQL Server's oldest optional parameter optimization problems
- 🧠 SQL Server 2025 can now generate multiple execution plans automatically
- 💣 Optional predicates previously caused catastrophic parameter sniffing issues
- 🚀 OPPO can dramatically reduce CPU, IO, and execution instability
- 🔍 Benchmarking is mandatory before production rollout
📢 Support the Blog: Did you find this deep-dive helpful? The ads you see here are selected to reflect your interests. If a partner's offer catches your eye, give it a look! Your engagement helps me continue publishing premium SQL Server content for the community.
Performance Tuning Knowledge Hub
Tutto ciò che devi sapere per dominare SQL Server, in un unico posto.
Comments
Post a Comment