Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

SQL Server error query processor ran out of internal resources and could not produce a query plan


By:   |   Last Updated: 2018-04-12   |   Comments   |   Related Tips: More > Query Plans

Problem

We recently upgraded from SQL Server 2014 to SQL Server 2016 and started to get these error messages "Msg 8623, Level 16, State 1, Line 1 The query processor ran out of internal resources and could not produce a query plan...”.  In this tip I will discuss this error for more clarification.

Solution

When dealing with complex queries, the query processor may take a lot of time to build a query plan and during this execution the query optimizer cannot guarantee to optimize the plan due to lack of processes and resources and the optimizer stops the process and raises an error like: “The query processor ran out of internal resources and could not produce a query plan...” In this tip I will show how you can recreate this error and possible fixes.

Scenario

I came across this issue in a production environment, but for the demonstration purposes I prepared a sample script that you can execute in your test environment to show the error.  I am going to be using SQL Server 2017 for the test.

First, we will create a database, a table, insert some data and then create a view.

CREATE DATABASE PlanError
GO

USE PlanError
GO

CREATE TABLE designation_mst
(
id INT PRIMARY KEY IDENTITY (1,1),
varDesignation NVARCHAR (200),
varDesignationCode NVARCHAR (100),
chrActive CHAR (1)
)
GO

INSERT INTO designation_mst
SELECT 'Director','101','Y'
UNION ALL
SELECT 'Branch Manager','102','Y'
UNION ALL
SELECT 'ManagerR','103','Y'
UNION ALL
SELECT 'ManagerG','104','Y'
UNION ALL
SELECT 'ManagerT','105','Y'
UNION ALL
SELECT 'TeamLeader','106','Y'
UNION ALL
SELECT 'Sr','107','Y'
UNION ALL
SELECT 'Jr','108','Y'
GO

CREATE TABLE Employee_MST
(
id INT IDENTITY (1,1),
varEmpName VARCHAR(200),
varEmail VARCHAR(20),
fk_desiGlcode INT,
fk_empGlcode INT,
chrActive CHAR(1)
)
GO

INSERT INTO Employee_MST
SELECT 'Emp1','[email protected]',1,0,'Y'

INSERT INTO Employee_MST
SELECT 'Emp2','[email protected]',2,1,'Y'

INSERT INTO Employee_MST
SELECT 'Emp3','[email protected]',3,2,'Y'

INSERT INTO Employee_MST
SELECT 'Emp4','[email protected]',4,3,'Y'

INSERT INTO Employee_MST
SELECT 'Emp5','[email protected]',5,4,'Y'

INSERT INTO Employee_MST
SELECT 'Emp6','[email protected]',6,5,'Y'

INSERT INTO Employee_MST
SELECT 'Emp7','[email protected]',7,6,'Y'

INSERT INTO Employee_MST
SELECT 'Emp8','[email protected]',8,7,'Y'
GO

insert into Employee_MST
select 'EMP' +  CAST(MAX(ID) + ROW_NUMBER() OVER(ORDER BY MAX(ID))  AS VARCHAR(200)),
'test' +  CAST(MAX(ID) + ROW_NUMBER() OVER(ORDER BY MAX(ID))  AS VARCHAR(200)) + '@gmail.com',8,7,'Y'
FROM Employee_MST
go 5000

CREATE view get_employee
AS
	SELECT em.* FROM Employee_MST EM
	INNER JOIN designation_mst DM ON DM.id = EM.fk_desiGlcode
	AND DM.varDesignation = 'jr'
	AND DM.chrActive = 'y'
	where em.fk_empGlcode in
	(select em1.id FROM Employee_MST em1
	inner join designation_mst dm1 on dm1.id = em1.fk_desiGlcode
	AND dm1.varDesignation = 'sr'
	AND dm1.chrActive = 'y'
	and em1.fk_empGlcode in
	(select em2.id FROM Employee_MST em2
	inner join designation_mst dm2 on dm2.id = em2.fk_desiGlcode
	AND DM2.varDesignation = 'TeamLeader'
	AND DM2.chrActive = 'y'
	and em2.fk_empGlcode in
	(select em3.id FROM Employee_MST em3
	inner join designation_mst dm3 on dm3.id = em3.fk_desiGlcode
	AND dm3.varDesignation = 'ManagerT'
	AND dm3.chrActive = 'y'
	and em3.fk_empGlcode in 
	(select em4.id FROM Employee_MST em4
	inner join designation_mst dm4 on dm4.id = em4.fk_desiGlcode
	AND dm4.varDesignation = 'ManagerG'
	AND dm4.chrActive = 'y'
	and em4.fk_empGlcode IN
	(select em5.id FROM Employee_MST em5
	inner join designation_mst dm5 on dm5.id = em5.fk_desiGlcode
	AND dm5.varDesignation = 'ManagerR'
	AND dm5.chrActive = 'y'
	and em5.fk_empGlcode IN	   
	(select em6.id FROM Employee_MST em6
	inner join designation_mst dm6 on dm6.id = em6.fk_desiGlcode
	AND dm6.varDesignation = 'Branch Manager'
	AND dm6.chrActive = 'y'
	and em6.fk_empGlcode IN 
	(select em7.id FROM Employee_MST em7
	inner join designation_mst dm7 on dm7.id = em7.fk_desiGlcode
	AND dm7.varDesignation = 'Director'
	AND dm7.chrActive = 'y')))))))
	
	EXCEPT
	
	SELECT em.* FROM Employee_MST EM
	INNER JOIN designation_mst DM ON DM.id = EM.fk_desiGlcode
	AND DM.varDesignation = 'jr'
	AND DM.chrActive = 'y'
	where em.fk_empGlcode in
	(select em1.id FROM Employee_MST em1
	inner join designation_mst dm1 on dm1.id = em1.fk_desiGlcode
	AND dm1.varDesignation = 'sr'
	AND dm1.chrActive = 'y'
	and em1.fk_empGlcode in
	(select em2.id FROM Employee_MST em2
	inner join designation_mst dm2 on dm2.id = em2.fk_desiGlcode
	AND DM2.varDesignation = 'TeamLeader'
	AND DM2.chrActive = 'y'
	and em2.fk_empGlcode in
	(select em3.id FROM Employee_MST em3
	inner join designation_mst dm3 on dm3.id = em3.fk_desiGlcode
	AND dm3.varDesignation = 'ManagerT'
	AND dm3.chrActive = 'y'
	and em3.fk_empGlcode in 
	(select em4.id FROM Employee_MST em4
	inner join designation_mst dm4 on dm4.id = em4.fk_desiGlcode
	AND dm4.varDesignation = 'ManagerG'
	AND dm4.chrActive = 'y'
	and em4.fk_empGlcode IN
	(select em5.id FROM Employee_MST em5
	inner join designation_mst dm5 on dm5.id = em5.fk_desiGlcode
	AND dm5.varDesignation = 'ManagerR'
	AND dm5.chrActive = 'y'
	and em5.fk_empGlcode IN
	(select em6.id FROM Employee_MST em6
	inner join designation_mst dm6 on dm6.id = em6.fk_desiGlcode
	AND dm6.varDesignation = 'Branch Manager'
	AND dm6.chrActive = 'y'
	and em6.fk_empGlcode IN 
	(select em7.id FROM Employee_MST em7
	inner join designation_mst dm7 on dm7.id = em7.fk_desiGlcode
	AND dm7.varDesignation = 'Director'
	AND em7.varEmail = '[email protected]'
	AND dm7.chrActive = 'y'))))))) 
			

After creating the database and database objects, I want to check the compatibility level of the database.  We can see the value below is 140 which is SQL Server 2017.

check sql server database compatibility level

Creating the Error

Now I am going to run the following query.  This is pretty complex query using the view that was created above, so this will generate the error message.

SELECT tt.*
FROM
(
SELECT ge.* FROM get_employee ge
CROSS APPLY get_employee gee WHERE (ISNULL(gee.fk_empGlcode,0) > 0 AND ISNULL(ge.fk_empGlcode,0) > 0 )
) tt
LEFT JOIN get_employee ggg on ggg.id = tt.id
WHERE ISNULL(ggg.fk_empGlcode,0) > 0

We can see the error below.

sql server error the query processor ran out of resources

Here is the full text from the error message:

"Msg 8623, Level 16, State 1, Line 1

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.".

Understanding Query Execution Flow

To help better understand how the query optimizer works, I put together the following image and an overview.

Query execution flow

When a SQL statement is executed, the relational engine performs a few steps.  The first step is the Query Parser parses the submitted SQL statements. This generates a parsed tree and submits to the Algebrizer. The Algebrizer uses the parsed tree and resolves all the names of the different objects, synonyms, alias, etc. It also handles aggregates (for an example Group By, Max, etc.) within the query which is called aggregate binding. In some cases, this step could be omitted for things like DDL and DCL statements. The algebraic output which is binary is called the query processor tree and is then passed to the Query Optimizer. The query optimizer works based as a cost-based optimizer and tries to match and retrieve the query plan from the plan cache, but if not found it is necessary to build a new execution plan. The execution plan is based on a certain number of thresholds, like indexing and statistics. The query engine also decides if the plan will work on a single processor or multi-processor. Once the query plan is built, the query optimizer transfers control to the Storage Engine which is responsible for the query execution process of the query and passes data per the relational engine as row set requests. Finally, the relational engine processes and formats the data and returns the query results. The problem we are facing is in the query execution process.

Reasons this error can be generated

As per the above process overview, this error happens at the Query Execution Process. This error is not related to a particular area of code. Sometimes when the query execution is taking more time in the query engine, the process could be suspended and an error raised, which means the query processor has limitations and it doesn’t produce a query plan.

There are certain number of reasons why this error could happen.

  • It is possible to generate this error in all SQL versions, but in my case, I have found it occurs more frequently in newer versions like SQL Server 2016 and later due to new cardinality estimator changes.
  • This error might be generated due to a heavy, complex query or a large number of tables used as a query reference. In order to execute these complex queries with older or outdated statistics it’s possible to generate this error.
  • It is possible to generate this error during execution due to limitation of server resources like memory, etc.

Resolution Steps

Let's test this in other compatibility levels to see if this error goes away without making any other changes to the query.

First, I will change the compatibility level for the database to 130 (SQL 2016) then execute the query, but we can see the error still occurs.

sql server error the query processor ran out of resources

Now I will go to change compatibility level to 120 (SQL 2014) then execute the query. We can see there is still an issue.

sql server error the query processor ran out of resources

Now I will change the compatibility level to 110 (SQL 2012) then execute the query.  We can see the query not executes without issue.

sql server query results

Conclusion

I have noticed, when we deal with complex queries the optimizer does not behave the same way on every call of the procedure because of query level issues such as: wrong selectivity, SARGability, optimizer level cardinality issues, possibly older statistics, server workload, etc. which can lead to the inability to produce a query plan.

In SQL Server 2014, a new cardinality estimator was introduced and further changes were made in SQL Server 2016 and 2017. You may experience better results in newer versions depending on the queries and data, but sometimes there are issues as we have seen above.

Work Around Solutions

  • First, need to verify server and database level configurations and resource availability (memory, CPU, etc.) as well.
  • Try to simplify the query rather than deal with a very complex query. If possible, re-write the query and removed unnecessary joins, unions, complex subqueries, conditions, etc.
  • Try to update indexes and statistics because the problem might be due to outdated statistics.
  • If you find this error in SQL Server 2016 or later then try to change the database compatibility level to a lower version 120 or 110, etc.
  • There is also a database level scope configuration option available LEGACY_CARDINALITY_ESTIMATION. After enabling the Legacy CE, the query processor works as compatibility 110.
  • There is also a query level option or trace flag available which is OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));  This works the same as the database level change, but this option is available at the query level.
  • If you find this error in SQL 2014, then you can try to change the database compatibility level to a lower version 110 or use Trace Flag 9481. We can use this trace flag at the query level using hint QUERYTRACEON.
  • Also, you need to make sure your server has enough resources for the query execution, like memory, CPU, etc.

There are different query level options and database level options available to help with this issue.  The first option to fix would be to optimize the query rather than applying other fixes.

Next Steps


Last Updated: 2018-04-12


next webcast button


next tip button



About the author
MSSQLTips author Bhavesh Patel Bhavesh Patel is a SQL Server database professional with 10+ years of experience.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools