The Catch-All Report Query

This is the most likely single cause of a slow parameterized report, and it is worth reading closely. If your report has a filter panel with several optional fields, this lesson probably describes your query.

The shape

A report offers optional filters: date range, region, customer, status, product. Users fill in one or two and leave the rest blank. The natural way to write that in one query:

CREATE OR ALTER PROCEDURE dbo.usp_SalesReport
    @FromDate   date = NULL,
    @ToDate     date = NULL,
    @RegionId   int  = NULL,
    @CustomerId int  = NULL,
    @StatusId   tinyint = NULL,
    @ProductId  int  = NULL
AS
BEGIN
    SET NOCOUNT ON;

    SELECT  o.OrderId, o.OrderDate, o.CustomerId, o.RegionId, o.Total
    FROM    dbo.Orders AS o                          -- placeholder table
    WHERE   (@FromDate   IS NULL OR o.OrderDate  >= @FromDate)
      AND   (@ToDate     IS NULL OR o.OrderDate  <  @ToDate)
      AND   (@RegionId   IS NULL OR o.RegionId    = @RegionId)
      AND   (@CustomerId IS NULL OR o.CustomerId  = @CustomerId)
      AND   (@StatusId   IS NULL OR o.StatusId    = @StatusId)
      AND   (@ProductId  IS NULL OR o.ProductId   = @ProductId);
END;

It is readable, it is one procedure, it handles every combination, and it is correct. It is also close to the worst possible thing for the optimizer.

Why it performs badly

The plan is compiled once and cached, for whichever parameter combination happened to run first. But the optimizer cannot know at compile time which parameters will be NULL on future executions, so it must produce a plan that is valid for every combination. A plan that seeks on CustomerId would return wrong results when @CustomerId is NULL, so it cannot use one.

The result is a plan that scans and filters — safe for all six parameter combinations, good for none. Six well-chosen indexes sit on the table, unused.

Two follow-on effects:

The OR conditions defeat SARGability even for the parameters that are supplied.
Cardinality estimates for (@x IS NULL OR Col = @x) are guesses, so downstream joins and memory grants are sized wrongly too.

The symptom users report: "the report is slow no matter what I filter on." Filtering to one customer returns four rows and still takes forty seconds, because the plan read the entire table to find them. That specific complaint — narrow filter, tiny result, long runtime — is close to diagnostic.

Confirming it is your problem

Look at the plan for a highly selective parameter set. If a query returning 4 rows shows a Clustered Index Scan over millions, and the WHERE clause has this (@p IS NULL OR ...) shape, you have found it. Cross-check against the plan cache:

SELECT TOP (25)
       qs.execution_count,
       qs.total_elapsed_time / NULLIF(qs.execution_count,0) / 1000 AS avg_ms,
       qs.total_logical_reads / NULLIF(qs.execution_count,0)       AS avg_reads,
       qs.total_rows / NULLIF(qs.execution_count,0)                AS avg_rows,
       SUBSTRING(t.text, 1, 500) AS query_snippet
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE t.text LIKE '%IS NULL OR%'
ORDER BY qs.total_elapsed_time DESC;

High avg_reads with low avg_rows is the signature: reading a great deal to return very little.

Fix 1: OPTION (RECOMPILE)

The simplest effective fix. It tells SQL Server to compile a fresh plan on every execution, with the actual parameter values known.

    SELECT  o.OrderId, o.OrderDate, o.CustomerId, o.RegionId, o.Total
    FROM    dbo.Orders AS o
    WHERE   (@FromDate   IS NULL OR o.OrderDate  >= @FromDate)
      AND   (@ToDate     IS NULL OR o.OrderDate  <  @ToDate)
      AND   (@RegionId   IS NULL OR o.RegionId    = @RegionId)
      AND   (@CustomerId IS NULL OR o.CustomerId  = @CustomerId)
      AND   (@StatusId   IS NULL OR o.StatusId    = @StatusId)
      AND   (@ProductId  IS NULL OR o.ProductId   = @ProductId)
    OPTION (RECOMPILE);

With the values known, the optimizer performs constant folding: @CustomerId IS NULL where @CustomerId is NULL simplifies to TRUE and the whole branch disappears. What remains is exactly the predicates the user supplied — SARGable, and eligible to use your indexes. It also gets accurate cardinality estimates for the actual values.

The cost: a compile on every execution. For a report running a few dozen times a day, that compile is a few milliseconds against a query taking tens of seconds — an excellent trade. For a query running thousands of times per minute, it is not; the CPU spent compiling can exceed the savings. Reports are firmly in the first category, which is why this is the standard answer for reporting procedures specifically.

Judge it by watching CPU before and after, and by checking that plan-cache pressure has not moved.

Fix 2: dynamic SQL

Build only the predicates that were supplied. More work, and it scales better to very high execution counts because each distinct filter shape gets its own cached, reusable plan.

CREATE OR ALTER PROCEDURE dbo.usp_SalesReport_Dynamic
    @FromDate   date = NULL,
    @ToDate     date = NULL,
    @RegionId   int  = NULL,
    @CustomerId int  = NULL,
    @StatusId   tinyint = NULL,
    @ProductId  int  = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @sql nvarchar(max) = N'
        SELECT o.OrderId, o.OrderDate, o.CustomerId, o.RegionId, o.Total
        FROM   dbo.Orders AS o
        WHERE  1 = 1';

    IF @FromDate   IS NOT NULL SET @sql += N' AND o.OrderDate  >= @FromDate';
    IF @ToDate     IS NOT NULL SET @sql += N' AND o.OrderDate  <  @ToDate';
    IF @RegionId   IS NOT NULL SET @sql += N' AND o.RegionId    = @RegionId';
    IF @CustomerId IS NOT NULL SET @sql += N' AND o.CustomerId  = @CustomerId';
    IF @StatusId   IS NOT NULL SET @sql += N' AND o.StatusId    = @StatusId';
    IF @ProductId  IS NOT NULL SET @sql += N' AND o.ProductId   = @ProductId';

    EXEC sys.sp_executesql
         @sql,
         N'@FromDate date, @ToDate date, @RegionId int,
           @CustomerId int, @StatusId tinyint, @ProductId int',
         @FromDate, @ToDate, @RegionId, @CustomerId, @StatusId, @ProductId;
END;

Two rules that make this safe and effective:

Always parameterize with sp_executesql. Never concatenate user values into the string. Values go in as parameters — that prevents SQL injection and lets each distinct filter shape reuse a cached plan. Concatenating values would produce a new plan for every value and flood the cache.

Only object names may be concatenated, and only from a whitelist. If a report lets the user pick a sort column, validate it against a fixed list or QUOTENAME() it. Never interpolate raw input into identifier position.

The trade-off against OPTION (RECOMPILE): dynamic SQL gets plan reuse, at the cost of harder-to-read code, harder debugging, and the need for careful permission handling (dynamic SQL executes under the caller's context, so ownership chaining does not apply — the caller needs rights on the underlying tables, or you use EXECUTE AS).

Fix 3: branch into separate statements

When there are only two or three meaningful combinations, an IF per shape gives each its own optimal cached plan with no recompile cost and no dynamic SQL:

IF @CustomerId IS NOT NULL
    SELECT ... FROM dbo.Orders WHERE CustomerId = @CustomerId AND OrderDate >= @FromDate;
ELSE
    SELECT ... FROM dbo.Orders WHERE RegionId = @RegionId AND OrderDate >= @FromDate;

This does not scale — six optional parameters means sixty-four combinations — but for two or three dominant patterns it is clean, fast and obvious.

What not to do

Do not write WHERE ISNULL(o.RegionId, -1) = ISNULL(@RegionId, ISNULL(o.RegionId, -1)). It is a compact way to express the same thing and it is comprehensively non-SARGable — a function on the column, on both sides, guaranteeing a scan with no usable estimate.

Which to choose

Report procedure, run tens or hundreds of times a day → OPTION (RECOMPILE). Start here; it is one line, easy to reverse, and usually resolves the problem completely.
High-frequency query, or recompile CPU measurably hurts → dynamic SQL with sp_executesql.
Two or three dominant filter shapes → explicit branches.

The short version

The catch-all pattern — WHERE (@p IS NULL OR Col = @p) for every optional filter — caches one plan that has to be valid for every combination, so it scans and filters and none of the indexes get used. The tell is a report that is slow even when the user filters down to four rows. OPTION (RECOMPILE) lets the optimizer fold out the NULL branches and build a plan for the parameters actually supplied. For a report running a few dozen times a day the compile cost is negligible against the runtime; if the query ran thousands of times a minute, use dynamic SQL with sp_executesql instead, so each filter shape gets its own reusable plan.