Azure SQL Server - Query failing

Hi Nalini Bhavaraju

The error message

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

means that SQL Server’s query optimizer couldn’t generate an execution plan for your query. Even though your tables contain only a few thousand rows and the query appears straightforward, this error can occur when the optimizer is overwhelmed by internal complexity. In this case, the issue isn’t about the sheer amount of data or the number of tables; rather, it’s about how the join conditions and query structure are being processed internally.

What’s likely happening:

• Internal Complexity in the Query Plan: Although the join between [table1] and [table2] seems simple at a glance, the combination of quoted identifiers (for column names) and the specific join conditions (on production_BOM_Number and production_BOM_Version_Number) might be causing the optimizer to work harder than usual. Sometimes, even seemingly simple join predicates can trigger the generation of a plan whose complexity exceeds the limits of the internal resources allocated for plan generation.
• Resource Limits within the Query Optimizer: SQL Server has internal (hidden) thresholds for how much memory and processing it dedicates to producing a query plan. In this case, despite scaling the vCores up from 2 to 6, those extra processing resources don’t directly address the internal limits of the query optimizer. The error (8623) isn’t about runtime data processing; it’s about plan compilation. Essentially, SQL Server determines the “best” way to execute the query but can’t even complete that process due to internal algorithmic constraints.
• Potential Environmental or Version-Specific Issues: There are known instances where even if the query and data are trivial, certain configurations—especially in modern serverless or Hyperscale environments—can cause the optimizer to hit these internal limits. This behavior might be triggered by subtle changes in how the new environment handles join optimization, statistical estimates, or even a potential bug in the SQL Server version you’re using. Microsoft’s documentation for error 8623 suggests that you should simplify the query if possible, but if the query logic is essential and simple, it might indicate a need to contact Customer Support for a deeper review.

Workarounds and Next Steps:

• Query Hints / Rewriting: You might try guiding the optimizer with hints such as OPTION (FORCE ORDER) or even using a Common Table Expression (CTE) to break the query into simpler parts. Although these are workarounds, they can sometimes help bypass the resource-intensive path the optimizer is taking.
• Review Database Compatibility Level: In some cases, adjusting the compatibility level of the database to a lower version can alter the planning behavior. This is more of a diagnostic step than a permanent fix.

You can try the following steps to troubleshoot and optimize your query:

Update Statistics:

UPDATE STATISTICS [table1];
UPDATE STATISTICS [table2];

Check Indexes:

CREATE INDEX idx_table1_BOM ON [table1] (production_BOM_Number, production_BOM_Version_Number);
CREATE INDEX idx_table2_BOM ON [table2] (production_BOM_Number, production_BOM_Version_Number);

I hope this explanation clarifies why your query is failing despite the seemingly modest workload. Please let me know if you’d like further details or have other questions on optimizing your query or working around this issue.

For more details, please refer the below Documents
MSSQLSERVER_8623
SQL server query processor ran out of internal resources
SQL Server error query processor ran out of internal resources and could not produce a query plan