Why don't I get an index seek?

Question

Working on SQL Server 2014 and trying to optimize a query. One portion of it is doing a table scan. I distilled the issue down to the simplest form.

There is a field in a table (3rd party product's DB) that has no index and no PK. We frequently search on that field (Form_FKey)

SELECT load_ID FROM [dbo].[tblLoads]
WHERE [Form_FKey] = '87F13E42-B11D-413B-AEBC-A58E4CDB9D3E' 

The exec plan recommends this:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[tblLoads] ([Form_FKey])

I create the index and the warning goes away, BUT I'm still getting a table scan. There are 95K records in the table and this query finds 860 rows.

The predicate says:

[Scale_WH].[dbo].[tblLoads].[Form_FKey]={guid'87F13E42-B11D-413B-AEBC-
A58E4CDB9D3E'}

I even tried casting the string to a uniqueidentifier:

SELECT load_ID FROM [dbo].[tblLoads]
WHERE [Form_FKey] = CAST('87F13E42-B11D-413B-AEBC-A58E4CDB9D3E' AS 
UNIQUEIDENTIFIER)

Can anyone explain why I'm still getting a table scan?

loadid is unique. and is in fact the primary key on the table (BUT no primary key is defined. it's a 3rd party vendor's database so we have to be very careful about modifying the schema). I'm trying to learn more about tuning and this is an example. Assume for this case that it's just a regular column.

My understanding is that it's using the index in the WHERE clause to identify the row, it will then go directly to the row to get other columns not in the index, so the columns in select don't affect whether it does a seek or not. I could be wrong.

QueryPlan XML

<?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.4449.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="2" StatementEstRows="860.314" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="1.70055" StatementText="SELECT [load_ID] FROM [dbo].[tblLoads] WHERE [Form_FKey]=@1" StatementType="SELECT" QueryHash="0x8334E910E51EB276" QueryPlanHash="0xA3BB83D7483E1FB" RetrievedFromCache="false">
          <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="1" CachedPlanSize="16" CompileTime="3" CompileCPU="3" CompileMemory="248">
            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1153433" EstimatedPagesCached="720896" EstimatedAvailableDegreeOfParallelism="5" />
            <RelOp AvgRowSize="39" EstimateCPU="0.105572" EstimateIO="1.59498" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="860.314" LogicalOp="Table Scan" NodeId="0" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="1.70055" TableCardinality="95832">
              <OutputList>
                <ColumnReference Database="[Scale_WH]" Schema="[dbo]" Table="[tblLoads]" Column="Load_ID" />
              </OutputList>
              <RunTimeInformation>
                <RunTimeCountersPerThread Thread="0" ActualRows="860" ActualEndOfScans="1" ActualExecutions="1" />
              </RunTimeInformation>
              <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
                <DefinedValues>
                  <DefinedValue>
                    <ColumnReference Database="[Scale_WH]" Schema="[dbo]" Table="[tblLoads]" Column="Load_ID" />
                  </DefinedValue>
                </DefinedValues>
                <Object Database="[Scale_WH]" Schema="[dbo]" Table="[tblLoads]" IndexKind="Heap" Storage="RowStore" />
                <Predicate>
                  <ScalarOperator ScalarString="[Scale_WH].[dbo].[tblLoads].[Form_FKey]={guid'87F13E42-B11D-413B-AEBC-A58E4CDB9D3E'}">
                    <Compare CompareOp="EQ">
                      <ScalarOperator>
                        <Identifier>
                          <ColumnReference Database="[Scale_WH]" Schema="[dbo]" Table="[tblLoads]" Column="Form_FKey" />
                        </Identifier>
                      </ScalarOperator>
                      <ScalarOperator>
                        <Const ConstValue="{guid'87F13E42-B11D-413B-AEBC-A58E4CDB9D3E'}" />
                      </ScalarOperator>
                    </Compare>
                  </ScalarOperator>
                </Predicate>
              </TableScan>
            </RelOp>
            <ParameterList>
              <ColumnReference Column="@1" ParameterCompiledValue="'87F13E42-B11D-413B-AEBC-A58E4CDB9D3E'" ParameterRuntimeValue="'87F13E42-B11D-413B-AEBC-A58E4CDB9D3E'" />
            </ParameterList>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>

Answer 1

Your new index isn't covering the query as it's missing the load_ID column from the INCLUDE statement (which incidentally is also missing from the index definition). Recreate the index including the additional column and see if you get the expected behavior.

My understanding is that it's using the index in the WHERE clause to identify the row, it will then go directly to the row to get other columns not in the index, so the columns in select don't affect whether it does a seek or not. I could be wrong.

It can do that and execute a row lookup after a Seek operation is performed, but that's never guaranteed. Aaron's alluded to the tipping point, and as explained by Kimberly Tripp in the Why is the tipping point interesting? section, "Narrow (non-covering) nonclustered indexes have fewer uses than often expected (just because a query has a column in the WHERE clause doesn't mean that SQL Server's going to use that index)"

Answer 2

I added load_id as an included column and I got a seek.