BUG: Outer Joins with Many Tables May Cause 701 Error Message "..Insufficient System Memory ..." (274030)

SYMPTOMS

When many tables are involved in an outer join, SQL Server may report the following error message during query plan generation:

Server: Msg 701, Level 17, State 99, Line 1 [Microsoft][ODBC SQL Server Driver][SQL Server]There is insufficient system memory to run this query.

While SQL Server supports outer joins with a large number of tables successfully, this behavior has been observed in limited cases that have more than 10 outer joins. The behavior cannot be readily reproduced and may be data-specific.

CAUSE

SQL Server may require a significant amount of memory in order to search an optimal plan during a many-table outer join. If the system is under memory pressure or has limited memory, the error message can occur.

WORKAROUND

To work around this behavior, use either of the following:

To reduce the amount of memory used by the optimizer to search an optimal plan, you can use the Force Order query option hint to force the join order. However, forcing join order may lead to a less than optimal plan, which in turn may degrade performance. For more information about how to use the Force Order query hint, refer to the "OPTION Clause" topic in SQL Server Books Online.

-or-
Increase the max amount of memory for SQL Server.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.