BUG: Inefficient Access Plan When Using Index for ORDER BY

ID: Q119580

The information in this article applies to:

BUG# OS/2: 1873 (4.2)
       NT:  852 (4.2)

SYMPTOMS

When the optimizer decides to use an index to satisfy an ORDER BY condition, it may use an access plan that results in large numbers of page reads. This can result in slow query performance.


WORKAROUND

Expanding the number of columns in the ORDER BY clause will cause the optimizer to use a worktable for sorting the results instead of attempting to use the index. This will prevent one page I/O for each row from being performed.


STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2b for OS/2 and Microsoft SQL Server version 4.2. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.


MORE INFORMATION

In some cases, the optimizer may decide to traverse an index in order to satisfy an ORDER BY clause if an index exists that fully covers the columns listed in the ORDER BY. If the WHERE clause of such a query contains references to columns that are not the first key of the index being used to satisfy the ORDER BY, SQL Server will scan all leaf rows of the index, performing a page read for each row in the table.

An example of such a query would be: 


   create table testtab( col1 int, col2 int )
   go
   create unique index idx1 on testtab( col1 )
   go
   /* populate table */ 
   go
   select * from testtab
   where col1 > <value> and col2 = <value2>
   order by col1

If the table above contained 20 rows, SQL Server would perform approximately 21 page reads. This can be determined by issuing the query with SET STATISTICS IO ON.

Additional query words: order by index sort 


Keywords          : kbprg SSrvProg kbbug4.20 SSrvWinNT 
Version           : 4.2b | 4.2
Platform          : OS/2 WINDOWS 
Issue type        :

Last Reviewed: March 20, 1999