Data factory fails to run Stored Procedure when SSMS succeeds

Question

Data factory fails to run Stored Procedure when SSMS succeeds

Lun, Biondi 0

I have a nightly job that generates an email via a stored procedure run through azure data factory. It has been in use for about 2 years with no problem. Yesterday it failed without any changes to the SQL

A database operation failed with the following 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.'
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., SqlErrorNumber=8623,Class=16,State=1,

I was able to run the same stored procedure in MSSMS with no issues and I've looked through query analyzer and there were no outstanding issues with performance.

Any idea what's going on?

PratikLad 1,825 Reputation points Microsoft External Staff Moderator

2025-06-12T16:33:28.45+00:00

Hi Lun, Biondi

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
PratikLad 1,825 Reputation points Microsoft External Staff Moderator

2025-06-13T14:00:03.3033333+00:00

Hi Lun, Biondi

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

2 answers

Your answer

PratikLad 1,825 Reputation points Microsoft External Staff Moderator

2025-06-12T16:33:28.45+00:00

Hi Lun, Biondi

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
PratikLad 1,825 Reputation points Microsoft External Staff Moderator

2025-06-13T14:00:03.3033333+00:00

Hi Lun, Biondi

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

I'm very short on time right now, but try this in SSMS:

SET ARITHABORT OFF
EXEC YourSP

You may get the same error.

I guess the reason the query started to fail is that statistics have changed, which caused SQL Server to recompile the query, and then took a different route during optimization.

If this is Azure SQL Database, automatic indexing may also cause you surprises.

In any case, try to identify the problematic query and try to simplify it, for instance by introducing an intermediate temp table or two.

Lun, Biondi 0 Reputation points

2025-06-06T21:48:09.36+00:00

I tried this but the error did not appear. Underneath the stored procedure is just a bunch of simple selects that populate a temp table that gets returned at the end so I don't think there's much to simplify

If there's any other way to identify the problematic query aside from looking at the query analyzer I'd be happy to try
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-06-07T08:28:35.02+00:00

Is this in Azure SQL Database or is this a database on-prem? For the latter case, I have an article Monitor Exceptions in Your Database on my web site that shows how to set up an extended events session to capture exceptions and a view to query the session so that you can see what exceptions that occurred and in which line in which stored procedure.

You can do the same thing in Azure SQL Database, but a few things need to be done differently, and I don't cover that in the article.
Lun, Biondi 0 Reputation points

2025-06-09T07:31:20.3366667+00:00

It is an Azure SQL Database so I'll try to look around and see if I see anything about that
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-06-09T21:13:50.9+00:00

I guess I should update that article to include a version that works for Azure SQL Database as well.

For this particular task, I think the easiest may be to set up a section that includes sp_statement_starting and error_reported and then use Watch Live Data.
Vijayalaxmi Kattimani 3,250 Reputation points Microsoft External Staff Moderator

2025-06-10T08:54:06.18+00:00

Hi Lun, Biondi

We would like to thank you for your feedback provided on survey. We found your recent engagement on this question was rated low. However, we were able to share some additional information that could helped in the later comment. Would you please re-evaluate your feedback.
Vijayalaxmi Kattimani 3,250 Reputation points Microsoft External Staff Moderator

2025-06-11T03:51:38.23+00:00
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.
Vijayalaxmi Kattimani 3,250 Reputation points Microsoft External Staff Moderator

2025-06-12T03:47:55.4+00:00

Hi Lun, Biondi

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Biondi Lun 0 Reputation points

2025-06-16T15:37:59.3633333+00:00

It seems like just refactoring the stored procedure has fixed the issue by moving some sub-queries into temp tables
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2025-06-16T19:59:23.8066667+00:00

It seems like just refactoring the stored procedure has fixed the issue by moving some sub-queries into temp tables

That certainly sounds like a plausible solution. The main problem for a long procedure may be to find out which statement you need to break up. Then again, the longest query will be the prime suspect.

Answer 2

Hi Lun, Biondi

Glad to know Biondi Lun you have solved it. I am collating the data in one place for anyone in the QnA forum to refer to it

Adding to @Erland Sommarskog answer, this error happens at the Query Execution Process. This error is not related to a particular area of code. Sometimes when the query execution is taking more time in the query engine, the process could be suspended and an error raised, which means the query processor has limitations and it doesn’t produce a query plan.

Some workarounds to resolve this:

First, verify server and database level configurations and resource availability (memory, CPU, etc.) as well.
Try to simplify the query rather than deal with a very complex query. If possible, re-write the query and removed unnecessary joins, unions, complex subqueries, conditions, etc.
Try to update indexes and statistics because the problem might be due to outdated statistics.

If this answers your query, do click Accept Answer and Upvote for was this answer helpful.

Share via

Data factory fails to run Stored Procedure when SSMS succeeds

2 answers

Your answer