Query processor ran out of internal resources and could not produce a query plan

If you happen to see the below error in your SQL Server log file, and not sure what’s it related to and why is it logged, here’s what you should be doing-

Error: 8623, Severity: 16, State: 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 look closely into the error, it states that the query processor ran out of internal resources and could not produce a query plan. When the query processor finds itself consuming a lot of time optimizing a query, it may decide to stop the optimization process abruptly, and choose the best available plan. This is to ensure that the optimizer doesn’t end up optimizing forever.

There are trace flags available which are meant to be used on a session level to avoid such issues.
But if such issue happens regularly, you should approach session owner, and advise them to fine tune the queries or use Database Tuning Advisor, and apply the recommendations.

Happy Learning!

Cheers!
Aman Kharbanda