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.

"syspolicy_purge_history" SQL Agent Job is failing with error "Cannot find drive A drive with the name 'SQLSERVER' does not exist"

The SQL Agent Job "syspolicy_purge_history" was failing in our environment since we have upgraded our SQL server instance from SQL 2008R2 to SQL2012. The job was failing at step-3 named as "Erase Phantom System Health Records" We have tried each and every article available over internet, but nothing was helpful in our case. After a lot of troubleshooting my friend Desh found the solution for it. 

There are still lot of DBA's searching the fix of same issue. So, I want to share the all steps which fixed the issue. You can try it in your case and let us know if this works.

Issue Details:

SQL Server version: SQL Server 2012

Job Name: syspolicy_purge_history

Error:

Executed as user: "XXXXX". The job script encountered the following errors. 
These errors did not stop the script:  A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS  -DisableNameChecking'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The specified module 'SQLPS' was not loaded because no valid module file was found in any module directory.  '  A job step received an error at line 2 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\ServerName$a).EraseSystemHealthPhantomRecords()'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot find drive. A drive with the name 'SQLSERVER' does not exist.  '  A job step received an error at line 2 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\

Steps to Fix the issue:

The first step is to check if SQLPS folder exists to below location:

C:\Program Files\Microsoft SQL Server\110\Tools\PowerShell\Modules

Now Open Computer Properties and then click on advance system settings> environment variables> Copy the environment path to a notepad file

%ProgramFiles%\WindowsPowerShell\Modules;

%SystemRoot%\system32\WindowsPowerShell\v1.0\Modules

Now copy the SQLPS folder from location “C:\Program Files\Microsoft SQL Server\110\Tools\PowerShell\Modules“ to below locations which we found under environment variables:

 %ProgramFiles%\WindowsPowerShell\Modules

                                           %SystemRoot%\system32\WindowsPowerShell\v1.0\Modules

You can Open the above two locations via start>run> and then paste location and then Enter.

Once this folder is copied to these locations. Try to run the below command from Powershell.

Open Powershell  as admin and then execute the below command.

import-module SQLPS  -DisableNameChecking

Once import module is done. Open the Group Policy Object Editor as below:

1.  Start > run > "mmc"
2.  File > Add/Remove Snap-In...
3.  Under Available snap-ins, select "Group Policy Object Editor" and click Add, then Finish.
4.  Click OK to snap-in the GPO Editor

Now navigate to the specific policy:

1.  Console Root > Local Computer Policy > Computer Configuration > Administrative Templates > Windows Components > Windows Powershell
2.  Open-up the "Turn on Script Execution" policy setting
3.  Enable Script Execution and in Options select “ Allow All Scripts”. Apply the setting and click on OK.
4.  Exit from this window

Below is the screen shot for above steps for your reference:

Exit from this window and during save pop-up dialog click on No.

Now Try to run the Job it should be running without any issue.

Hope this works in your case.