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

Every so often, an application may run into this SQL Server 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.

That is SQL Server error 8623. It is not a very common error but when it does happen, it can be confusing because the query may not look obviously unreasonable at first glance.

In my case, an application connected to one of the databases I manage was encountering this error at least once a day. This error is sent to the client/application that initiated the query, so it won't necessarily 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

What does SQL Server error 8623 mean?

Error 8623 happens when the SQL Server Query Optimizer cannot produce an execution plan. Usually, that means one of two things:

• State 1: The optimizer timed out because the query was too complex to fully explore.

• State 2: The optimizer ran out of internal resources, such as memory, while trying to build a plan.

This is an optimizer or compilation problem. It is different from an application query timeout, even though both problems can look similar from the application side. 

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 failed with error 8623 after approximately 20 seconds of optimization/compilation on our SQL Server 2019 Standard instance (8 cores, 48 GB RAM with 40 GB allocated to SQL Server). The problem was not that the server lacked memory or CPU. The real issue was the shape of the query the application was sending to SQL Server.

Please note that there is no universal threshold where 400 branches will always fail. Another server, database, query, or set of statistics may behave differently. Still, allowing a query to grow without a practical limit is a pattern worth watching for. This is ultimately a scalability concern: can the approach continue to work reliably as the number of branches grows into the hundreds or thousands? 

This scenario where 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.

If users may select thousands of values, consider sending those values to SQL Server as data instead of building them into the query text. For example, the application could load the selections into a temporary or staging table and join to that table. This keeps the query shape much more stable as the number of selections grows.

Another option is a table-valued parameter, which lets an application send multiple rows to a stored procedure or query as one parameterized call. Table-valued parameters work naturally with set-based operations and can be a good fit when the application already calls stored procedures. 

• 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 it is a quick and effective short-term workaround and it reduced the optimizer’s search space enough to produce a plan. Treat this as a targeted workaround rather than a permanent fix. 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

Query Store has been available in SQL Server since SQL Server 2016. However, Query Store hints, including sys.sp_query_store_set_hints, require SQL Server 2022 or later, or a supported Azure SQL platform. On SQL Server 2019, Query Store can still help identify and analyze the problematic query, but it cannot apply Query Store hints.

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. 

So first as a quick temporary workaround, I first implemented the suggested 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,  which reduced the complexity enough for the query to complete in slightly under one second, while still returning the same 469 rows. 

That was a significant improvement, but it is worth adding one caution: a very large IN list can also become difficult for the optimizer. So while an IN predicate may be much better than hundreds of repeated branches, it is not always the best long-term pattern for thousands of values where a temporary table, staging table, or table-valued parameter, is often easier for both the application and the optimizer to manage.

Finally, I placed the selected values into a table variable and joined to it. That also completed in less than one second.

This was a good result for this specific query, but it does not mean table variables are always the best choice either. They are often a good fit for smaller, predictable workloads. For larger or more variable value sets, it is worth testing a temporary table as well. The right option depends on the number of values, indexing needs, query shape, and the quality of the resulting plan. In most cases, reshaping the query to make it easier for the optimizer to plan is the most durable solution.

What about Query Store hints?

If the query comes from a third-party application and changing the source code is not immediately possible, Query Store may still be useful.

Query Store can help identify query text, plans, and runtime history. It has been available in SQL Server since SQL Server 2016. 

However, Query Store is not enabled by default for new SQL Server 2019 databases, so it is worth checking whether it is enabled before assuming it will have the information you need. 

Query Store hints are a separate feature. They can apply a query hint without changing application code, but they require SQL Server 2022 or later, Azure SQL Database, Azure SQL Managed Instance, or supported Microsoft Fabric SQL offerings. 

For a SQL Server 2019 database, Query Store can still help identify and analyze the troublesome query, but it cannot apply Query Store hints.

---

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;

Because error 8623 can occur before SQL Server successfully creates a plan, do not be surprised if plan_handle, query_hash, or query_plan_hash are blank for some events. The statement text, application name, host name, database, user, and session ID should still give you a strong starting point.

---

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.