PRB: You receive error message 8623 when you try to run a query that joins multiple tables

Article translations Article translations
Article ID: 832412 - View products that this article applies to.
Expand all | Collapse all

SYMPTOMS

When you try to run a query that joins multiple tables in Microsoft SQL Server 2000, you receive the following error message:
Server: Msg 8623, Level 16, State 1, Line 1
Internal Query Processor Error: The query processor could not produce a query plan. Contact your primary support provider for more information.
The query that causes this problem typically involves a table that has a small number of rows (less than 100 rows, for example) and at least one non-clustered index.

CAUSE

This problem occurs because of a costing inconsistency in the SQL Server query optimizer when the cost that is associated with various query plans for this query is calculated. The SQL Server query optimizer makes some adjustments to the basic costing process to encourage the use of a non-clustered index in some cases. In this particular case, those adjustments cause an inconsistency and the SQL Server query optimizer cannot compile the query.

RESOLUTION

To resolve this problem, use trace flag 9136 so that SQL Server does not make the adjustment to the costing process that causes this problem. When the trace flag is enabled, the query compiles and runs successfully.

In some cases, this trace flag can make SQL Server slightly less likely to use a non-clustered index. Typically, those cases involve a table that has a small number of rows and at least one non-clustered index.

Note The symptoms described in this article occur for several reasons. This article describes one possible cause. If the error still occurs after you enable trace flag 9136, it is likely occurring for a reason other than the one described in this article. If the error still occurs, look for other possible causes or contact your primary support provider for more information.

Properties

Article ID: 832412 - Last Review: January 2, 2004 - Revision: 1.3
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kberrmsg kbprb KB832412

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com