Microsoft KB Archive/252733

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 16:15, 18 July 2020 by 3155ffGd (talk | contribs) (importing KB archive)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Knowledge Base

BUG: Optimizer Chooses Table Scan Instead of Index Seek

Article ID: 252733

Article Last Modified on 10/17/2003


  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition

This article was previously published under Q252733

BUG #: 57350 (SQLBUG_70)


Under certain conditions a query may issue a table scan instead of using a useful covering nonclustered index.


The optimizer is using the density information from the distribution page statistics to estimate the number of qualifying rows. In WHERE clauses with two or more constant values, the value of the constants can be estimated as too costly for using the index because the average density is worse than the density for these special constant values. This is the reason why the optimizer is not using the index.


To work around this problem, try either of the following:

  • Use an index hint.


  • Change the nonclustered index to a clustered index.


Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Additional query words: performance slow non-clustered

Keywords: kbbug kbpending KB252733