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

Question

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 < @OrderEndDate
 AND EXISTS (
 SELECT *
 FROM tablebackups.dbo.tblBOrderItem oi 
 WHERE oi.strBxOrderNo = o.strBxOrderNo
 AND oi.decCatItemPrice > 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 particular, I just want to check (EXISTS) if an order has any item where decCatItemPrice > 0.

SQL Server is doing an index scan as you can see in the pictures below.

• Statistics have just been updated.
• The item table has 41,208 rows in test.

Please note, I don't select any columns from the items table.

This item table has 164,309,397 in live. I would like to avoid a scan there.

questions:

Why is SQL Server not doing an index seek?

Are there other factors/things I should consider in order to improve this query?

(4537 row(s) affected) Table 'tblBorder'. Scan count 1, logical reads
116, physical reads 0, read-ahead reads 0, lob logical reads 0, lob
physical reads 0, lob read-ahead reads 0. Table 'tblBorderItem'. Scan
count 1, logical reads 689, physical reads 0, read-ahead reads 0, lob
logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)

enter image description here enter image description here

this is the definition and indexes on table tblBorderItem

 IF OBJECT_ID('[dbo].[tblBorderItem]') IS NOT NULL 
 DROP TABLE [dbo].[tblBorderItem] 
 GO
 CREATE TABLE [dbo].[tblBorderItem] ( 
 [strBxOrderNo] VARCHAR(20) NOT NULL,
 [sintOrderSeqNo] SMALLINT NOT NULL,
 [sintOrderItemStatusId] SMALLINT NOT NULL,
 [sintNameStructureID] SMALLINT NOT NULL,
 [strItemNo] VARCHAR(20) NOT NULL,
 [sintQuantity] SMALLINT NOT NULL,
 [strCurrencyCode] VARCHAR(3) NOT NULL,
 [decCostPrice] DECIMAL(18,4) NOT NULL,
 [decCatItemPrice] DECIMAL(18,2) NOT NULL,
 [decOfferDiscount] DECIMAL(18,2) NOT NULL,
 [decOverrideDiscount] DECIMAL(18,2) NOT NULL,
 [decFinalPrice] DECIMAL(18,2) NOT NULL,
 [decTaxAmount] DECIMAL(18,2) NOT NULL,
 [strBCCurrencyCode] VARCHAR(3) NOT NULL,
 [decBCCostPrice] DECIMAL(18,4) NOT NULL,
 [decBCCatItemPrice] DECIMAL(18,4) NOT NULL,
 [decBCOfferDiscount] DECIMAL(18,4) NOT NULL,
 [decBCOverrideDiscount] DECIMAL(18,4) NOT NULL,
 [decBCFinalPrice] DECIMAL(18,4) NOT NULL,
 [decBCTaxAmount] DECIMAL(18,4) NOT NULL,
 [dtmOrdItemCreated] DATETIME NOT NULL,
 [blnChargeShipping] BIT NOT NULL,
 [lngTimeOfOrderQtyOnHand] INT NULL,
 [sdtmTimeOfOrderDueDate] SMALLDATETIME NULL,
 [lngProdSetSeqNo] INT NULL,
 [lngProdRelationId] INT NULL,
 [lngProdRelationMemberId] INT NULL,
 [decWasPrice] DECIMAL(18,2) NULL,
 [sintOrderItemType] SMALLINT NULL,
 [tsRowVersion] TIMESTAMP NULL,
 [sdtmOrderItemStatusUpdated] SMALLDATETIME NULL,
 CONSTRAINT [PK_tblBOrderItem] 
PRIMARY KEY CLUSTERED 
([strBxOrderNo] asc, [sintOrderSeqNo] asc) 
WITH FILLFACTOR = 100)
 GO
 CREATE NONCLUSTERED INDEX 
 [IX_tblBOrderItem__dtmOrdItemCreated] 
 ON [dbo].[tblBorderItem] ([dtmOrdItemCreated] asc)
 WITH FILLFACTOR = 100
 CREATE NONCLUSTERED INDEX [IX_tblBOrderItem__sintOrderItemStatusId] 
 ON [dbo].[tblBorderItem] ([sintOrderItemStatusId] asc)
 INCLUDE ([sdtmOrderItemStatusUpdated], 
 [sintOrderSeqNo], [strBxOrderNo], [strItemNo])
 WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX [IX_tblBOrderItem__
sintOrderItemStatusId_decFinalPrice_
sdtmOrderItemStatusUpdated_
include_strBxOrderNo] 
 ON [dbo].[tblBorderItem] 
([sintOrderItemStatusId] asc, 
 [decFinalPrice] asc, 
 [sdtmOrderItemStatusUpdated] asc)
 INCLUDE ([strBxOrderNo])
 WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX [IX_tblBOrderItem__strBxOrderNo] 
 ON [dbo].[tblBorderItem] 
([strBxOrderNo] asc)
 WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX [IX_tblBOrderItem__strItemNo] 
 ON [dbo].[tblBorderItem] ([strItemNo] asc)
 WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX 
[IX_tblBOrderItem_decCatItemPrice_INCL] 
 ON [dbo].[tblBorderItem] 
([strBxOrderNo] asc, [sintOrderSeqNo] asc, [decCatItemPrice] asc)
 INCLUDE ([blnChargeShipping], 
[decBCCatItemPrice], [decBCCostPrice], [decBCFinalPrice], 
[decBCOfferDiscount], [decBCOverrideDiscount], 
[decBCTaxAmount], [decCostPrice], [decFinalPrice], 
[decOfferDiscount], [decOverrideDiscount], 
[decTaxAmount], [decWasPrice], [dtmOrdItemCreated], 
[sintOrderItemStatusId], [sintOrderItemType], 
[sintQuantity], [strItemNo])
 WHERE ([decCatItemPrice]>(0))
 WITH FILLFACTOR = 95

this is the definition and indexes on table tblBorder

IF OBJECT_ID('[dbo].[tblBorder]') IS NOT NULL 
DROP TABLE [dbo].[tblBorder] 
GO
CREATE TABLE [dbo].[tblBorder] ( 
[strBxOrderNo] VARCHAR(20) NOT NULL,
[uidOrderUniqueID] UNIQUEIDENTIFIER NOT NULL,
[sintOrderStatusID] SMALLINT NOT NULL,
[sintOrderChannelID] SMALLINT NOT NULL,
[sintOrderTypeID] SMALLINT NOT NULL,
[blnIsBasket] BIT NOT NULL,
[sdtmOrdCreated] SMALLDATETIME NOT NULL,
[sintMarketID] SMALLINT NOT NULL,
[strOrderKey] VARCHAR(20) NOT NULL,
[strOfferCode] VARCHAR(20) NOT NULL,
[lngShippedToParticipantID] INT NOT NULL,
[lngOrderedByParticipantID] INT NOT NULL,
[lngShipToAddressID] INT NOT NULL,
[lngAccountAddressID] INT NOT NULL,
[lngAccountParticipantID] INT NOT NULL,
[lngOrderedByAddressID] INT NOT NULL,
[lngOrderTakenBy] INT NOT NULL,
[strCurrencyCode] VARCHAR(3) NOT NULL,
[decShipFullPrice] DECIMAL(18,2) NOT NULL,
[decShipOfferDisc] DECIMAL(18,2) NOT NULL,
[decShipOverride] DECIMAL(18,2) NOT NULL,
[decShipFinal] DECIMAL(18,2) NOT NULL,
[decShipTax] DECIMAL(18,2) NOT NULL,
[strBCCurrencyCode] VARCHAR(3) NOT NULL,
[decBCShipFullPrice] DECIMAL(18,4) NOT NULL,
[decBCShipOfferDisc] DECIMAL(18,4) NOT NULL,
[decBCShipOverride] DECIMAL(18,4) NOT NULL,
[decBCShipFinal] DECIMAL(18,4) NOT NULL,
[decBCShipTax] DECIMAL(18,4) NOT NULL,
[decTotalAmount] DECIMAL(18,2) NOT NULL,
[decBCTotalAmount] DECIMAL(18,4) NOT NULL,
[decWrittenTotalAmount] DECIMAL(18,2) NULL,
[decBCWrittenTotalAmount] DECIMAL(18,4) NULL,
[blnProRataShipping] BIT NOT NULL,
[blnChargeWithFirstShipment] BIT NOT NULL,
[sintShippingServiceLevelID] SMALLINT NOT NULL,
[sintShippingMethodID] SMALLINT NOT NULL,
[sdtmDoNotShipUntil] SMALLDATETIME NULL,
[blnHoldUntilComplete] BIT NOT NULL,
[tsRowVersion] TIMESTAMP NULL,
CONSTRAINT [PK_tblBOrder] 
PRIMARY KEY CLUSTERED 
([strBxOrderNo] asc) WITH FILLFACTOR = 100)
GO
CREATE NONCLUSTERED INDEX 
[IX_tblBOrder__lngAccountAddressID] 
 ON [dbo].[tblBorder] 
 ([lngAccountAddressID] asc, [sintOrderStatusID] asc)
 WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX 
[IX_tblBOrder__lngAccountParticipantID] 
 ON [dbo].[tblBorder] 
 ([lngAccountParticipantID] asc)
 WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX 
[IX_tblBOrder__lngOrderedByAddressID] 
 ON [dbo].[tblBorder] 
 ([lngOrderedByAddressID] asc, [sintOrderStatusID] asc)
 WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX 
[IX_tblBOrder__lngOrderedByParticipantID] 
 ON [dbo].[tblBorder] ([lngOrderedByParticipantID] asc)
 WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX 
[IX_tblBOrder__lngShippedToParticipantID] 
 ON [dbo].[tblBorder] 
 ([lngShippedToParticipantID] asc)
 WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX 
[IX_tblBOrder__lngShipToAddressID] 
 ON [dbo].[tblBorder] 
 ([lngShipToAddressID] asc, [sintOrderStatusID] asc)
 WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX 
[IX_tblBOrder__sdtmOrdCreated_sintMarketID__include_strBxOrderNo] 
 ON [dbo].[tblBorder] 
 ([sdtmOrdCreated] asc, [sintMarketID] asc)
 INCLUDE ([strBxOrderNo])
 WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX 
[IX_tblBOrder_sdtmOrdCreated_INCL] 
 ON [dbo].[tblBorder] 
 ([sdtmOrdCreated] asc)
 INCLUDE ([decBCShipFinal], [decBCShipFullPrice], 
 [decBCShipOfferDisc], [decBCShipOverride], 
 [decBCShipTax], [decBCTotalAmount], [decBCWrittenTotalAmount], 
 [decShipFinal], [decShipFullPrice], [decShipOfferDisc], 
 [decShipOverride], [decShipTax], [decTotalAmount], 
 [decWrittenTotalAmount], [lngAccountParticipantID], 
 [lngOrderedByParticipantID], [sintMarketID], 
 [sintOrderChannelID], [sintOrderStatusID], 
 [sintOrderTypeID], [strBxOrderNo], [strCurrencyCode], 
 [strOfferCode], [strOrderKey])
 WITH FILLFACTOR = 100
CREATE NONCLUSTERED 
INDEX [IX_tblBOrder_sintMarketID_sdtmOrdCreated] 
 ON [dbo].[tblBorder] 
 ([sintMarketID] asc, [sdtmOrdCreated] asc)
 INCLUDE ([sintOrderChannelID], [strBxOrderNo])
 WITH FILLFACTOR = 100
CREATE NONCLUSTERED 
INDEX [IX_tblBOrder__sintOrderChannelID_sdtmOrdCreated_INCL] 
 ON [dbo].[tblBorder] 
 ([sintOrderChannelID] asc, [sdtmOrdCreated] asc)
 INCLUDE ([decBCShipFinal], [decBCShipFullPrice], 
 [decBCShipTax], [decShipFinal], [decShipFullPrice], 
 [decShipTax], [lngAccountParticipantID], [sintMarketID], 
 [sintOrderTypeID], [strBxOrderNo], 
 [strCurrencyCode], [strOrderKey])
 WITH FILLFACTOR = 100
CREATE NONCLUSTERED INDEX [IX_tblBOrder_strBxOrderNo_sdtmOrdCreated_incl] 
 ON [dbo].[tblBorder] ([strBxOrderNo] asc, 
 [sdtmOrdCreated] asc)
 INCLUDE ([sintOrderChannelID], [sintOrderTypeID], [sintMarketID], 
 [strOrderKey], [lngAccountParticipantID], [strCurrencyCode], 
 [decShipFullPrice], [decShipFinal], [decShipTax], 
 [decBCShipFullPrice], [decBCShipFinal], 
 [decBCShipTax])

Conclusion

I applied my index on the LIVE system, and updated my stored procedure to use SMALLDATETIME, in order to match the data types in the database for the columns involved.

After that, when looking at the query plan I see the picture below:

enter image description here

enter image description here

it was exactly how I wanted it to be.

I think the query optimizer in this case did a good work to get the best query plan on both environments, and I am glad I did not add any query hints.

I learned with the 3 answers posted. thanks to Max Vernon, Paul White and Daniel Hutmacher for their answers.

Answer 1

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:

<ScalarOperator ScalarString="GetRangeWithMismatchedTypes([@OrderStartDate],NULL,(22))">
<ScalarOperator ScalarString="GetRangeWithMismatchedTypes(NULL,[@OrderEndDate],(10))">

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.

Answer 2

SQL Server is doing an index scan since it thinks that is cheaper than seeking to each required row. Most likely, SQL Server is correct, given the choices it has in your setup.

Be aware SQL Server may actually be doing a range scan on the index, as opposed to scanning the entire index.

If you provide the DDL for both tables, along with the other indexes you may have, we may be able to help you make this much less resource intensive.

As a side note, never ever use date literals like that. Instead of:

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

use this:

DECLARE @OrderStartDate DATETIME2 = '2016-02-27T00:00:00.0000';
DECLARE @OrderEndDate DATETIME2 = '2016-02-28T00:00:00.0000';

Aaron's post may help clarify that.

Answer 3

To add to Max's answer, I would probably try to split your query into two parts:

DECLARE @OrderStartDate DATETIME2 = {d '2016-02-27'};
DECLARE @OrderEndDate DATETIME2 = {d '2016-02-28'};
--- Work variable declarations:
DECLARE @minOrderNo varchar(20), @maxOrderNo varchar(20);
--- Find the lowest and highest order number respectively for
--- your date range:
SELECT @minOrderNo=MIN(strBxOrderNo),
 @maxOrderNo=MAX(strBxOrderNo)
FROM dbo.tblBOrder o
WHERE o.sdtmOrdCreated >= @OrderStartDate AND
 o.sdtmOrdCreated < @OrderEndDate;
--- Join orders and order items on their respective clustering keys.
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 dbo.tblBOrder AS o
INNER /*MERGE*/ JOIN dbo.tblBOrderItem AS oi ON
 o.strBxOrderNo>=@minOrderNo AND --- OrderNo filter on "orders"
 o.strBxOrderNo<=@maxOrderNo AND
 oi.strBxOrderNo=o.strBxOrderNo AND --- Equijoin
 oi.strBxOrderNo>=@minOrderNo AND --- OrderNo filter on "order items"
 oi.strBxOrderNo<=@maxOrderNo AND
 oi.decCatItemPrice > 0 --- Item price filter on "order items"
OPTION (RECOMPILE);

This query will (a) eliminate the Sort operator (which is expensive because it is blocking and requires a memory grant), (b) create a Merge Join (which you could force with a join hint, but it should happen automatically with enough data). As a bonus, it'll also (c) eliminate the Index Scan.

All in all, the MIN/MAX query uses a highly optimal index on the Orders table to identify a range of order numbers (included in the clustering key) from a non-clustered index on the date column:

MIN/MAX query plan

Then, you can Merge Join the two tables on their respective clustered indexes:

Merge Join of orders and order items

Obviously, I don't have your data to test with, but I imagine this should be a really well-performing solution.

• Marcello Miorelli: +1 I really liked the way of thinking here. It did not work for this case scenario in particular but I got the idea.