8623錯(cuò)誤：The query processor ran out of internal resources and could not pro

8623錯(cuò)誤：The query processor ran out of internal resources and could not produce a query plan

問題描述：

配置了SQL Server安全性16的告警，發(fā)送郵件通知，如下：

收到如下告警信息：

查看錯(cuò)誤日志：

Error: 8623, Severity: 16, State: 1.    
The query processor ran out of internal resources and could not produce a query plan.

原因：

這是一個(gè)突發(fā)事件，預(yù)料中只會(huì)發(fā)生在極其復(fù)雜的查詢，或者參照了非常大量的表或者分區(qū)的查詢。比如，使用IN從句（多于10000個(gè)條目）SELECT記錄。

解決方法：

如果是SQL Server 2008 R2及之前版本，使用Server Side Trace；如果是SQL Server 2012及之后版本，使用Extended Event。首先，跟蹤到具體導(dǎo)致8623錯(cuò)誤的查詢。然后對(duì)查詢進(jìn)行優(yōu)化，可以嘗試將部分查詢結(jié)果放到臨時(shí)表中，然后再去根據(jù)條件關(guān)聯(lián)。

對(duì)于IN從句，我們可以來看看BOL上的附注部分：

“

Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table, and use a SELECT subquery within an IN clause.

Error 8623:

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.

Error 8632:

Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

”

其他方法：

可以嘗試運(yùn)行帶有使用了提示option(force order)、option(hash join)、option(merge join)、option(querytraceon 4102)的計(jì)劃向?qū)У牟樵?。通過啟用跟蹤標(biāo)志4102，將行為轉(zhuǎn)為SQL Server 2000的半連接處理。啟用跟蹤標(biāo)志4118、4122（或者涵蓋的4199）也可以避免你看到的問題。查看文檔確定你的情況下的具體原因：

Microsoft Knowledge Base article for TF 4122

Microsoft Knowledge Base article for TF 4102, 4118

Microsoft Knowledge Base article for TF 4199

打相關(guān)Hotfix補(bǔ)丁包，或者直接升級(jí)到對(duì)應(yīng)版本最新的SP包。相關(guān)KB 982376文章：

FIX: A non-yielding scheduler error or an error 8623 occurs when you run a query that contains a large IN clause in SQL Server 2005, SQL Server 2008, or SQL Server 2008 R2

使用擴(kuò)展事件識(shí)別8623錯(cuò)誤：

在SQL Server 2012及后續(xù)版本運(yùn)行以下TSQL腳本：

CREATE EVENT SESSION
overly_complex_queries
ON SERVER
ADD EVENT sqlserver.error_reported
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.database_id, sqlserver.username)
WHERE ([severity] = 16
AND [error_number] = 8623)
)
ADD TARGET package0.asynchronous_file_target
(set filename = 'E:\SQL-DATA\XE\overly_complex_queries.xel' ,
metadatafile = 'E:\SQL-DATA\XE\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

該創(chuàng)建語句若在SQL Server 2008 R2中運(yùn)行，會(huì)報(bào)如下錯(cuò)誤：

Msg 25706, Level 16, State 8, Line 1

The event attribute or predicate source, "error_number", could not be found.

參考：

https://blogs.technet.microsoft.com/mdegre/2012/03/13/8623-the-query-processor-ran-out-of-internal-resources-and-could-not-produce-a-query-plan/

http://dba.stackexchange.com/questions/28945/query-processor-ran-out-of-internal-resources-and-could-not-produce-a-query-plan

https://mssqlwiki.com/2012/10/07/optimizer-timeout-or-optimizer-memory-abort/

http://blog.rdx.com/blog/dba_tips/2014/05/using-server-trace-to-identify-8623-errors

http://jasonbrimhall.info/2014/01/02/day-9-queries-going-boom/