Microsoft KB Archive/105347

FIX: Index Incorrectly Chosen When Highest Key Value Used

PSS ID Number: Q105347 Article last modified on 10-31-1994

4.20

OS/2

BUG# OS/2: 1666 (4.2)

= SYMPTOMS =

A query that uses the highest key value of an index runs very slowly. Using showplan indicates that the optimizer is not using the index when it should, or using it when it should not.

This only happens when the high value is used in a greater than comparison (>), or in the high value of a between statement. Using the highest key in an equality test (=), or a value one above or below will show the index being used correctly.

= CAUSE =

If the highest key value is used in a greater than statement, the optimizer will incorrectly not use a beneficial index, and perform a table scan instead.

If the highest key value is used as the high value in a between statement, the optimizer will incorrectly use an index when it should do a table scan. Using the index, in this case, causes more page I/Os to be performed than would be in a table scan, causing the query to run much longer than it should.

= WORKAROUND =

For lookup tables and other tables in which the data content is known beforehand, make sure that the highest value is not used in the comparison operator.

= STATUS =

Microsoft has confirmed this to be a problem in SQL Server version 4.2 for OS/2. This problem was corrected in SQL Server version 4.2b. For more information, contact your primary support provider.

= MORE INFORMATION =

The following script demonstrates the behavior.

use pubs go create table optim ( number int not null, filler char(10) not null) go declare @counter int select @counter=1 while @counter < 768 begin insert optim values (@counter, ‘some text’) select @counter=@counter + 1 end go create nonclustered index number_index on optim (number) go set showplan on set statistics io on go select * from optim where number > 766 go select * from optim where number > 767 go select * from optim where number > 768 go /* Remove the noexec on if you want to see the io / / counts, but at least you will get to see the plan / / without getting 70 rows back. / set noexec on go select  from optim where number between 700 and 766 go select * from optim where number between 700 and 767 go select * from optim where number between 700 and 768 go set noexec off go set showplan off set statistics io off drop table optim go

Additional reference words: 4.20 slow KBCategory: kbprg KBSubcategory: SSrvServer

=
================================================================

Copyright Microsoft Corporation 1994.