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 fo...
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.
