8623 The query processor ran out of internal resources and could not produce a query plan.

Problem description: Last week, I got the error below inside BO.

Error: 8623, Severity: 16, State: 1.
The query processor ran out of internal resources and could not produce a query plan.

Cause: This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. In my case, my customer has been getting the following error when attempting to select records through a query with a large number of entries in the "IN" clause (> 10,000).

 

Resolution: Our recommendation is to simplify the query. You may try divide and conquer approach to get part of the query working (as temp table) and then add extra joins / conditions.

See also remarks in the BOL (https://technet.microsoft.com/en-us/library/ms177682.aspx)
“Including an extremely large number of values (many thousands) in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table.
The large IN clause needs to be changed to a table. “

 

Others workarounds: You could try to run the query using the hint option (force order), option (hash join), option (merge join), option (querytraceon 4102)  with a plan guide. By enabling the traceflag 4102, we will revert the behavior to SQL Server 2000 for handling semi-joins.

 

Interesting KB:
- FIX: A non-yielding scheduler error or an error 8623 occurs when you run a query that contains a large IN clause in SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2
- FIX: Error message when you run a complex query after you install Cumulative Update 3 or Cumulative Update 4 for SQL Server 2005 Service Pack 2: "The query processor ran out of internal resources and could not produce a query plan"

 

Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |