Microsoft KB Archive/170295

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 15:35, 18 July 2020 by 3155ffGd (talk | contribs) (importing KB archive)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Knowledge Base

FIX: Wrong Join Plan Selected That Causes Excessive Reads

Article ID: 170295

Article Last Modified on 10/3/2003


  • Microsoft SQL Server 6.5 Service Pack 2

This article was previously published under Q170295

BUG #: 16801 (6.5)


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.


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.


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.


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.

Additional query words: prodsqlslow sluggish high optimization

Keywords: kbbug kbfix kbusage KB170295