// SQL Server error reference

SQL Server error 8623: Query processor ran out of resources

The optimizer gave up building a plan, usually for a query with a huge IN list, thousands of UNIONs or very deeply nested expressions.

Error number
8623
Severity
16
Category
Memory, log and storage
Error message
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.

What it means

Error 8623 means the query was too complex for the optimizer to compile, not that data was too large. It is typically caused by generated SQL: an IN (...) list with tens of thousands of values, a very long chain of OR or UNION ALL, or many tables and partitions in one statement.

Common causes

  • An ORM or application building WHERE id IN (...) with a very large list of literals or parameters.
  • Thousands of UNION ALL branches or OR conditions generated in a loop.
  • Queries over views nested many levels deep that expand to a very large number of tables.

How to fix it

  1. Load the values into a temp table or table-valued parameter and join to it.

    CREATE TABLE #ids (id int PRIMARY KEY);
    INSERT INTO #ids (id) VALUES (1), (2), (3);  -- or bulk insert / TVP
    SELECT o.* FROM dbo.Orders AS o JOIN #ids AS i ON i.id = o.OrderId;
  2. Pass a delimited list and split it on the server.

    SELECT o.* FROM dbo.Orders AS o
    JOIN STRING_SPLIT(@idList, ',') AS s ON o.OrderId = CAST(s.value AS int);
  3. Simplify deeply nested views, or materialize intermediate results into temp tables.

Related errors

More memory, log and storage errors

The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.

// fewer errors, faster fixes

Work on SQL Server in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.