SQL Server Error 8623

Error number

8623

Severity

16 (user-correctable error)

State

Documented by Microsoft as 1 (plan too complex) or 2 (out of memory); not reproduced here (see the state table below)

Product

Microsoft SQL Server Database Engine

Versions

All supported SQL Server versions (2016 to 2025), Azure SQL Database, Azure SQL Managed Instance

Category

Query Processing & Performance

Last reviewed

24 October 2026

What it means

Error 8623 means the Query Optimizer could not finish building a plan for one statement, because it was too complex to optimise in time or because compiling it needed more memory than was available.

Before a statement runs, the optimiser explores possible plans. For a statement with a huge number of tables, partitions, expressions or literal values, that search can exceed the optimiser's internal limits. Microsoft documents two states: state 1, the optimisation timed out because the plan is too complex, and state 2, it ran out of memory. Either way the statement never starts, so nothing was changed.

The error is about one statement, not the server: other queries keep working. The usual culprits are generated SQL (an ORM or report tool that writes an IN list with many thousands of values or hundreds of OR conditions), views stacked on views that expand into a very large join, and queries over tables with thousands of partitions. Microsoft notes that moving part of the query into a subquery, function or common table expression does not help, because the compiler combines them into one query again.

Common causes

• An IN list with many thousands of literal values, which Microsoft names as a cause of 8623 and 8632.
• Generated SQL with hundreds of joins, OR branches or UNION parts, often from views nested inside views.
• A query that touches a very large number of tables or partitions.
• Compile memory pressure on the server, so a large compilation cannot get the memory it needs (state 2).

Diagnose it

Read-only These queries only read metadata and dynamic management views. They do not change data, permissions or configuration, so they are safe to run first.

SELECT pool_id, name, max_count, active_count, waiter_count, threshold
FROM sys.dm_exec_query_optimizer_memory_gateways;

SELECT counter, occurrence, value
FROM sys.dm_exec_query_optimizer_info
WHERE counter IN (N'optimizations', N'timeout', N'memory limit exceeded');

SELECT type, name, pages_kb
FROM sys.dm_os_memory_clerks
WHERE type IN (N'MEMORYCLERK_SQLOPTIMIZER', N'MEMORYCLERK_SQLQUERYCOMPILE')
ORDER BY pages_kb DESC;

SELECT TOP (10) OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
       OBJECT_NAME(p.object_id) AS table_name, COUNT(*) AS partitions
FROM sys.partitions AS p
WHERE p.index_id IN (0, 1)
GROUP BY p.object_id
ORDER BY partitions DESC;

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

CREATE TABLE #wanted (customer_id INT PRIMARY KEY);
INSERT INTO #wanted (customer_id) VALUES (1), (2), (3);   -- or bulk load, or a TVP

SELECT o.order_id, o.total
FROM dbo.Orders AS o
JOIN #wanted AS w ON w.customer_id = o.customer_id;

SELECT o.customer_id, SUM(o.total) AS revenue
INTO #customer_revenue
FROM dbo.Orders AS o
WHERE o.order_date >= '2026-01-01'
GROUP BY o.customer_id;

SELECT c.name, r.revenue
FROM #customer_revenue AS r
JOIN dbo.Customers AS c ON c.customer_id = r.customer_id;

SELECT ...
FROM dbo.BigView AS v
JOIN dbo.Customers AS c ON c.customer_id = v.customer_id
OPTION (FORCE ORDER);

Examples

A generated query with an enormous IN list

Shown as documented by Microsoft (IN list remarks and MSSQLSERVER_8623); not reproduced here. On SQL Server 2025 a query with 30,000 integer literals in an IN list compiled and ran without error, so whether a given list fails depends on its size, data types and the server. The state and raised level were not observed, so no message header is shown.

-- Generated by an application: many thousands of literals
SELECT order_id, total
FROM dbo.Orders
WHERE customer_id IN (1, 2, 3, 4, 5, /* ... tens of thousands more ... */ 99999);

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.

How to prevent it

Do not let applications build statements whose size grows with the data: pass value lists as table-valued parameters or stage them in a table, and limit how many filters a report tool can combine. Avoid deep stacks of views over views; each layer is expanded into the final statement.

For heavily partitioned tables, make sure queries filter on the partitioning column so most partitions are eliminated before optimisation becomes expensive.

Common questions

Learn more on CodeWithSQL

• Advanced query optimisation
• Performance tuning

Official documentation

• MSSQLSERVER_8623 (Microsoft Learn)
• IN (Transact-SQL): remarks on very long lists
• sys.dm_exec_query_optimizer_memory_gateways
• Query hints (Transact-SQL)

Last technically reviewed 24 October 2026; examples and diagnostics run on SQL Server 2025 (17.0.1145.4). Message text checked against the engine's sys.messages catalogue.