ERROR 8623 The query processor ran out of internal resources and could not produce a query plan

09 January,2017 by Jack Vamvas

Question: In the SQL Server Error Logs I saw the following message:

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

Is there a way to find the query ? I know I can use something like Extended Events – which would allow me to trap the sql text , but what about finding a query that has executed and triggered this error

Answer: If you don’t have the query , you can use some DMVs to extract large queries .The limitation is that the data from DMVs is refreshed after every SQL Server restart.

Here are some example queries utilitising DMVs which could assist you to track down the query .

SQL Server – Find high impact queries with sys.dm_exec_query_stats

SQL Server – Performance Top Queries by Total CPU Time 

How to find Longest running queries in SQL Server

Look for queries with long IN lists, an unusually  large number of UNIONS & nested sub queries. In my experience these have contributed a large percentage of the reason for these error messages.

On occasion , the root cause may be fixed by a  the service pack upgrade.

Read More

How to Audit user DML with SQL Server Extended Events (SQL ...

How to capture DML for a single logon on database audit specification

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on ERROR 8623 The query processor ran out of internal resources and could not produce a query plan


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer