Seek predicate not using all available columns

I have a strange query compilation issue which is hard to reproduce. It only happens under high load and cannot be easily repeated.

• There is a table T with columns A,B,C,D.

• There is a non-unique clustered index on T(A, B, C, D).

• There is a query SELECT * FROM T WHERE A=@P1 AND B=@P2 AND (C=@P3 OR

C=@P4) AND D=@P5. The seek condition is on all columns of the
clustered index, the 3-rd column has an OR.

The problem is that the query plan for this query has Seek Predicate only on A and B! The predicate on C and D is an ordinary predicate, so this means that search tree on columns C and D is not utilized.

The data types for all parameters match column data types.

Could anyone provide any hints on why this could be happening? SQL version is 2008 R2 (SP1) - 10.50.2789.0 (X64)

For a parameterised query It can't just do two seeks on

WHERE A=@P1 AND B=@P2 AND C=@P3 AND D=@P5

and

WHERE A=@P1 AND B=@P2 AND C=@P4 AND D=@P5

Because if @P3 = @P4 that would incorrectly bring back duplicate rows. So it would need an operator that removed duplicates from these first.

From a quick test this end it appears to be dependant upon the size of the table whether or not you get that. In the test below 245/246 rows is the cut off point between plans (this was also the cut off point between the index fitting all on one page and it becoming 2 leaf pages and a root page).

CREATE TABLE T(A INT,B INT,C INT,D INT)

INSERT INTO T
SELECT TOP (245) 1,2,3,5
FROM master..spt_values v1

CREATE CLUSTERED INDEX IX ON T(A, B, C, D)

SELECT index_level,page_count, record_count
FROM sys.dm_db_index_physical_stats(db_id(),object_id('T'),1,NULL, 'DETAILED')

DECLARE @C1 INT = 3,
        @C2 INT = 4

 SELECT * FROM T WHERE A=1 AND B=2 AND (C=@C1 OR C=@C2) AND D=5

 DROP TABLE T

1 Pages / 245 rows

This plan has a seek on A=1 AND B=2 with a residual predicate on (C=@C1 OR C=@C2) AND D=5

2 leaf Pages / 246 rows

In the second plan the extra operators are responsible for removing any duplicates from @C1,@C2 first before performing the seek(s).

The seek in the second plan is actually a range seek between A=1 AND B=2 AND C > Expr1010 and A=1 AND B=2 AND C < Expr1011 with a residual predicate on D=5. It still isn't an equality seek on all 4 columns. More information about the additional plan operators can be found here.

Adding OPTION (RECOMPILE) does allow it to inspect the parameter values for duplicates at compile time and produces a plan with two equality seeks.

You could also achieve that with

;WITH CTE
     AS (SELECT DISTINCT ( C )
         FROM   (VALUES (@C1),
                        (@C2)) V(C))
SELECT CA.*
FROM   CTE
       CROSS APPLY (SELECT *
                    FROM   T
                    WHERE A=1 AND B=2 AND D=5  AND C = CTE.C) CA

But actually in this test case it would likely be counter productive as having two seeks into the single page index rather than one increases the logical IO.

StackExchange Database Administrators Q#45107, answer score: 8