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,
SELECT *
FROM people
WHERE email IN ('Olivia_Brown8588@cdpfn.name',
'Bryon_Mcnally9621@6ijur.mobi',
'Zara_Partridge6053@voylg.autos',
'Chester_Grant8482@ohqqh.shop',
'Jacob_Swan7467@qu9ml.shop'.....
Now that’s a cut down snippet, there were in fact over 30,000 values in that IN clause! *shocked gasps* 
The knee jerk reaction here, which also was the reaction from some people I spoke to when investigating this is that it’s a SQL resource issue.
Don’t be tempted to fall down that rabbit hole. The problem here, as I’m sure we’ve all guessed is most definitely with the query and specifically the large number of IN clause values.
Explicitly defining values in an IN clause consumes resources. When the number of those values starts getting high (up in the thousands), SQL will eventually say, “enough is enough” and throw its toys out of the pram.
How to Fix It?
My suggestion to deal with the mammoth IN clause was to put all of those values into a temp table and feed that temp table into the IN clause instead.
SELECT *
FROM people
WHERE email in (SELECT email FROM #emails)
And with the change to the code, SQL was able to generate a plan and return the results in under a second.
Thanks for reading and if you happen to be here because you’ve also hit this error, I’d love to hear about the query that caused it and how you managed to fix things.