FIX: Optimizer May Incorrectly Choose Reformat Strategy

• Microsoft SQL Server, version 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.