Microsoft KB Archive/159445

= FIX: Optimizer May Incorrectly Choose Reformat Strategy =

Article ID: 159445

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q159445



BUG #: 15601 (NT 6.5)



SYMPTOMS
As stated in the SQL Server documentation, when joining tables, SQL Server may, use a reformatting strategy to join the tables and return the qualifying rows. This strategy is considered only as a last resort, when the tables are large and neither table in the join has a useful index.

However, this strategy, when chosen, may result in the query running slower than it would if either the join order of the tables in the query has been forced using the FORCEPLAN statement, or the underlying indexes on the joined tables were used.



WORKAROUND
To work around this problem, try using the SET FORCEPLAN ON statement before running the query.



STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 2 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.



MORE INFORMATION
When the data satisfying the query is largely in cache, the REFORMAT strategy chosen is slower than forcing the table join order or using the underlying indexes. However, when the data is obtained largely from disk, then the REFORMAT strategy chosen by the Optimizer is both valid and significantly faster than forcing the table join order using SET FORCEPLAN ON or using the underlying indexes on the tables.

Additional query words: slow performance showplan

Keywords: kbbug kbfix kbusage KB159445

-

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

© Microsoft Corporation. All rights reserved.