locked
The query processor ran out of internal resources and could not produce a query plan..... RRS feed

  • Question

  • Hi Everyone

    I have an application that executes a query that breaks 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 

    I have read in many posts, that this is related to a clause IN returning thousands of records that it is not the case, none of my tables has thousands of records. 

    Along that, my application it is executing the same query in many identical SQL Azure Databases instances and the error it is just happening in one of them.

    Any clues ?

    Regards

    Gaston

    Thursday, December 13, 2012 3:49 PM

Answers

  • Bear in mind that Azure is a multi-tenant environment, so it may be that a particular node has fewer resources available for your query than other nodes, indicating that your problem database resides on a node with limited resources.

    It is possible that various factors such as the way the query is constructed and column statistics etc can lead to an overly large memory request for the query (have a look in the sys.dm_exec_query_memory_grants DMV for how much memory is being requested).

    Please post the query in the first instance as there may be a simple fix.  Otherwise your options may be limited.

    • Marked as answer by Iric Wen Monday, December 24, 2012 8:43 AM
    Tuesday, December 18, 2012 3:40 PM

All replies

  • This is an indication that the query processor ran out of resources trying to compile a plan. Have you looked at your query plan on another database to see what it looks like? Does it appear overly complex?

    Could you post the query plan here?

    Sunday, December 16, 2012 2:03 AM
  • Bear in mind that Azure is a multi-tenant environment, so it may be that a particular node has fewer resources available for your query than other nodes, indicating that your problem database resides on a node with limited resources.

    It is possible that various factors such as the way the query is constructed and column statistics etc can lead to an overly large memory request for the query (have a look in the sys.dm_exec_query_memory_grants DMV for how much memory is being requested).

    Please post the query in the first instance as there may be a simple fix.  Otherwise your options may be limited.

    • Marked as answer by Iric Wen Monday, December 24, 2012 8:43 AM
    Tuesday, December 18, 2012 3:40 PM