Why is my EXISTS query doing an index scan instead of an index seek?

I am working on optimizing some queries.

For the query below,

SET STATISTICS IO ON;
DECLARE @OrderStartDate DATETIME2 = '27 feb 2016';
DECLARE @OrderEndDate  DATETIME2 = '28 feb 2016';

SELECT  o.strBxOrderNo
        , o.sintOrderStatusID
        , o.sintOrderChannelID
        , o.sintOrderTypeID
        , o.sdtmOrdCreated
        , o.sintMarketID
        , o.strOrderKey
        , o.strOfferCode
        , o.strCurrencyCode
        , o.decBCShipFullPrice
        , o.decBCShipFinal
        , o.decBCShipTax
        , o.decBCTotalAmount
        , o.decWrittenTotalAmount
        , o.decBCWrittenTotalAmount
        , o.decBCShipOfferDisc
        , o.decBCShipOverride
        , o.decTotalAmount
        , o.decShipTax
        , o.decShipFinal
        , o.decShipOverride
        , o.decShipOfferDisc
        , o.decShipFullPrice
        , o.lngAccountParticipantID
        , CONVERT(DATE, o.sdtmOrdCreated, 120) as OrderCreatedDateConverted
FROM    tablebackups.dbo.tblBOrder o
WHERE   o.sdtmOrdCreated >= @OrderStartDate
        AND o.sdtmOrdCreated  0
        )
OPTION (RECOMPILE);

I have created the following FILTERED index:

-- table dbo.tblBorderItem
CREATE NONCLUSTERED INDEX IX_tblBOrderItem_decCatItemPrice_INCL 
ON dbo.tblBorderItem 
( 
     strBxOrderNo ASC
    , sintOrderSeqNo ASC
    , decCatItemPrice   
)   
INCLUDE 
(
    blnChargeShipping
    , decBCCatItemPrice
    , decBCCostPrice
    , decBCFinalPrice
    , decBCOfferDiscount
    , decBCOverrideDiscount
    , decBCTaxAmount
    , decCostPrice
    , decFinalPrice
    , decOfferDiscount
    , decOverrideDiscount
    , decTaxAmount
    , decWasPrice
    , dtmOrdItemCreated
    , sintOrderItemStatusId
    , sintOrderItemType
    , sintQuantity
    , strItemNo
)  
WHERE decCatItemPrice > 0 
WITH (DROP_EXISTING = ON, FILLFACTOR = 95);

This index is not used only for this query in particular, there are other queries that use this same index, therefore the INCLUDED columns.

For this query in particula

If you want good results from the query optimizer, it pays to be careful about data types.

Your variables are typed as datetime2:

DECLARE @OrderStartDate datetime2 = '27 feb 2016';
DECLARE @OrderEndDate  datetime2 = '28 feb 2016';

But the column these are compared to is typed smalldatetime (as the sdtm prefix suggests!):

[sdtmOrdCreated] SMALLDATETIME NOT NULL

The type incompatibility makes it hard for the optimizer to work out the resulting cardinality estimate through a type conversion, as shown in the execution plan xml:





The current estimate may or may not be accurate (probably not). Fixing the type incompatibility may or may not completely solve your plan selection problem, but it is the first (easy!) thing I would fix before looking deeper into the issue:

DECLARE @OrderStartDate smalldatetime = CONVERT(smalldatetime, '20160227', 112);
DECLARE @OrderEndDate smalldatetime = CONVERT(smalldatetime, '20160228', 112);

Always check the accuracy of cardinality estimates, and the reason for any discrepancy before deciding to rewrite the query or use hints.

See my SQLblog.com article, "Dynamic Seeks and Hidden Implicit Conversions" for more details on the dynamic seek.

Update: Fixing the data type got you the seek plan you wanted. The cardinality estimation errors caused by the type conversion before gave you the slower plan.

StackExchange Database Administrators Q#132689, answer score: 16