About three years ago, SQL Server 2022 introduced Parameter-Sensitive Plan Optimization (PSPO). At the time, I explained that it didn’t work particularly well, and went so far as to pronounce PSPO in a rather unflattering way. I wouldn’t suggest that anyone turn it off – it’s fine, just fine – but it isn’t powerful enough, and poses serious challenges for monitoring and plan cache analysis.
SQL Server 2025 improved PSPO to handle multiple predicates that might have parameter sensitivity, and that’s great! I love it when Microsoft ships a v1 feature, and then gradually iterates over to make it better. Adaptive Memory Grants were a similar investment that got improved over time, and today they’re fantastic.
SQL Server 2025 introduces another feature to mitigate parameter sniffing problems: Optional Parameter Plan Optimization (OPPO). It ain’t perfect today – in fact, it’s pretty doggone limited, like PSPO was when it first shipped, but I have hopes that SQL Server vNext will make it actually usable. Let’s discuss what we’ve got today first.
OPPO is for those kitchen-sink stored procedures where one query has a lot of optional parameters that might or might not be called at runtime, like this example using the Stack Overflow database:
Transact-SQL
12345678910111213141516 | CREATE OR ALTER PROC dbo.SearchUsers @DisplayName NVARCHAR(40) = NULL, @Location NVARCHAR(100) = NULL, @WebsiteUrl NVARCHAR(200) = NULLASSELECT TOP 100 * FROM dbo.Users WHERE (DisplayName = @DisplayName OR @DisplayName IS NULL) AND (Location = @Location OR @Location IS NULL) AND (WebsiteUrl = @WebsiteUrl OR @WebsiteUrl IS NULL) ORDER BY Reputation DESC;GOCREATE INDEX DisplayName ON dbo.Users(DisplayName);CREATE INDEX Location ON dbo.Users(Location);CREATE INDEX WebsiteUrl ON dbo.Users(WebsiteUrl);GO |
You can search by display name, location, website url, or any combination thereof. I’m not going to illustrate the problems with this in previous versions of SQL Server – they’re well-known and well-documented – and instead I’ll jump to SQL Server 2025 compatibility level, and turn on OPPO:
Transact-SQL
1234567 | ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 170;ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;GODBCC FREEPROCCACHE;GOEXEC dbo.SearchUsers @DisplayName = N'Brent Ozar'; |
The query runs in milliseconds and does a nice, fast index seek into our DisplayName index:
If you scroll through the query in the plan, you’ll notice that the query has been modified by both PSPO and OPPO. I’m formatting it here to make it a little easier to read:
Transact-SQL
12345678 | SELECT TOP 100 * FROM dbo.Users WHERE (DisplayName = @DisplayName OR @DisplayName IS NULL) AND (Location = @Location OR @Location IS NULL) AND (WebsiteUrl = @WebsiteUrl OR @WebsiteUrl IS NULL) ORDER BY Reputation DESC option (PLAN PER VALUE(ObjectID = 1957582012, QueryVariantID = 1, predicate_range([StackOverflow].[dbo].[Users].[Location] = @Location, 100.0, 10000.0),predicate_range([StackOverflow].[dbo].[Users].[WebsiteUrl] = @WebsiteUrl, 100.0, 1000000.0),optional_predicate(@DisplayName IS NULL))) |
The “PLAN PER VALUE” and “predicate_range” stuff comes from PSPO, but the “optional_predicate” stuff is new! That means that OPPO detected that DisplayName is an optional parameter that might or might not get passed in.
So now, if we search by location instead:
Transact-SQL
1 | EXEC dbo.SearchUsers @Location = N'Las Vegas, NV'; |
We get an execution plan that’s perfectly tuned for… wait, hang on a second:
That query plan is doing an index scan, not a seek, on the Location index. It’s reading all of the rows in the table, which takes about a second. Plus, the estimate of 5 rows isn’t all that great, either. What query was created?
Transact-SQL
12345678 | SELECT TOP 100 * FROM dbo.Users WHERE (DisplayName = @DisplayName OR @DisplayName IS NULL) AND (Location = @Location OR @Location IS NULL) AND (WebsiteUrl = @WebsiteUrl OR @WebsiteUrl IS NULL) ORDER BY Reputation DESC option (PLAN PER VALUE(ObjectID = 1957582012, QueryVariantID = 2, predicate_range([StackOverflow].[dbo].[Users].[Location] = @Location, 100.0, 10000.0),predicate_range([StackOverflow].[dbo].[Users].[WebsiteUrl] = @WebsiteUrl, 100.0, 1000000.0),optional_predicate(@DisplayName IS NULL))) |
The only thing that changed here is that we’ve got a different QueryVariant, but, uh… this is still a bad plan. It’s just a different plan. What if we search by website?
Transact-SQL
1 | EXEC dbo.SearchUsers @WebsiteUrl = N'https://www.brentozar.com' |
The query takes SEVENTEEN SECONDS to run, eventually producing this query plan:
Because it’s reusing the Location query, as evidenced by the QueryVariantID = 2 in the query it came up with:
Transact-SQL
12345678 | SELECT TOP 100 * FROM dbo.Users WHERE (DisplayName = @DisplayName OR @DisplayName IS NULL) AND (Location = @Location OR @Location IS NULL) AND (WebsiteUrl = @WebsiteUrl OR @WebsiteUrl IS NULL) ORDER BY Reputation DESC option (PLAN PER VALUE(ObjectID = 1957582012, QueryVariantID = 2, predicate_range([StackOverflow].[dbo].[Users].[Location] = @Location, 100.0, 10000.0),predicate_range([StackOverflow].[dbo].[Users].[WebsiteUrl] = @WebsiteUrl, 100.0, 1000000.0),optional_predicate(@DisplayName IS NULL))) |
To make matters worse, if we blow the plan cache and try these queries again, but in different order this time:
Transact-SQL
123 | DBCC FREEPROCCACHE;GOEXEC dbo.SearchUsers @WebsiteUrl = N'https://www.brentozar.com' |
If WebsiteUrl is searched for first, then it’s fast, running in milliseconds:
Because SQL Server picked a different parameter to use as the optional one! Check out the query that OPPO built:
Transact-SQL
12345678 | SELECT TOP 100 * FROM dbo.Users WHERE (DisplayName = @DisplayName OR @DisplayName IS NULL) AND (Location = @Location OR @Location IS NULL) AND (WebsiteUrl = @WebsiteUrl OR @WebsiteUrl IS NULL) ORDER BY Reputation DESC option (PLAN PER VALUE(ObjectID = 1957582012, QueryVariantID = 1, optional_predicate(@WebsiteUrl IS NULL),predicate_range([StackOverflow].[dbo].[Users].[Location] = @Location, 100.0, 10000.0),predicate_range([StackOverflow].[dbo].[Users].[WebsiteUrl] = @WebsiteUrl, 100.0, 1000000.0))) |
Before, when @DisplayName ran first to populate the plan cache, the optional_predicate was @DisplayName.
Now, when @WebsiteUrl runs first, the optional predicate is determined to be @WebsiteUrl! When Location runs next, it’s relatively quick, although with the same bad scan plan:
Transact-SQL
1 | EXEC dbo.SearchUsers @Location = N'Las Vegas, NV'; |
The resulting plan:
Then, when DisplayName runs:
Transact-SQL
1 | EXEC dbo.SearchUsers @DisplayName = N'Brent Ozar'; |
It takes 17 seconds to run:
Even though both PSPO and OPPO kicked in to build it a custom query variant:
Transact-SQL
12345678 | SELECT TOP 100 * FROM dbo.Users WHERE (DisplayName = @DisplayName OR @DisplayName IS NULL) AND (Location = @Location OR @Location IS NULL) AND (WebsiteUrl = @WebsiteUrl OR @WebsiteUrl IS NULL) ORDER BY Reputation DESC option (PLAN PER VALUE(ObjectID = 1957582012, QueryVariantID = 10, optional_predicate(@WebsiteUrl IS NULL),predicate_range([StackOverflow].[dbo].[Users].[Location] = @Location, 100.0, 10000.0),predicate_range([StackOverflow].[dbo].[Users].[WebsiteUrl] = @WebsiteUrl, 100.0, 1000000.0))) |
Summary: OPPO’s Pretty Limited in 2025.
Before Microsoft introduced 2022’s PSPO and 2025’s OPPO, we struggled with the problem of queries getting dramatically different performance depending on which parameters go into cache first.
Aaaaand we still do.
OPPO does seem to help if your query has exactly one, and only one, optional parameter. In real-world complex query scenarios, that doesn’t cut it. However, I hold out hope that OPPO will get the same gradual investments that Adaptive Memory Grants and PSPO got over time, and by the time SQL Server 2028 (or whatever they call it) rolls around, we’ll be in a better situation for parameter sniffing problems.