Catch-all queries

In one line: a search procedure with optional filters written as (@p IS NULL OR col = @p) caches one plan that has to work for every combination of filters, and it’s bad for most of them.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/catch-all-queries on GitHub.

WHERE (@CustomerID IS NULL OR CustomerID = @CustomerID)
  AND (@FromDate   IS NULL OR OrderDate >= @FromDate)

What you’ll see

  • A search screen that’s quick for some filters and terrible for others. In the demo, a date-only search read 3,001,773 pages through the catch-all plan, against 10,218 for dynamic SQL.
  • One cached plan for the procedure, scanning an index and looking up rows for searches that should seek or scan something else entirely.

Why it happens

The plan is compiled once and reused for every call, so it can’t assume any parameter is non-NULL. It can’t simply seek on CustomerID, because next time @CustomerID might be NULL. The result is a compromise plan that suits none of the searches.

What to do

  • Dynamic SQL with parameters. Build only the predicates that were supplied and run them with sp_executesql, passing the values as parameters. Each combination gets its own cached plan, reused by later calls with the same combination. In the demo it had the fewest reads on every search. Concatenate predicates, never values, or you get a plan per value and SQL injection.
  • OPTION (RECOMPILE). Each call is compiled with its actual values, the dead branches disappear, and the plan fits. You pay a compile per call: invisible on a report, real at thousands of calls a second.
  • On SQL Server 2025 at compatibility level 170, optional parameter plan optimization targets exactly this pattern. The date search fell to 11,862 reads.

What doesn’t help

  • RECOMPILE on a statement that assigns to variables. With SELECT @x = ... the values weren’t embedded on the tested build, and the catch-all scan came back, compiled fresh on every call: 1,853 reads instead of 122. If your search assigns to variables or OUTPUT parameters, check the plan.
  • col = COALESCE(@p, col). When @p is NULL, rows where col is NULL become NULL = NULL and are silently dropped. The demo returned 900,000 rows instead of 1,000,000, and it was slower too.

Check your server

-- Modules in this database that contain the catch-all or COALESCE pattern.
SELECT OBJECT_SCHEMA_NAME(m.object_id) AS schema_name,
       OBJECT_NAME(m.object_id)        AS module_name,
       HasIsNullOr = CASE WHEN m.definition LIKE '%IS NULL OR%' THEN 1 ELSE 0 END,
       HasCoalesce = CASE WHEN m.definition LIKE '%= COALESCE(@%' THEN 1 ELSE 0 END
FROM sys.sql_modules AS m
WHERE m.definition LIKE '%IS NULL OR%'
   OR m.definition LIKE '%= COALESCE(@%';

It’s a text search, so it finds candidates, not proof. Check each one’s plan.

See it happen

The catch-all-queries demo writes the same search five ways and calls each with four combinations of arguments. It measures the reads and the cached plans, and checks that every version returns the same answer. One doesn’t.

Related: optional parameter plans (SQL Server 2025) · parameter sniffing · non-SARGable predicates

*Measured on SQL Server 2022 CU25 (16.0.4255.1), 8 schedulers, plus CI on

  1. Your numbers will differ; the behaviour shouldn’t.*

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights