Index SEEK is not used unless OPTION (RECOMPILE)?

(Question moved from SO)

I have a table (dummy data) with clustered index contains 2 columns :

Now I run those two queries :

declare 
@productid int =1 , 
@priceid  int = 1

SELECT productid,
       t.priceID
FROM   Transactions AS t
WHERE  (productID = @productid OR @productid IS NULL)
       AND (priceid = @priceid OR @priceid IS NULL)  

SELECT productid,
       t.priceID
FROM   Transactions AS t
WHERE  (productID = @productid)
       AND (priceid = @priceid)

The actual execution plan for both queries is :

As you can see , the first one is using SCAN while the second one is using SEEK.

However - adding OPTION (RECOMPILE) to the first query , made the execution plan also to use SEEK :

Friends at DBA chat told me that:


In your query, @productid=1, which means that (productID=@productID OR
@productID IS NULL) can be simplified to (productID=@productID). The
former requires a scan to work with any value of @productID, the
latter could use a seek. So, when you use RECOMPILE, SQL Server will
look at what value you actually have in @productID and make the best
plan for it. With a non-null value in @productID, a seek is best. If
the value of @productID is unknown, the plan has to suit any possible
value in @productID, which would require a scan. Be warned: OPTION
(RECOMPILE) will force a recompile of the plan every time you run it,
which will add a few milliseconds to every execution. Though this is
only a problem if the query runs very frequently.

Also :


If @productID is null, to what value would you seek? Answer: there is
nothing to seek to. All values qualify.

I understand that OPTION (RECOMPILE) forces SQL Server to see what actual values the parameters has , and see if it can SEEK with it.

But now I lose the benefit of ahead-compilation.

Question

IMHO - SCAN will only occur if a param is null .

That's fine - let SQL SERVER create an execution plan for SCAN.

BUT if SQL Server sees that I run this query

Summarizing some of the main points from our chat room discussion:

Generally speaking, SQL Server caches a single plan for each statement. That plan must be valid for all possible future parameter values.

It is not possible to cache a seek plan for your query, because that plan would not be valid if, for example, @productid is null.

In some future release, SQL Server might support a single plan that dynamically chooses between a scan and a seek, depending on runtime parameter values, but that is not something we have today.
General problem class

Your query is an example of a pattern variously referred to as a "catch all" or "dynamic search" query. There are various solutions, each with their own advantages and disadvantages. In modern versions of SQL Server (2008+), the main options are:

• IF blocks

• OPTION (RECOMPILE)

• Dynamic SQL using sp_executesql

The most comprehensive work on the topic is probably by Erland Sommarskog, which is included in the references at the end of this answer. There is no getting away from the complexities involved, so it is necessary to invest some time in trying each option out to understand the trade-offs in each case.
IF blocks

To illustrate an IF block solution for the specific case in the question:

IF @productid IS NOT NULL AND @priceid IS NOT NULL
BEGIN
    SELECT 
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T
    WHERE
        T.productID = @productid
        AND T.priceID = @priceid;
END;
ELSE IF @productid IS NOT NULL
BEGIN
    SELECT 
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T
    WHERE
        T.productID = @productid;
END;
ELSE IF @priceid IS NOT NULL
BEGIN
    SELECT 
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T
    WHERE
        T.priceID = @priceid;
END;
ELSE
BEGIN
    SELECT 
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T;
END;

This contains a separate statement for the four possible null-or-not-null cases for each of the two parameters (or local variables), so there are four plans.

There is a potential problem there with parameter sniffing, which might require an OPTIMIZE FOR hint on each query. Please see the references section to explore these types of subtleties.
Recompile

As noted above an in the question, you could also add an OPTION (RECOMPILE) hint to get a fresh plan (seek or scan) on each invocation. Given the relatively slow frequency of calls in your case (once every ten seconds on average, with a sub-millisecond compilation time) it seems likely this option will be suitable for you:

SELECT
    T.productID,
    T.priceID
FROM dbo.Transactions AS T
WHERE
    (T.productID = @productid OR @productid IS NULL)
    AND (T.priceID = @priceid OR @priceid IS NULL)
OPTION (RECOMPILE);

It is also possible to combine features from the above options in creative ways, to make the most of the advantages of each method, while minimizing the downsides. There really is no shortcut to understanding this stuff in detail, then making an informed choice backed by realistic testing.
Further reading

• Parameter Sniffing, Embedding, and the RECOMPILE Options

• Dynamic Search Conditions in T-SQL by Erland Sommarskog

• High Performance Procedures by Kimberly Tripp

StackExchange Database Administrators Q#130751, answer score: 10