Troubleshooting : Error: 8624, Severity: 16, State: 21. Internal Query Processor Error: The query processor could not produce a query plan.

Sometimes we see the below error in SQL Server 2008 R2 SP1 when executing a query in SQL Server Management Studio:

--------------------------------------------------------------

Error: 8624, Severity: 16, State: 116.

Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

--------------------------------------------------------------

 

So, how to handle this situation? I am using SQL Server 2008 R2 SP1 specifically, because some bugs related to the above problem has been fixed in SQL Server 2005 SP2:

https://support.microsoft.com/kb/931329

 

As I am using SQL Server 2008 R2 SP1, so I should not get this error message, but still I am getting this. So, maybe I am doing something wrong which is not the above bug but something different. Let’s check.

 

I have used the below query (well, the logic of the query is not important here) in my SSMS:

---------------------------------------------------------------

select * from

(select c2 "c7", c4 "c8", COUNT(1) "c9"

from Table1

group by c2, c4) Table2

where (c7) not in

(select c7 from

(select c2 "c7", c4 "c8", COUNT(1) "c9"

from Table1

group by c2, c4) Table2,

(select c7 "c11", COUNT(c7) "c12" from

(select c2 "c7", c4 "c8", COUNT(1) "c9"

from Table1

group by c2, c4) Table2

group by c7 having count(c7)=1) Table3

where Table2.c7=Table3.c11 and Table2.c9>1)

---------------------------------------------------------------

 

The query failed with:

Error: 8624, Severity: 16, State: 116

Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services

 

The error message means that the optimizer couldn’t generate the query plan at all.

 

But why? What’s wrong with the optimizer? The same optimizer is producing plans for the other queries.

First of all, please check whether the stats are updated with Full Scan as the optimizer replies on the stats for generating the best execution plan.

We can update them by using the below command for all the statistics created in the associated tables:

---------------------------------------------------------------

UPDATE STATISTICS SchemaName.TableName(StatsName) WITH FULLSCAN;

---------------------------------------------------------------

 

After updating the stats try to run the query. If it still gives the same error, then the cause of the above error can be:

1. The SET operators are not correctly set in the instance.

2. This query is really complex and optimizer timed out while generating the plan.

3. Maybe we didn’t write the query in optimal way.

4. Maybe we are missing some indexes or statistics from the database.

 

Now, the point no. 3 is something which the developers should look into to make the query simpler and optimized.

So, I will focus on point no. 1, 2 and 4 because these are the points which we can improve as a DBA.

 

If we are getting this error, then we should start from checking the non-default SET options.

We can get this from TextData column value in profiler traces (we need to select ‘Existing Connection’ and ‘Audit Login’ events).

We can also get the values from SSMS (if we are using that as client to execute the query):

Click on ‘Tools’ in SSMS à ‘Options’ à Expand ‘Query Execution’ à Expand ‘SQL Server’ à Click on ‘Advanced’

 

 

 

The default SET OPTIONS for a SSMS Query Window executing a query are:

1. quoted_identifier

2. arithabort

3. ansi_null_dflt_on

4. ansi_warnings

5. ansi_padding

6. ansi_nulls

7. concat_null_yields_null

 

(Note: To check for the correct SET operators to ensure that we have correct connection options:

https://msdn.microsoft.com/en-us/library/ms175088(v=sql.105).aspx)

 

If all the SET options are set correctly, so now we know that the optimizer is not able to produce the query plan because of the complexity (join order, where clause, nested tables etc.), so we need to tell the optimizer not to use its own logic and just follow the join order which we specified in the query and produce the query plan (may be a bad plan but just produce it).

We can achieve the above by specifying a clause at the end of the query: OPTION (FORCE ORDER)

 

So, I added that clause at the end of the complex query:

---------------------------------------------------------------

select * from

(select c2 "c7", c4 "c8", COUNT(1) "c9"

from Table1

group by c2, c4) Table2

where (c7) not in

(select c7 from

(select c2 "c7", c4 "c8", COUNT(1) "c9"

from Table1

group by c2, c4) Table2,

(select c7 "c11", COUNT(c7) "c12" from

(select c2 "c7", c4 "c8", COUNT(1) "c9"

from Table1

group by c2, c4) Table2

group by c7 having count(c7)=1) Table3

where Table2.c7=Table3.c11 and Table2.c9>1)

OPTION (FORCE ORDER)

---------------------------------------------------------------

 

Now, executed the query and it gave the result. It means that the optimizer produced the query plan (may be a bad plan, but at least it produced that).

It means that now we can run the DTA against this query.

 

The rest of the thing is pretty simple. Just execute the query in DTA by right click on the query window and selecting the option “Analyze query in Database Engine Tuning Advisor”.

 

 

It will give all the recommendations of missing indexes and missing stats.

Create them and then run the query again after removing the OPTION (FORCE ORDER) clause from the end of the query.

(Warning: Implement the indexes in a test environment first and test thoroughly to check any performance benefit. If you get the desired performance benefit, then only implement in production.)

 

(Note: If you are still not able to execute the query, then please contact Microsoft Support.)

Written by:
Sandipan Pratihar – Support Engineer, SQL Server Support

Reviewed by:
Balmukund Lakhani – Support Escalation Engineer, SQL Server Support

Comments

• Anonymous
  July 22, 2014
  The comment has been removed

• Anonymous
  July 31, 2014
  Hello Vikas, Does the bulk insert query contain SELECT statement inside it? If yes, then the query processor can face the problem. You can go with the above options. Also, you need to check whether there are very huge number of values in the IN operator of the WHERE clause. For example: select table1.col1, table1.col2, table2.col3, table2.col4 from table1, table2 where table1.col1=table2.col1 and table1.col2 in (1,2,3,4,5,6,7..................................huge number of values) This can also generate the problem.

• Anonymous
  May 09, 2015
  Good

• Anonymous
  November 03, 2015
  Interesting write-up.  Thanks for placing in the public domain.

• Anonymous
  May 27, 2016
  The comment has been removed

• Anonymous
  June 05, 2016
  I run into similar error with postgres. Do you think, you will have 5 minutes to review what I have and may suggest a way to fix it?

• Anonymous
  October 20, 2016
  I've got same error, I 'm working with insert through linked server and the query it's a simple "select" but after hours of test I discovered that the problem is the nvarchar (max) datatype in the destination table, e.g. if you change it to nvarchar(4000) the query works fine.

• Anonymous
  January 09, 2017
  I ran into this issue while running an install script in SQL 2008 SP3. The script would restore a baseline backup and run a series of updates scripts on it. This problem occurred while trying to delete 3 rows based off their PKs from a table with just 12 rows. Based off the suggestion above, by the process of elimination I was able to narrow down the resolution to include "SET QUOTED_IDENTIFIER ON"