Troubleshoot and resolve SQL Server Error 8623: The query processor ran out of internal resources and could not produce a query plan

At least once a day, an application for one of the databases I manage encounters this error:

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.

This error is sent to the client that initiated the query, so it won't appear in the SQL Server error log. Consequently, you may not be aware of it until you receive reports from your users. However, you can set up Extended Events, traces, etc., to capture the error. That's exactly what I did because we were uncertain which specific query was causing this error. 

Here is an explanation on this error:

https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/mssqlserver-8623-database-engine-error

In my experience, this is indeed an uncommon situation and therefore can be a daunting challenge. However, the query itself doesn't necessarily have to be highly complex. More often than not, it's an excessively and unnecessarily long or inefficient query. For instance, it could involve an IN clause with thousands of values, an overly convoluted query with numerous derived tables, joins, UNION ALL operations, and so on. As a result, the SQL Server query optimizer eventually gives up attempting to find an optimal execution plan, or any plan for that matter, and throws the error 8623 instead.

In my specific case, the problematic query was dynamically generated by an application’s UI based on user selections. Users could select multiple values, and the application would build a query with a UNION ALL for each selected value before sending it to SQL Server.

When a user selected “too many” values, the number of UNION ALL branches exploded to over 400, and the optimizer struggled to find an execution plan. The query consistently timed out and produced error 8623 after about 20 seconds on our SQL Server 2019 Standard instance (8 cores, 48 GB RAM with 40 GB allocated to SQL Server).

This particular scenario, an application generating dynamic SQL with many repeated UNION ALL branches or a huge IN list, is a very common way to hit error 8623.

---

A word of caution about dynamic SQL

Error 8623 often shows up with queries that are generated dynamically by application code or reporting tools rather than written by hand. It is very easy for dynamic SQL builders to produce statements that are much more complex than they need to be, long chains of UNION ALL, deeply nested predicates, or huge IN lists constructed from user selections.

When you design or review dynamic SQL, pay special attention to:

• How conditions are combined as users select more filters or options.

• Whether repeated patterns (like multiple UNION ALL branches) could be replaced by a set/table and a join.

• Whether large lists of values are better handled via temp tables or table-valued parameters instead of string‑concatenated IN lists.

• I wrote a separate article on patterns and guidelines for building safer, more efficient dynamic SQL, which can help reduce the risk of running into errors like 8623:

        Writing Better Dynamic SQL

---

How you resolve it? From the same Microsoft article:

Simplify the query by breaking the query into multiple queries along the largest dimension. First, remove any query elements that aren't necessary, then try adding a temp table and splitting the query in two. Note that if you move a part of the query to a subquery, function, or a common table expression that isn't sufficient because they get recombined into a single query by the compiler. You can also, try adding hints to force a plan earlier, for example OPTION (FORCE ORDER).

Why did I highlight the use of 'OPTION (FORCE ORDER)'? Because I've found it to be a reliable workaround to make the query work, especially when you need a quick workaround to address the issue immediately. Afterward, you can focus on implementing a better and more sensible permanent solution, assuming you have the option to rewrite the query, if not you can dynamically apply query hints using the Query Store:

https://learn.microsoft.com/en-us/sql/relational-databases/performance/query-store-hints

Please note that the Query Store is available in SQL Server 2019 and later, but Query Store hints (sys.sp_query_store_set_hints) are only available in SQL Server 2022 and in Azure SQL Database / Managed Instance.

In my case the database I'm referring to is a third-party database running on SQL Server version 2019 Standard edition, equipped with 8 cores and 48GB of RAM, with 40GB allocated to the SQL Server. However, it's worth noting that this error can occur in across editions and all versions of SQL Server, including versions 2019, 2022 and 2025 as well as potentially future versions of SQL Server. Error 8623 can appear with different state values: 

• State 1 - The query timed out due to the plan being too complex.
• State 2 - The query ran out of resources (memory).

So in my case as a temporary workaround, I first implemented the query hint OPTION (FORCE ORDER), which reduced the query execution time to just 7 seconds, still returning same 469 rows. Then in search of efficiency, replacing the UNION ALL operations with an IN clause further improved performance, allowing the query to finish in slightly under a second and producing the same 469 rows. Opting for an even better solution, I moved all values into a table variable, which enabled the query to complete in less than a second.

It's important to note that the specific solution for this error in one of your queries may differ based on the specific characteristics of your query. In most cases, reshaping the query to make it easier for the optimizer to plan is the most durable solution.

---

Capturing this error using Extended Events:

Because error 8623 is returned to the client and not written to the SQL Server error log, you may need to capture additional details (statement text, user, application, etc.) using Extended Events. Below is an example Extended Events session targeting error 8623 on SQL Server 2019+.

/*
*******************************************************************************************
Script: Create Extended Events Session to Capture 
Error 8623

Purpose: Monitors and captures diagnostic information when 
         SQL Server encounters Error 8623 - "The query 
         processor ran out of internal resources and
         could not produce a query plan."

Permissions Required:
    - ALTER ANY EVENT SESSION
    - VIEW SERVER STATE (to read captured events later)

Usage:
    1. Run this script to CREATE the session.
    2. Start: ALTER EVENT SESSION [trace_error_8623] ON SERVER STATE = START;
    3. Stop:  ALTER EVENT SESSION [trace_error_8623] ON SERVER STATE = STOP;
    4. Drop:  DROP EVENT SESSION  [trace_error_8623];
******************************************************************************************
**/

CREATE EVENT SESSION [trace_error_8623] ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION (
        package0.last_error,
        sqlserver.client_app_name,
        sqlserver.client_connection_id,
        sqlserver.database_id,
        sqlserver.database_name,
        sqlserver.is_system,
        sqlserver.plan_handle,
        sqlserver.query_hash,
        sqlserver.query_plan_hash,
        sqlserver.session_id,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE ([error_number] = 8623 AND [severity] = 16 AND [sqlserver].[is_system] = 0)
)
ADD TARGET package0.event_file(
    SET filename = N'trace_error_8623.xel',
        max_file_size = 10,   -- 10MB per file
        max_rollover_files = 5
)
WITH (
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 30 SECONDS,
    MAX_EVENT_SIZE = 0 KB,
    MEMORY_PARTITION_MODE = NONE,
    TRACK_CAUSALITY = OFF,
    STARTUP_STATE = OFF
);
GO

-- Start the session when needed:
-- ALTER EVENT SESSION [trace_error_8623] ON SERVER STATE = START;

To read the captured events:

WITH ErrorEvents AS
(
    SELECT TOP (1000)
        @@SERVERNAME AS [SQL Server],
        xevent.value('(event/@timestamp)[1]', 'datetime2') AS utc_time,
        DATEADD(
            MINUTE,
            DATEDIFF(MINUTE, GETUTCDATE(), SYSDATETIMEOFFSET()),
            xevent.value('(event/@timestamp)[1]', 'datetime2')
        ) AS local_time,
        xevent.value('(event/@name)[1]', 'sysname') AS event_name,
        xevent.value('(event/data[@name="error_number"]/value)[1]', 'int') AS error_number,
        xevent.value('(event/data[@name="severity"]/value)[1]', 'int') AS severity,
        xevent.value('(event/data[@name="message"]/value)[1]', 'nvarchar(2048)') AS message_text,
        xevent.value('(event/action[@name="database_id"]/value)[1]', 'int') AS database_id,
        xevent.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(256)') AS database_name,
        xevent.value('(event/action[@name="session_id"]/value)[1]', 'int') AS session_id,
        xevent.value('(event/action[@name="username"]/value)[1]', 'nvarchar(256)') AS username,
        xevent.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(256)') AS client_app_name,
        xevent.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text,
        xevent.value('(event/action[@name="query_hash"]/value)[1]', 'nvarchar(256)') AS query_hash,
        xevent.value('(event/action[@name="plan_handle"]/value)[1]', 'nvarchar(256)') AS plan_handle
    FROM (
        SELECT CONVERT(xml, event_data) AS xevent
        FROM sys.fn_xe_file_target_read_file(
            'trace_error_8623*.xel',
            NULL,
            NULL,
            NULL
        )
    ) AS ev
)
SELECT *
FROM ErrorEvents
ORDER BY local_time DESC;

This gives you the query text, user, application name, and other useful context for each occurrence of error 8623.

---

See also

The exact real-life queries I have worked with are proprietary, so I cannot share them here. However, here are some very similar examples others have shared publicly that you can experiment with and reproduce in your own test environment.

• MSSQLTips – Walks through a reproduced 8623 case with example code and a step‑by‑step analysis. When I tested the sample code provided in the article, it would trigger this error within a couple of seconds. Here, the suggested workaround is to include the query hint OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')). However, adding the query hint OPTION (FORCE ORDER) also resolves the issue.

• Stack Overflow – Shows a simple IN query hitting 8623 and discusses rewriting it using a table of values.

• Microsoft KB 982376 – Documents how a very large IN clause can trigger 8623 and related issues.

• SQLServerCentral / Packt – Another real-world example with discussion on query shape and complexity.