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

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

Error: 8623, Severity: 16, State: 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."

We were getting the above error very frequently in one of my production environments. The query was coming from application servers, so we were not able to pin point the query. The SQL also does not capture the failed queries by default.

Microsoft says that there may be couple of reason if your query fails with this error. As we were not having the original query, so it was difficult for us to proceed.

One of the obvious reasons is that a Complex query or a large number of tables used as a query reference-

Complex queries specially with many entries in the "IN" clause (> 10,000).

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/

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-ver15#remarks

I searched on google and found that we can capture such queries by using extended event.

You can find the script on below link of Brent Ozar website:

https://www.brentozar.com/archive/2013/08/what-queries-are-failing-in-my-sql-server/

I used below script to create extended event and finally I was able to find the problematic query.

Script to create extended Event: You may need to change the directory file in your case

CREATE EVENT SESSION [FailedQueries] ON SERVER

ADD EVENT sqlserver.error_reported

    (ACTION(sqlserver.client_app_name, sqlserver.client_hostname, 

        sqlserver.database_name, sqlserver.sql_text, sqlserver.username)

    WHERE ([severity]> 15))

 ADD TARGET package0.event_file (SET

    filename = N'B:\Test\XEvents\FailedQueries.xel'

    ,metadatafile = N'B:\Test\XEvents\FailedQueries.xem'

    ,max_file_size = (5)

    ,max_rollover_files = (10))

 WITH (STARTUP_STATE = ON)

GO

Use below query to see the logs:

SELECT

    [XML Data],

    [XML Data].value('(/event[@name=''error_reported'']/@timestamp)[1]','DATETIME')             AS [Timestamp],

    [XML Data].value('(/event/action[@name=''database_name'']/value)[1]','varchar(max)')        AS [Database],

    [XML Data].value('(/event/data[@name=''message'']/value)[1]','varchar(max)')                AS [Message],

    [XML Data].value('(/event/action[@name=''sql_text'']/value)[1]','nvarchar(max)')             AS [Statement]

FROM     (SELECT         OBJECT_NAME              AS [Event],

        CONVERT(XML, event_data) AS [XML Data]

    FROM         sys.fn_xe_file_target_read_file

    ('B:\Test\XEvents\FailedQueries*.xel',NULL,NULL,NULL)) as FailedQueries

                    where [XML Data].value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)')  is not null

                    and  [XML Data].value('(/event/data[@name=''message'']/value)[1]','varchar(max)')  like '%The query processor ran out of internal resources and could not produce a query plan %' ;

GO

The query which we found in our case was having a lot of values (>10000) inside IN Clause also the query was running with legacy optimizer on SQL server 2016.

In order to test the query, I tried to run with hint OPTION( QUERYTRACEON 2312) but was still failing with same error. This hint allows SQL server to execute the query with help of new query optimizer (>SQL2012).

And finally I tried to run the same query with help of hint OPTION(FORCE ORDER) and the query completed without any issue.

There are some other workaround to as per Microsoft which are as below:

You could try to run the query using the hint option (force order), option (hash join), option (merge join), option (querytraceon 4102)  with a plan guide. By enabling the traceflag 4102, we will revert the behavior to SQL Server 2000 for handling semi-joins.

Please share if there was any other issue in your case.