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.

Additional query words: slow performance showplan

Keywords          : kbusage SSrvGen kbbug6.50 kbfix6.50.sp2 
Version           : 6.5
Platform          : WINDOWS 
Issue type        : 

Last Reviewed: April 4, 1999