Data factory fails to run Stored Procedure when SSMS succeeds

Hi Lun, Biondi

Greetings!

As we understand that, This error (SqlErrorNumber=8623) – "The query processor ran out of internal resources and could not produce a query plan" – can be especially frustrating when nothing in your SQL or environment has explicitly changed.

Since you are running the same stored procedure successfully in SSMS but it fails from Azure Data Factory (ADF).

We would like to inform you that, The Query Optimizer cannot generate a query plan due to either insufficient resources or the query's complexity. This error can result in two different states:

• State 1 - The query timed out because the plan was too complex
• State 2 - The query ran out of memory resources

Simplify the query by breaking it into multiple queries along the largest dimension. First, remove any unnecessary query elements, then try adding a temp table and splitting the query into two. Note that moving part of the query to a subquery, function, or common table expression isn't sufficient because they get recombined into a single query by the compiler. You can also try adding hints to force a plan earlier, such as OPTION (FORCE ORDER) https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-8623-database-engine-error?view=sql-server-ver17.

Here are the most likely causes and what you can do to investigate and resolve it:

• Compare the execution context between ADF and SSMS. Run the following in both environments before executing the procedure using DBCC USEROPTIONS;
• It’s possible that a cached plan in ADF’s context is invalid or inefficient. SQL Server might generate different plans for the same proc depending on parameters and session context (plan sniffing). Try adding WITH RECOMPILE to the stored procedure call in ADF to force fresh plan generation or inside the stored procedure, use OPTION (RECOMPILE) for critical queries.

EXEC YourProc @param1 = ..., @param2 = ... WITH RECOMPILE;

Check row count and data distribution on the underlying tables. Manually update statistics Or rebuild indexes on high-use tables if fragmentation is high.

UPDATE STATISTICS YourTableName;

• Check for concurrent jobs or memory-intensive operations during the ADF run time. Review any recent changes in service tier (for Azure SQL) or query concurrency.
• Sometimes the root cause is a transient runtime issue in ADF’s execution environment, especially if it resolved immediately after or didn’t repro elsewhere. Rerun the ADF pipeline manually and see if it reproduces. Enable verbose logging in ADF for more diagnostics and add retry logic if transient failure is suspected.

I would request you to refer the below mentioned documents for more information.

https://sqlundercover.com/2024/10/30/the-query-processor-ran-out-of-internal-resources-and-could-not-produce-a-query-plan-when-youve-managed-to-confuse-sql-with-a-crazy-in-clause/

https://www.mssqltips.com/sqlservertip/5279/sql-server-error-query-processor-ran-out-of-internal-resources-and-could-not-produce-a-query-plan/

I hope this information helps. Please do let us know if you have any further queries.