Reputation: 7005

Why is query NOT using my index?

CREATE TABLE [LB].[Orders]
(
    [OrderID] [bigint] IDENTITY(1,1) NOT NULL,
    [OrderDate] [date] NOT NULL,
    [Status] [nvarchar](max) NULL,

    CONSTRAINT [PK_MasterOrderID] 
        PRIMARY KEY CLUSTERED ([OrderID] ASC)
) 

CREATE NONCLUSTERED INDEX [PK_Index]  
    ON [BTP_NYA].[LB].[Orders] ([OrderDate]); 

CREATE UNIQUE NONCLUSTERED INDEX [IX_OrderID_OrderDate]  
    ON [BTP_NYA].[LB].[Orders] ([OrderDate],[OrderID]);

I am trying to speed up this query:

SELECT * FROM [BTP_NYA].[XX].[Orders] WHERE [OrderDate] = '20170921' AND [OrderID] = 62192

Why does this query NOT USE my IX_OrderID_OrderDate Index? How else can I make is faster?

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.2" Build="12.0.4232.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="0.0032831" StatementText="SELECT * FROM [BTP_NYA].[LB].[Orders] WHERE [OrderDate]=@1 AND [OrderID]=@2" StatementType="SELECT" QueryHash="0x547011333B745205" QueryPlanHash="0xD16E72141B86DC03" RetrievedFromCache="true">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="32" CompileTime="0" CompileCPU="0" CompileMemory="144">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="836385" EstimatedPagesCached="209096" EstimatedAvailableDegreeOfParallelism="2" />
            <RelOp AvgRowSize="12369" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="59123">
              <OutputList>
                <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="OrderID" />
                <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="OrderDate" />
                <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="Status" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="OrderID" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="OrderDate" />
                  </DefinedValue>
                  <DefinedValue>
                    <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="Status" />
                  </DefinedValue>
                </DefinedValues>
                <Object Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Index="[PK_MasterOrderID]" IndexKind="Clustered" Storage="RowStore" />
                <SeekPredicates>
                  <SeekPredicateNew>
                    <SeekKeys>
                      <Prefix ScanType="EQ">
                        <RangeColumns>
                          <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="OrderID" />
                        </RangeColumns>
                        <RangeExpressions>
                          <ScalarOperator ScalarString="CONVERT_IMPLICIT(bigint,[@2],0)">
                            <Convert DataType="bigint" Style="0" Implicit="true">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Column="@2" />
                                </Identifier>
                              </ScalarOperator>
                            </Convert>
                          </ScalarOperator>
                        </RangeExpressions>
                      </Prefix>
                    </SeekKeys>
                  </SeekPredicateNew>
                </SeekPredicates>
                <Predicate>
                  <ScalarOperator ScalarString="[BTP_NYA].[LB].[Orders].[OrderDate]=CONVERT_IMPLICIT(date,[@1],0)">
                    <Compare CompareOp="EQ">
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Database="[BTP_NYA]" Schema="[LB]" Table="[Orders]" Column="OrderDate" />
                        </Identifier>
                      </ScalarOperator>
                      <ScalarOperator>
                        <Convert DataType="date" Style="0" Implicit="true">
                          <ScalarOperator>
                            <Identifier>
                              <ColumnReference Column="@1" />
                            </Identifier>
                          </ScalarOperator>
                        </Convert>
                      </ScalarOperator>
                    </Compare>
                  </ScalarOperator>
                </Predicate>
              </IndexScan>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@2" ParameterCompiledValue="(62192)" ParameterRuntimeValue="(62192)" />
              <ColumnReference Column="@1" ParameterCompiledValue="'20170921'" ParameterRuntimeValue="'20170921'" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Upvotes: 13

Answers (2)

Dan Guzman

Reputation: 46202

The reason the non-clustered index is not used is because it is more efficient to select the single row using the unique primary key clustered index. You can't get any faster than that to select all columns for a single row (barring a hash index on an in-memory table).

Upvotes: 23

Raphael Müllner

Reputation: 438

It does a clustered index seek on PK_MasterOrderID.

This is because you do select * and IX_OrderID_OrderDate doesn't include any columns.

If you would create the index with

CREATE UNIQUE NONCLUSTERED INDEX [IX_OrderID_OrderDate]  
ON [BTP_NYA].[LB].[Orders] ([OrderDate],[OrderID]) INCLUDE([all], [selected], [columns]);

, it would be used.

Upvotes: 2

Why is query NOT using my index?

Answers (2)

Related Questions