Catch-All Queries and Optional Parameter Plan Optimization in SQL Server 2025

Search screens can send a selective filter or no filter through the same statement. Catch-all queries must preserve both meanings, which makes a single reusable plan difficult to choose well.

Separate Optional Filters From Ordinary Parameters

A predicate such as CustomerID equals a parameter or the parameter is NULL expresses two different access requirements. A non-NULL value selects that customer's rows; NULL disables the filter. The plan must remain correct for both cases when reused. That requirement can prevent a simple selective access path from serving every execution efficiently.

More optional predicates create additional combinations. A customer-only search, a status-only search, and an unfiltered search can need different work. Selectivity also varies among non-NULL values. Optional filtering and ordinary parameter sensitivity overlap, but they are not identical problems.

I confirm the search contract before changing the query. NULL must have a defined meaning, including whether it means no filter or a search for stored NULL values. Those meanings require different expressions. A clever plan cannot repair a predicate that answers the wrong business question.

Build an Isolated Example of Catch-All Queries

The lab assumes an existing disposable SQL Server 2025 database named OptionalSearchLab. GENERATE_SERIES needs compatibility level 160 or higher. The chosen distribution deliberately makes one customer much more common than the others, without claiming a production row count or timing.

CREATE TABLE dbo.SearchOrders
(
    OrderID int PRIMARY KEY,
    CustomerID int NOT NULL,
    OrderState varchar(10) NOT NULL,
    Amount decimal(10,2) NOT NULL
);
INSERT dbo.SearchOrders(OrderID,CustomerID,OrderState,Amount)
SELECT CONVERT(int,value),
       CASE WHEN value%10<8 THEN 1 ELSE 2+CONVERT(int,value%999) END,
       CASE WHEN value%5=0 THEN 'Open' ELSE 'Closed' END,
       CONVERT(decimal(10,2),value%1000)
FROM GENERATE_SERIES(1,100000,1);
CREATE INDEX IX_SearchOrders_Customer
ON dbo.SearchOrders(CustomerID) INCLUDE(OrderState,Amount);
GO
CREATE OR ALTER PROCEDURE dbo.SearchOrdersByCustomer @CustomerID int=NULL
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID,CustomerID,OrderState,Amount
    FROM dbo.SearchOrders
    WHERE CustomerID=@CustomerID OR @CustomerID IS NULL;
END;
GO

Capture actual plans and resource measurements for NULL, the common customer, and a selective customer. Consider the result volume and client consumption as well. An unfiltered operation still has to deliver or aggregate its required data; plan optimization cannot eliminate a valid request for a large result.

Keep execution order visible during a plan-cache experiment. A warm reusable plan and a fresh compilation test answer different questions. Use the same data, indexes, settings, and statements when comparing alternatives so the experiment changes the intended variable rather than several unrelated conditions.

Recompile Catch-All Queries When the Cost Fits

Statement recompilation lets the optimizer consider the current parameter values for each execution. It can simplify the optional predicate for that call. The tradeoff is repeated compilation work and reduced reuse for that statement, so evaluate it under the actual request rate.

DECLARE @CustomerID int=2;
SELECT OrderID,CustomerID,OrderState,Amount
FROM dbo.SearchOrders
WHERE CustomerID=@CustomerID OR @CustomerID IS NULL
OPTION(RECOMPILE);

This is a straightforward candidate when the execution work dominates an acceptable compile cost or request frequency is modest. It is less attractive when a very frequent tiny operation spends significant resources compiling. Measure both sides. Recompilation is a strategy with an operating cost, not a generic punishment for a query with parameters.

Confirm that results match the original contract for every optional combination. A rewrite can look faster because it accidentally excludes a required row or applies a filter that NULL was supposed to disable. Retain correctness tests alongside plan and duration comparisons.

Generate Only the Required Parameterized Predicates

Dynamic SQL can create a stable statement shape for each supplied filter combination. Concatenate only fixed approved SQL fragments and pass user values as typed parameters. Do not concatenate the customer or state value into the command text.

DECLARE @CustomerID int=2,@OrderState varchar(10)=NULL;
DECLARE @Statement nvarchar(max)=
    N'SELECT OrderID,CustomerID,OrderState,Amount FROM dbo.SearchOrders WHERE 1=1';
IF @CustomerID IS NOT NULL
    SET @Statement+=N' AND CustomerID=@CustomerID';
IF @OrderState IS NOT NULL
    SET @Statement+=N' AND OrderState=@OrderState';
EXEC sys.sp_executesql @Statement,
    N'@CustomerID int,@OrderState varchar(10)',
    @CustomerID=@CustomerID,@OrderState=@OrderState;

Match parameter types to the columns, including length and Unicode requirements. Reuse can still create ordinary sensitivity within one statement shape. A selective customer and the common customer now share an equality-filtered shape, so the remaining workload still needs evaluation. Dynamic SQL does not guarantee one ideal plan for every value.

Keep fragment ordering consistent so equivalent combinations produce the same intended text. Review execution permissions and the module boundary for the actual application design. I treat parameterized construction as code that needs a clear security contract rather than a string-building shortcut.

Inspect the SQL Server 2025 Feature Prerequisites

Optional parameter plan optimization is available in SQL Server 2025 and requires compatibility level 170 with its scoped configuration enabled. It is enabled by default under that supported configuration, but inspect the actual database. An upgraded engine does not guarantee that every existing database changed compatibility level.

SELECT name,compatibility_level FROM sys.databases WHERE database_id=DB_ID();
SELECT name,value FROM sys.database_scoped_configurations
WHERE name=N'OPTIONAL_PARAMETER_OPTIMIZATION';

A reviewed lab change can enable the prerequisites. Compatibility changes affect more than this feature, so test the broader workload before applying them to an existing production database.

ALTER DATABASE OptionalSearchLab SET COMPATIBILITY_LEVEL=170;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION=ON;

Run the scoped command in OptionalSearchLab. The database and table must belong to the same intended lab context. Preserve the original configuration for reversal and compare the optional-filter procedure under the accepted test cases.

Verify Dispatcher and Variant Evidence

For eligible statements, the feature can use a dispatcher and query variants for different optional-parameter conditions. Inspect the actual plan and supported diagnostic information to establish whether it applied. Do not invent a selection threshold or assume every optional predicate receives the optimization.

EXEC dbo.SearchOrdersByCustomer @CustomerID=NULL;
EXEC dbo.SearchOrdersByCustomer @CustomerID=1;
EXEC dbo.SearchOrdersByCustomer @CustomerID=2;
SELECT name,description FROM sys.dm_xe_objects
WHERE object_type=N'event'
  AND name IN(N'optional_parameter_optimization_skipped_reason',
              N'query_with_optional_parameter_predicate');

The supported events can explain eligibility and skipped optimization when captured through a reviewed session. Plan evidence should show the relevant dispatcher and variant behavior rather than only a faster stopwatch result. Internal plan annotations are diagnostic output, not hints to paste manually into application SQL.

Which parameter combination still misses the application's latency target? Compare that case with the alternatives instead of declaring the feature a universal fix. Catch-all queries remain subject to indexing, result volume, data skew, and the exact supported eligibility rules.

Choose a Strategy for Catch-All Queries From Measured Behavior

Keep the application call path in the final trial as well. Client parameter types and supplied NULL values can differ from a manually typed example. Verify the actual request shape under the intended connection identity before accepting a query-window result as application evidence.

Retain the original contract, test cases, selected approach, and observed plans. Compare compile cost, execution resources, concurrency, and maintainability. Different search workloads can justify different strategies, and a supported automatic feature still needs acceptance evidence for the actual statement.

Catch-all queries become manageable when their optional meanings and access requirements are explicit. Use recompilation, parameterized shapes, or the supported optimization according to measured workload behavior. Keep the decision revisitable as the data and search patterns change.

Related reading on this blog: OPTIMIZE FOR and RECOMPILE Hints Compared and Parameter Sniffing and Bad Plan.

An optional filter is not one fixed access requirement, it is a query contract whose different cases need verified plan behavior.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.