FIX: Wrong Join Plan Selected That Causes Excessive Reads (170295)
The information in this article applies to:
- Microsoft SQL Server 6.5 Service Pack 2 and later
This article was previously published under Q170295
BUG #: 16801 (6.5)
SYMPTOMS
The SQL Server optimizer sometimes does not choose the optimal join order.
It may reformat the larger table involved in a join followed by excessive
logical and physical reads on the worktable, which can introduce serious
performance degradation.
CAUSE
The method used to calculate the cost of each possible join plan has been
altered in SQL Server 6.5 Service Pack 2 to provide more accurate and
thorough estimates. Incidentally, it introduced a very small window where
it may calculate the cost incorrectly. In such cases, the best join plan,
which was chosen by both the release build of SQL Server 6.5 and by Service
Pack 1, is considered to be very costly.
WORKAROUND
The combination of SET FORCEPLAN ON, optimizer hint on index selection, and
possibly trace flag 336 may force the optimizer to use the right join plan.
STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server
version 6.5 Service Pack 2. This problem has been corrected in the latest
U.S. Service Pack for SQL Server version 6.5. For more information, contact
your primary support provider.
Modification Type: | Major | Last Reviewed: | 10/3/2003 |
---|
Keywords: | kbBug kbfix kbusage KB170295 |
---|
|