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','test1@gmail.com',1,0,'Y'
INSERT INTO Employee_MST
SELECT 'Emp2','test2@gmail.com',2,1,'Y'
INSERT INTO Employee_MST
SELECT 'Emp3','test3@gmail.com',3,2,'Y'
INSERT INTO Employee_MST
SELECT 'Emp4','test4@gmail.com',4,3,'Y'
INSERT INTO Employee_MST
SELECT 'Emp5','test5@gmail.com',5,4,'Y'
INSERT INTO Employee_MST
SELECT 'Emp6','test6@gmail.com',6,5,'Y'
INSERT INTO Employee_MST
SELECT 'Emp7','test7@gmail.com',7,6,'Y'
INSERT INTO Employee_MST
SELECT 'Emp8','test8@gmail.com',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 = 'dp@gmail.com'
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.
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.
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.
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.
Now I will go to change compatibility level to 120 (SQL 2014) then execute the query. We can see there is still an issue.
Now I will change the compatibility level to 110 (SQL 2012) then execute the query. We can see the query now executes without issue.
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
- First try this on a test server before rolling out to production.
- Check out these SQL Server Query Optimization Tips.
- Check out these SQL Server Performance Tips.
