0

I recently noticed an error message in my SQL Server error log that said "The query processor ran out of internal resources and could not produce a query plan." I suspect that my server might be running out of memory or CPU resources. However, I'm not sure how to confirm this.

I'm aware of the sysprocesses and sp_whoisactive commands, but I'm not sure if they can tell me whether my server is running out of resources at this moment.

Could someone please provide me with some guidance on how to check if my SQL Server is currently running out of memory or CPU resources? Any help would be appreciated.

1 Answer 1

5

The full error message might possible read:

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.

This might not be due to lack of resources in the conventional meaning (RAM, CPU, IO, ...), but more an isolated issue with a query that has an impact on the internal processing resources of the SQL Server Database Engine...

Possible Reasons

There are multiple reasons why you might receive such an error message. I'll list a couple:

  1. Your queries are just too complex and contain multiple UNION clauses which join multiple results sets into one big table that has to be returned to the client.¨

    e.g.

    SELECT <column> FROM <table> WHERE <predicate>
    UNION
    SELECT <column> FROM <table> WHERE <predicate>
    UNION
    ....
    SELECT <column> FROM <table> WHERE <predicate> as RESULTSET;
    
  2. Your queries contain multiple sub-queries and are just exhausting the internal resources.

    e.g.

    SELECT <column> FROM <table> WHERE <column> = 
        (SELECT <column> FROM <table> WHERE <search_predicate> AND <column>
            (SELECT <column> FROM <table> WHERE <search_predicate>)
        );
    
  3. Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table, and use a SELECT subquery within an IN clause.

    Reference: IN (Transact-SQL) (Microsoft Learn | SQL )

Solutions

  1. Reduce the number of UNION clauses.

  2. Reduce the number of subqueries in your complex statement.

  3. Reduce the number of elements in the WHERE <column> IN (<value1>, <value2>, ... <value999>) clause. Pleas also consider the advice given by Microsoft:

    Simplify the query by breaking the query into multiple queries along the largest dimension. First, remove any query elements that aren't necessary, then try adding a temp table and splitting the query in two. Note that if you move a part of the query to a subquery, function, or a common table expression that 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, for example OPTION (FORCE ORDER).
    Reference: MSSQLSERVER_8623 (Microsoft Learn | SQL)

Answering Your (Other) Question

Could someone please provide me with some guidance on how to check if my SQL Server is currently running out of memory or CPU resources?

  1. That's quite a generic question. Finding the statements that are exhausting the internal resources, might be your better option. (See my initial starting explanation at the top)

  2. If you don't have a baseline of how your SQL Server normally performs and which resources are in use, then how would you know if your SQL Server is exhausting any resources? Consider having a look at the Baseline category over at SQLSkills.

  3. Ok. RAM configuration might be an issue.

  4. Try the First Responder Kit provided by Brent Ozar to see what is actually going on RIGHT NOW!

    • sp_BlitzFirst : Why is my SQL Server slow right now?
    • sp_BlitzCache : Which queries have been using the most resources?
    • sp_BlitzWho : Who's running what queries right now?
  5. Post more details in your question. In order to provide a more detailed response, we would require more information in your question.

    • SQL Server Version
    • SQL Server Edition
    • Size of database
    • Size of tables
    • RAM / CPU
    • Max Degree of Parallelism Setting (SQL Server properties)
    • Cost Threshold for Parallelism setting (SQL Server properties)
  6. Consider hiring a consultant. No, not me...

2
  • 1
    @Fajela Tajkiya Also, to be quite clear, John's point is it's very likely a targeted problem of an individual query or few queries (because of their complexity), not a global problem with your server and it's available hardware.
    – J.D.
    Commented Apr 28, 2023 at 12:37
  • 1
    Thanks @J.D. I have edited my answer slightly to emphasise what I meant in the first section of my response.
    – John K. N.
    Commented Apr 28, 2023 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.