SQL Server Msg 8623, Level 16 过于复杂的sql报错

最新推荐文章于 2021-01-12 10:36:53 发布

Hehuyi_In

最新推荐文章于 2021-01-12 10:36:53 发布

阅读量3.1k

点赞数

分类专栏：报错&告警 SQLServer

本文链接：https://blog.csdn.net/Hehuyi_In/article/details/101081880

版权

SQLServer 同时被 2 个专栏收录

142 篇文章 24 订阅

订阅专栏

报错&告警

51 篇文章 1 订阅

订阅专栏

有时数据库中会看到这样的报错：

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.

处理复杂查询时，查询处理器可能需要花费大量时间来构建查询计划，在查询优化器执行期间，由于缺少进程和资源，无法保证优化计划，查询优化器会停止进程并报错：“查询处理器耗尽了内部资源，无法生成查询计划......”

显然这样的sql需要找出来优化，但是sqlserver并不会将语句直接记录到日志里，我们有两种方法去捕获问题语句——扩展事件和SQL Profiler

一、通过扩展事件（推荐）

只需根据实际情况修改filename、metadatafile参数对应的值

CREATE EVENT SESSION
overly_complex_queries
ON SERVER
ADD EVENT sqlserver.error_reported
(
ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_pid,sqlserver.database_id,sqlserver.sql_text, sqlserver.tsql_stack,sqlserver.username)
WHERE ([severity] = 16
AND [error_number] = 8623)
)
ADD TARGET package0.asynchronous_file_target
(set filename = 'D:\DB_BACKUP\overly_complex_queries.xel',
metadatafile = 'D:\DB_BACKUP\overly_complex_queries.xem',
max_file_size = 10,
max_rollover_files = 5)
WITH (MAX_DISPATCH_LATENCY = 5SECONDS)
GO

-- Start the session
ALTER EVENT SESSION overly_complex_queries
ON SERVER STATE = START
GO

进行测试，报错模拟代码如下：
https://www.mssqltips.com/sqlservertip/5279/sql-server-error-query-processor-ran-out-of-internal-resources-and-could-not-produce-a-query-plan/

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'))))))) 
			
			
#执行测试语句
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;

用SSMS打开生成的日志，发现已经捕获到了该语句

这个方法简单且消耗小，但是这个扩展事件只能运行在SQL Server 2012及后续版本，如果是SQL Server 2008的相关版本部署，就会报下面错误：

Msg 25706, Level 16, State 8, Line 1
The event attribute or predicate source, "error_number", could not be found.

Msg 15151, Level 16, State 1, Line 18
Cannot alter the event session 'overly_complex_queries', because it does not exist or you do not have permission.

如果在2012之前，我们需要使用另一种方法

二、通过SQL Profiler

如上所示，如果数据库服务器的版本为SQL Server 2008 R2，我们只能采取Server Side Trace来捕获这个错误的SQL语句。设置脚本如下（相关参数需根据实际情况等设定）：

-- 定义参数  
declare @rc int  
declare @TraceID int  
declare @maxfilesize bigint  
set @maxfilesize = 1024   

-- 初始化跟踪  
exec @rc = sp_trace_create @TraceID output, 0, N'D:\SQLScript\trace_error_8623', @maxfilesize, NULL   
--此处的D:\SQLScript\trace_error_8623是文件名（可自行修改），SQL会自动在后面加上.trc的扩展名  
if (@rc != 0) goto error  

-- 设置跟踪事件  
declare @on bit  
set @on = 1  

--trace_event_id=13  SQL:BatchStarting   trace_event_id=22 ErrorLog
exec sp_trace_setevent @TraceID, 13, 1,  @on    
exec sp_trace_setevent @TraceID, 13, 3,  @on  
exec sp_trace_setevent @TraceID, 13, 6,  @on  
exec sp_trace_setevent @TraceID, 13, 7,  @on  
exec sp_trace_setevent @TraceID, 13, 8,  @on  
exec sp_trace_setevent @TraceID, 13, 11, @on  
exec sp_trace_setevent @TraceID, 13, 12, @on 
exec sp_trace_setevent @TraceID, 13, 14, @on 
exec sp_trace_setevent @TraceID, 13, 15, @on 
exec sp_trace_setevent @TraceID, 13, 35, @on  
exec sp_trace_setevent @TraceID, 13, 63, @on  

exec sp_trace_setevent @TraceID, 22, 1,  @on    
exec sp_trace_setevent @TraceID, 22, 3,  @on  
exec sp_trace_setevent @TraceID, 22, 6,  @on  
exec sp_trace_setevent @TraceID, 22, 7,  @on  
exec sp_trace_setevent @TraceID, 22, 8,  @on  
exec sp_trace_setevent @TraceID, 22, 12, @on 
exec sp_trace_setevent @TraceID, 22, 11, @on  
exec sp_trace_setevent @TraceID, 22, 14, @on 
exec sp_trace_setevent @TraceID, 22, 14, @on 
exec sp_trace_setevent @TraceID, 22, 35, @on  
exec sp_trace_setevent @TraceID, 22, 63, @on  

-- 启动跟踪  
exec sp_trace_setstatus @TraceID, 1  

-- 记录下跟踪ID，以备后面使用  
select TraceID = @TraceID  
goto finish  

error:   
select ErrorCode=@rc  

finish:   
GO

上面SQL会生成一个服务器端跟踪事件，并返回对应的id，如下：

注意：上面捕获SQL:BatchStarting事件（trace_event_id=13），是因为捕获ErrorLog（trace_event_id=22)等事件时，都无法捕获到对应的SQL(对应的trace column没有捕获SQL语句，暂时还没有找到一个好的解决方法）。这里也有个弊端，就是会捕获大量无关的SQL语句。

测试过后，可以使用SQL Profile工具打开D:\SQLScript\trace_error_8623.trc找到错误信息，对应的SQL语句（在这个时间点附近的SQL语句，一般为是错误信息后面的第一个SQL语句，需要做判断），如下:

也可以使用脚本查询，如下所示，也是需要自己判断定位SQL语句，一般都是“8623 The query processor ran out of internal resources and could not produce a query plan”出现后紧接着的SQL。

SELECT StartTime,EndTime,
    TextData, ApplicationName,SPID,Duration,LoginName
FROM ::fn_trace_gettable(N'D:\SQLScript\trace_error_8623.trc',DEFAULT)
WHERE spid=64
ORDER BY StartTime

三、报错原理

1. 查询执行流程

执行SQL语句时，关系引擎执行几个步骤：

Query Parser解析提交的SQL语句，生成解析树并提交给Algebrizer

Algebrizer使用解析树和处理不同的对象、同义词、别名等，也处理聚合操作（例如GROUP BY，Max等），这就是所谓的查询中聚合绑定（aggregate binding）。在某些情况下，DDL和DCL语句之类的内容可能会省略此步骤。

Algebrizer输出二进制的查询处理器树，并传送给查询优化器。

查询优化器尝试从计划缓存中匹配和检索查询计划，但如果没有找到，则需要构建新的执行计划。查询引擎还决定该计划是在单处理器还是多处理器上运行。构建查询计划后，查询优化器将控制权转移到存储引擎，后者负责查询的执行过程，并将每个关系引擎的数据作为行集请求传递。最后，关系引擎处理并格式化数据并返回查询结果。

我们面临的问题就发生在查询执行过程。

2. 可能导致此错误的原因

此错误可能出现在所有SQL版本中，但由于新的基数估算器更改，它在2016等较新版本中更频繁地出现。
查询引用了大量的表或执行过于复杂的查询（例如 in中超过1万个值），可能会生成此错误。
使用较旧或过时的统计信息，可能会生成此错误。
由于服务器资源（如内存等）的限制，可能会在执行期间生成此错误。

3. 解决方案

首先需要验证服务器和数据库级别配置以及资源可用性（内存，CPU等）。
尝试简化查询。如果可能，重写查询并删除不必要的join、复杂子查询、条件等。
尝试更新索引和统计信息，因为问题可能是由于过时的统计信息。
如果在SQL Server 2016或更高版本中发现此错误，可以尝试将数据库兼容级别更改为较低版本120或110等。
有一个数据库级别范围配置选项 LEGACY_CARDINALITY_ESTIMATION。在启用Legacy CE之后，查询处理器使用兼容性110。也可以使用hint，在查询在添加OPTION（USE HINT（'FORCE_LEGACY_CARDINALITY_ESTIMATION'））
如果在SQL 2014中发现此错误，则可以尝试将数据库兼容级别更改为较低版本110或使用跟踪标记9481。

修复的最优选项是优化查询而不是应用其他方法。

参考：

https://www.mssqltips.com/sqlservertip/5279/sql-server-error-query-processor-ran-out-of-internal-resources-and-could-not-produce-a-query-plan/

https://www.mssqltips.com/sqlservertip/1035/sql-server-performance-statistics-using-a-server-side-trace/

https://www.cnblogs.com/kerrycode/p/9860653.html

Hehuyi_In

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
SQL Server Msg 8623, Level 16 过于复杂的sql报错

有时数据库中会看到这样的报错：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 ...
复制链接

扫一扫