Problem
Recently, Microsoft announced SQL Server 2025. In this version, they introduced the Optional Parameter Plan Optimization (OPPO) feature and improved the Parameter Sensitive Plan Optimization (PSPO) feature, which was originally introduced in SQL Server 2022. The PSPO and OPPO features are to address Parameter Sniffing issues. In this article, I will show you how these features work.
Solution
An execution plan is generated and cached based on the first set of values passed to a stored procedure or a parameterized query. When the distribution of data in a table is not evenly balanced, the generated plan may not be suitable for all possible values that will be passed to the stored procedure. Moreover, some stored procedures have optional parameters, which means we can either pass a value or not. Therefore, one execution plan is not suitable for all search modes, and your query is sometimes fast and sometimes slow. In a nutshell, there are times when a query needs multiple plans to run fast enough, i.e., one plan is suitable for some input parameter values and another plan is better for other values.
In theory, the OPPO and PSPO features generate multiple plans for a single T-SQL statement.
According to Microsoft documentation:
- PSPO addresses the scenario where a single cached plan for a parameterized query isn’t optimal for all possible incoming parameter values.
- During query execution time, OPPO selects the appropriate plan:
- Where the parameter value isn’t NULL, it uses a seek plan or something more optimal than a full scan plan.
- Where the parameter value is NULL, it uses a scan plan.
If you are interested in learning more about these features, view: Optional Parameter Plan Optimization (OPPO).
Let’s see how these features work in action.
Set Up Test Environment
I will use SQL Server 2025 and the StackOverflow database.
To benefit from the OPPO feature, set the compatibility level to 170. I also enable the OPPO feature, which is a database-scoped configuration.
Use master
GO
ALTER DATABASE StackOverflow SET COMPATIBILITY_LEVEL = 170;
GO
Use StackOverflow
GO
ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;
GOBegin the demo by creating two nonclustered indexes on the Reputation and Location columns of the Users table.
Create Index IX_Reputation On dbo.Users (Reputation)
With (Data_Compression = Page)
GO
Create Index IX_Location On dbo.Users (Location)
With (Data_Compression = Page)
GOI’m creating a stored procedure with two optional input parameters. This means I can pass one, both, or no values to it.
Create Or Alter Procedure USP_OPPO_Test
(@Reputation Int = Null, @Location Nvarchar(100) = Null)
AS
Select * From dbo.Users u
Where (u.Reputation = @Reputation Or @Reputation Is Null) And (u.[Location] = @Location Or @Location Is Null)
GOTesting OPPO and PSPO
For the first time, I run the procedure to display the information of users who live in India:
Exec USP_OPPO_Test @Location = N'India'
GOSQL Server scans the entire table and uses only one logical CPU core to execute the query, returning 49,358 rows. The query executes in serial mode (not parallel).
SQL Server changes the query as shown below and considers both input parameters to be optional.
The optional_predicate is related to OPPO, but SQL Server did not add predicate_range, which is related to PSPO!
Select * From dbo.Users u
Where (u.Reputation = @Reputation Or @Reputation Is Null) And (u.[Location] = @Location Or @Location Is Null) option (PLAN PER VALUE(ObjectID = 1461580245, QueryVariantID = 3,
optional_predicate(@Reputation IS NULL),optional_predicate(@Location IS NULL)))
GOAnyway, execute the procedure once more to display users who live in India and have a reputation of 1.
Exec USP_OPPO_Test @Location = N'India', @Reputation = 1
GOSQL Server performs a clustered index scan, but this time it ran the query in parallel mode, despite the number of rows returned being reduced to 26,433.
What will happen if we run the stored procedure to display users who have a reputation of 2 and live in India?
Exec USP_OPPO_Test @Location = N'India', @Reputation = 2
GOSQL Server reads nearly 9 million rows to return just 75 rows:
Do not forget that we created two nonclustered indexes on the Location and Reputation columns, and SQL Server ignored them.
Let’s remove all plans from the cache and run the stored procedure again. To figure out how many pages SQL Server reads to return 75 rows, turn on STATISTICS IO.
DBCC FreeProcCache
GO
SET STATISTICS IO ON
GO
Exec USP_OPPO_Test @Location = N'India', @Reputation = 2
GOSQL Server performs a nonclustered index seek followed by key lookups. It uses only the index created on the Reputation column and ignores the one created on the Location column.
Let’s see how many pages were read to display the result.
The number of logical reads is slightly more than 28,000.
Ad Hoc Query
However, if we write a query directly in SQL Server Management Services (SSMS) to filter users who live in India and have a reputation of 2, the Query Optimizer uses both nonclustered indexes, and the number of logical reads is significantly reduced.
Select * From dbo.Users u
Where u.Reputation = 2 And u.Location = N'India'You can see the actual execution plan here:
The number of logical reads is only 306.
In situations demonstrated in this tip, I recommend using dynamic queries to address Parameter Sniffing issues, especially if your server has enough RAM, say, more than 128 GB.
Summary
The Parameter Sensitivity problem, also known as the Parameter Sniffing issue, is a long-standing issue in SQL Server. OPPO and PSPO aim to mitigate the Parameter Sensitivity problem. In this article, I have shown that the OPPO and PSPO features do not work well—at least in the examples used here. Since SQL Server 2025 is still in preview, I hope Microsoft will enhance these algorithms in the future. We can still use OPTION (RECOMPILE) or dynamic SQL to address this problem.
Next Steps
