FIX: Optimizer May Incorrectly Choose Reformat Strategy (159445)
The information in this article applies to:
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.
| Modification Type: | Major | Last Reviewed: | 10/3/2003 |
|---|
| Keywords: | kbBug kbfix kbusage KB159445 |
|---|
|