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 &gt; Expr1010 and A=1 AND B=2 AND C &lt; 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.