索引搜索与聚簇索引扫描 - 为什么选择扫描？

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

以下查询对LastModifiedTime列上的索引使用索引搜索。

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.

下面的查询与上面的查询几乎完全相同，使用聚簇索引扫描，而不是LastModifiedTime上的索引。谁能告诉我为什么？而且，更重要的是，我可以做些什么来让SQL Server在LastModifiedTime列上使用索引，而不使用索引提示。

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'

2 个解决方案

#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?

下面的查询与上面的查询几乎完全相同，使用聚簇索引扫描，而不是LastModifiedTime上的索引。谁能告诉我为什么？

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.

而且，更重要的是，我可以做些什么来让SQL Server在LastModifiedTime列上使用索引，而不使用索引提示。

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).

或者，您可以添加OPTION（RECOMPILE），它将在每次运行查询时创建不同的执行计划，将参数值带入帐户（参数嗅探）。

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

但是，这并不能保证将使用索引。

#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.

您可以使用sp_create_plan_guide创建计划指南。请参阅使用计划指南优化已部署应用程序中的查询。计划指南将帮助优化器决定是使用索引范围搜索还是聚簇扫描。

#1