99 Red Balloons – The query processor ran out of internal resources and could not produce a query plan

99 Red Balloons – Nena or in my case 109,202 red balloons (values) in the IN Statement caused an error.

Today a user received the message

{“Msg 8623, Level 16, State 1, Line 1

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.”}

WTF…I have never seen this message before. After searching the internet I found that users will receive this message when there are too many values in an IN Statement list. Microsoft explains in the documentation for the In statement.

Remarks

Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, 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, and use a SELECT subquery within an IN clause. +

Error 8623: +

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information. +

Error 8632: +

Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

---

How many values can I put into an in statement and not get the error?

The answer is a complicated calculation based on your CPU speed and network packet sizes. However, just for fun, I went through my list of 109,202 values (someone listed 109,202 loan applications in the IN Statement) to see what value the errors starts to happen.

I found that with integer values in the IN Statement I could use 30,694 rows of integers before the error occurs. I multiplied 30694 x 4 bytes (int size) = 122776 bytes or 123KB.

I figure the IN statement on my server can only handle 123KB of data.

---

How do I fix the error?

Change the query. Either remove all values over the limit or create several IN statements.

For Example:

Original Query: Select * from table where CUST_Number in (1,2,3,4,…109202)

Change to: Select * from table where CUST_Number in (1,2,3,4,…30000) or Cust_Number in (30001,…,60000) or Cust_Number in (60001, …, 90000) or… or Cust_Number in (100000, …,109202)

or