Microsoft KB Archive/260700

= FIX: Optimizer Selects Table Scan Instead of Index Covering the ORDER BY Clause for a Single Table Select =

Article ID: 260700

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q260700



BUG #: 57868 (SQLBUG_70)



SYMPTOMS
When you execute a SELECT query with an ORDER BY clause that has at least 14 columns against a table with highly redundant data in the leading columns of the ORDER BY clause with 25 or more rows in the table, the optimizer ignores the index that covers the ORDER BY clause and picks the clustered index for a single table query resulting in performance degradation.

This problem can cause the query to run 40 times slower with a table that has 2.8 million rows when the query does not use the correct index.

The optimizer only picks the right index when there are less than 25 rows in the table or when an optimizer hint is added to the query.



WORKAROUND
Use an index hint to force the correct the index.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

254561 INF: How to Obtain Service Pack 2 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.



MORE INFORMATION
A sample statement that causes the problem follows.

NOTE: The inclusion or removal of a WHERE clause makes no difference. select * from TEST order by TEST_SW, TO_COMPANY, FISCAL_YEAR, ACCT_PERIOD, ACCT_UNIT, ACCOUNT, SUB_ACCOUNT, POST_DT, SYSTEM, GROUP_ID, TYPE, LINE_NUM, COMPANY, SEQ_NUM To verify the problem, run the query with the Show query plan option selected.

Samples of three bad query plans follow.

With 25 or 5000 rows in the table: |--Sort(ORDER BY:([TEST5].[TEST_SW] ASC, [TEST5].[TO_COMPANY] ASC, [TEST5].[FISCAL_YEAR] ASC, [TEST5].[ACCT_PERIOD] ASC, [TEST5].[ACCT_UNIT] ASC, [TEST5].[ACCOUNT] ASC, [TEST5].[SUB_ACCOUNT] ASC, [TEST5].[POST_DT] ASC, |--Clustered Index Scan(OBJECT:([gl].[dbo].[TEST5].[GLTSET1])) With 50,000 or more rows in the table: |--Parallelism(Gather Streams, ORDER BY:([TEST].[TEST_SW] ASC, [TEST].[TO_COMPANY] ASC, [TEST].[FISCAL_YEAR] ASC, [TEST].[ACCT_PERIOD] ASC, [TEST].[ACCT_UNIT] ASC, [TEST].[ACCOUNT] ASC, [TEST].[SUB_ACCOUNT] ASC, [TEST].[POST |--Sort(ORDER BY:([TEST].[TEST_SW] ASC, [TEST].[TO_COMPANY] ASC, [TEST].[FISCAL_YEAR] ASC, [TEST].[ACCT_PERIOD] ASC, [TEST].[ACCT_UNIT] ASC, [TEST].[ACCOUNT] ASC, [TEST].[SUB_ACCOUNT] ASC, [TEST].[POST_DT] ASC, [GL |--Clustered Index Scan(OBJECT:([gl].[dbo].[TEST].[GLTSET1])) A big table that has no clustered index: |--Parallelism(Gather Streams, ORDER BY:([TEST2].[TEST_SW] ASC, [TEST2].[TO_COMPANY] ASC, [TEST2].[FISCAL_YEAR] ASC, [TEST2].[ACCT_PERIOD] ASC, [TEST2].[ACCT_UNIT] ASC, [TEST2].[ACCOUNT] ASC, [TEST2].[SUB_ACCOUNT] ASC, [TEST |--Sort(ORDER BY:([TEST2].[TEST_SW] ASC, [TEST2].[TO_COMPANY] ASC, [TEST2].[FISCAL_YEAR] ASC, [TEST2].[ACCT_PERIOD] ASC, [TEST2].[ACCT_UNIT] ASC, [TEST2].[ACCOUNT] ASC, [TEST2].[SUB_ACCOUNT] ASC, [TEST2].[POST_DT] |--Table Scan(OBJECT:([gl].[dbo].[TEST2])) The correct query plans follow.

With the index hint or when the table has less than 25 rows: |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([gl].[dbo].[TEST5]) WITH PREFETCH) |--Index Scan(OBJECT:([gl].[dbo].[TEST5].[GLTSET2]), ORDERED) With the WHERE clause with index hint or only 24 rows of data: |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([gl].[dbo].[TEST5]) WITH PREFETCH) |--Index Seek(OBJECT:([gl].[dbo].[TEST5].[GLTSET2]), SEEK:([TEST5].[TEST_SW]='Y' AND [TEST5].[TO_COMPANY]=1 AND [TEST5].[FISCAL_YEAR]=1999 AND [TEST5].[ACCT_PERIOD]=4 AND [TEST5].[ACCT_UNIT] >= '721501 A') ORDERED)

Keywords: kbbug kbfix KB260700

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.