Index Seek vs. Clustered Index Scan - Why is the scan chosen?

Question

The following query uses an index seek on an index on the LastModifiedTime column.

SELECT 
      CONVERT(varchar, a.ReadTime, 101) as ReadDate,
      a.SubID,
      a.PlantID,
      a.Unit as UnitID,
      a.SubAssembly
FROM dbo.Accepts a WITH (NOLOCK)
WHERE  a.LastModifiedTime BETWEEN '3/3/2010' And '3/4/2010'
AND a.SubAssembly = '400'

The query below, which is almost identical to the above query, uses a clustered index scan, instead of the index on LastModifiedTime. Can anyone tell me why? And, more importantly, what I can do to get SQL Server to use the index on the LastModifiedTime column, without using an index hint.

Declare @LastModifiedTimeEnd dateTime
Declare @LastModifiedTimeStart dateTime

    SELECT 
          CONVERT(varchar, a.ReadTime, 101) as ReadDate,
          a.SubID,
          a.PlantID,
          a.Unit as UnitID,
          a.SubAssembly
    FROM dbo.Accepts a WITH (NOLOCK)
    WHERE  a.LastModifiedTime BETWEEN @LastModifiedTimeStart And @LastModifiedTimeEnd
    AND a.SubAssembly = '400'

Answer 1

The query below, which is almost identical to the above query, uses a clustered index scan, instead of the index on LastModifiedTime. Can anyone tell me why?

The query below does not know the values of the parameters when building the plan and assumes that in general, the clustered index scan is better.

And, more importantly, what I can do to get SQL Server to use the index on the LastModifiedTime column, without using an index hint.

SELECT 
      CONVERT(varchar, a.ReadTime, 101) as ReadDate,
      a.SubID,
      a.PlantID,
      a.Unit as UnitID,
      a.SubAssembly
FROM dbo.Accepts a WITH (NOLOCK)
WHERE  a.LastModifiedTime BETWEEN @LastModifiedTimeStart And @LastModifiedTimeEnd
AND a.SubAssembly = '400'
OPTION (OPTIMIZE FOR (@LastModifiedTimeStart = '3/3/2010', @LastModifiedTimeEnd = '3/4/2010'))

Alternatively, you can add OPTION (RECOMPILE), which will create the different execution plan each time the query is run, taking the parameter values into the account (parameter sniffing).

This, however, does not guarantee that the index will be used.

Answer 2

You can create a plan guide with sp_create_plan_guide. See Optimizing Queries in Deployed Applications by Using Plan Guides. A plan guide will help the optimizer into deciding whether to use the index range seek or the clustered scan.