Microsoft KB Archive/169630

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


BUG: 6.5 Parse/Compile Time Is Greater than 6.0 on Large Join

Article ID: 169630

Article Last Modified on 10/3/2003



APPLIES TO

  • Microsoft SQL Server 6.5 Standard Edition



This article was previously published under Q169630

BUG #: 16569 (6.5)

SYMPTOMS

When processing a large join, the parse and compile time as indicated by SET STATISTICS TIME ON can be much greater for SQL Server 6.5 than it was for SQL Server 6.0. In one scenario a 9-way join took approximately 9.4 seconds to parse and compile on SQL Server 6.5, but only 1.3 seconds on version 6.0. Once parsed and compiled, the actual query execution time was similar between SQL Server versions 6.5 and 6.0. The problem happens whether the query is ad-hoc or is in a stored procedure.

This problem should not directly cause additional blocking or deadlocking, because no additional time is spent in the execution phase, and lock duration should be unchanged. However, this problem may use additional CPU resources.

All versions of SQL Server 6.5 through SQL Server 6.5 Service Pack 2 exhibit this behavior.


WORKAROUND

Use trace flag 342, which disables the costing of pseudo merge joins. This significantly reduces the amount of time spent in the parse and compile phase, and in the queries thus far examined has no negative execution performance impact. For instructions on how to use trace flags, see the SQL Server 6.5 documentation.

Alternatively you can use SET FORCEPLAN ON, which forces the join order indicated in the query FROM clause.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Keywords: kbbug kbusage KB169630