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
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 ALLbranches orORconditions generated in a loop. - Queries over views nested many levels deep that expand to a very large number of tables.
How to fix it
-
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;
-
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);
-
Simplify deeply nested views, or materialize intermediate results into temp tables.
Related errors
More memory, log and storage errors
9002Transaction log for database is full1105Filegroup is full, cannot allocate1101Could not allocate a new page1204Cannot obtain a LOCK resource
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.
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.