Forum Discussion
SQL Server failing to execute extremely complex queries
A query is failing with this error message on a customer environment:
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.Sorry I cannot post the actual SQL query, which must be massive, but basically it is a union of a lot of tables.
Maybe around 600 tables, maybe several thousands, I don't know exactly how many.
For technical reasons, we cannot really simplify this query.
In the documentation:
https://learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver16
It is specified that the maximum number of tables in a select is limited only by available resources.
Is it possible to increase the size of available internal resources somehow?
- Basicly, memory size limit the scale of query, the frenquacy and number of CPU cores limit the speed of query. As your description, it looks like memory size is bottleneck.
rodgerkong
It makes sense, but is it the true cause of this error?
I suggested the customer to increase the memory, using 'max server memory (MB)' and/or increasing the amount of RAM on the machine.
Maybe we will do some tests configuring a small 'max server memory (MB)' then performing a query on a lot of tables to see if we can reproduce the customer error message.So I made some tests.
I managed to reproduce the customer error message querying a bit more than 380 tables.
Below that the query is successful.
Then I reduced the 'max server memory' to see if I could reproduce the error even with less table if there is less memory available.
But I faced a different error message:
There is insufficient system memory in resource pool 'default' to run this query.
So my conclusion is that the "internal resources" limit reached by the customer might not be linked to the amount of RAM available.
hmm... so, the "internal resources" might be kinds of handles, threads...
Could you rerun the test in a VM, and reduce the memory by decrease the memory allocated to the VM?
In SQL Server logs, I can find:
Error : 8623, Severity : 16, State : 1.
According to the documentation:
https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-8623-database-engine-error?view=sql-server-ver16
State 1 means:
The query timed out due to the plan being too complex
It does not look like a memory issue, which would be State 2.
So I won't investigate more on the memory side.
I tried LEGACY_CARDINALITY_ESTIMATION and COMPATIBILITY_LEVEL = 110 without much success either.So, you have to optimize the query.
If you can't fix this with hardware you will have to consider fixing the query.
If it's a lot of unions maybe you could break the query into sections copying 10 (or so) unioned tables into temp tables, then union the staged tables in a final result set.
