Article ID: 832412
Article Last Modified on 1/2/2004
APPLIES TO
- Microsoft SQL Server 2000 Standard Edition
SYMPTOMS
When you try to run a query that joins multiple tables in Microsoft SQL Server 2000, you receive the following error message:
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.
Keywords: kberrmsg kbprb KB832412
