以下查询对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'
下面的查询与上面的查询几乎完全相同,使用聚簇索引扫描,而不是LastModifiedTime上的索引.谁能告诉我为什么？而且,更重要的是,我可以做些什么来让sql Server在LastModifiedTime列上使用索引,而不使用索引提示.
Declare @LastModifiedTimeEnd dateTime
Declare @LastModifiedTimeStart dateTime
SELECT
CONVERT(varchar,a.SubAssembly
FROM dbo.Accepts a WITH (NOLOCK)
WHERE a.LastModifiedTime BETWEEN @LastModifiedTimeStart And @LastModifiedTimeEnd
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
LastModifiedTimecolumn,without using an index hint.
SELECT
CONVERT(varchar,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'))
或者,您可以添加OPTION(RECOMPILE),它将在每次运行查询时创建不同的执行计划,将参数值带入帐户(参数嗅探).
但是,这并不能保证将使用索引.
