Query Processor Ran out of Internal Resources

David Fowler explains an error:

Recently I received a cry for help over Teams. The issue was that an application was throwing up the following SQL error,

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.

I’ll be honest, that’s not one that I had seen before but it seemed pretty self explanatory. the query was just too complex for SQL to cope with. I asked what the query was, the answer was something similar to the snippet below,

Read on to learn what the problem was, as well as David’s answer. David had a simple rewrite retaining the IN clause, though you could also rewrite this with an INNER JOIN or even an EXISTS. One of those two alternative approaches might have a better performance profile, though there are no guarantees.