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