Microsoft KB Archive/119580

{|
 * width="100%"|

BUG: Inefficient Access Plan When Using Index for ORDER BY

 * }

Q119580

-

The information in this article applies to:


 * Microsoft SQL Server version 4.2x

-

BUG# 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 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 > and col2 = 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 : kbprogramming

Issue type :

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ420OS2