Query fails on Developer edition but not Standard since updating to SQL2019

Question

Query fails on Developer edition but not Standard since updating to SQL2019

steal

SSC Eights!

Points: 827
More actions
July 13, 2023 at 11:14 pm

#4234595
We have recently migrated from SQL 2016 to SQL 2019. Since then, we have a complex query generated by Entity Framework (it is not a new query) that
- still runs successfully on a SQL2019 Standard edition server with 4 CPU
- but fails to run successfully on a SQL2019 Developer edition server with 8 CPU (memory is the same)
This is the error we get "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"
Both servers have the database in question in Compatibility level 150 (SQL 2019). If we change the compatibility level on the failing server to 140 or lower, the query runs successfully.
Does anyone have ideas what might be causing the problem on the Developer edition server?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 1

Which cardinality estimator has been set for the two databases? Also, have you check to make sure things like MaxDop and Cost Threshold For Parallelism and other settings are identical?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

steal SSC Eights! Points: 827 More actions · Answer 2

Thanks Jeff for the reply. Cardinality Estimation is set the same on both databases and changing it doesn't resolve the issue. All server and database options that you mention (and everything else we could think of to check) match on both environments. The database itself is identical (restored from production).

We are not having the problem in production, but are worried it could pop up since we can't explain why the query is failing in one of our test environments.

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 3

There is an article on the same problem here: https://www.mssqltips.com/sqlservertip/5279/sql-server-error-query-processor-ran-out-of-internal-resources-and-could-not-produce-a-query-plan/

What State did the error return? (1 or 2)

State 1 - The query timed out due to the plan being too complex

State 2 - The query ran out of resources - Memory

Can you paste in the query causing it?

steal SSC Eights! Points: 827 More actions · Answer 4

Interesting article, thank you. The error returned 1 for the state.

Our dev team updated the code that generates the EF query, and that has resolved the problem (so I won't bother posting the original query). Thanks all for the feedback, it would be nice to have a concrete answer but it looks like the exact details here will remain a mystery.

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 5

steal wrote:

Interesting article, thank you. The error returned 1 for the state.
Our dev team updated the code that generates the EF query, and that has resolved the problem (so I won't bother posting the original query). Thanks all for the feedback, it would be nice to have a concrete answer but it looks like the exact details here will remain a mystery.

Heh... another good reason to use stored procedures. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)