BUG: Optimizer Chooses Table Scan Instead of Index Seek

• Microsoft SQL Server version 7.0

SYMPTOMS

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

CAUSE

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.

WORKAROUND

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

• Use an index hint.
  
  -or-



• Change the nonclustered index to a clustered index.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 7.0.

Additional query words: performance slow non-clustered

Keywords : kbbug7.00 kbSQLServ700bug
Version : winnt:7.0
Platform : winnt
Issue type : kbbug