Fix it now
The optimiser could not build a plan for the statement. Microsoft documents two states for 8623: state 1 is a timeout caused by plan complexity, state 2 is memory exhausted during optimisation. In both, the documented action is the same – simplify the statement.
SELECT TOP 20 qs.execution_count, qs.total_worker_time,
SUBSTRING(st.text, 1, 400) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY LEN(st.text) DESC;
- Capture the statement that failed. If the application builds SQL at run time, log the generated text and not the template.
- Count the elements. Thousands of literals in an
INlist, or hundreds ofORbranches, is almost always the cause. - Replace the value list with a set: insert into a temporary table, or pass a table-valued parameter, and join to it.
- Where the list arrives as a delimited string, split it in the database with
STRING_SPLITrather than building SQL text around it. - Re-run. If you now see 8628 instead, the problem has moved from complexity to waiting for a compilation slot.
Moving part of the statement into a subquery, a function or a common table expression does not help. Microsoft is explicit that the compiler recombines them into one query.
If the rewritten statement compiles you are finished. If you are looking at 8632 or 8621 rather than 8623, the next section explains which limit each of them is.
Why it happens
Compiling a query is not free. The optimiser expands the statement – substituting the definitions of every view, computed column, function and common table expression it references – builds an internal tree, explores alternatives and holds all of that in per-request structures. Those structures are bounded, and the bound is not a configuration setting. 8623 is what you get when they run out before a plan exists. The failure happens at compile time, so the statement never touches your data, and rerunning it produces exactly the same result.
Microsoft documents two states behind the same message. State 1 is the query timing out because plan complexity is too high; state 2 is the query exhausting available memory during optimisation. That distinction matters mainly because it stops the usual argument: yes, memory can be involved, and no, the documented action is not to add memory. In both states the published user action is to simplify – remove unnecessary elements, split the query along its largest dimension, stage intermediate results in temporary tables, or force plan generation earlier with a hint such as OPTION (FORCE ORDER).
The neighbouring codes are different walls. 8632 is the expression services limit, and it has a published number: SQL Server allows 65,535 identifiers and constants in a single expression, counted after the asterisk, views and computed column definitions have been expanded. 8621 is the optimiser running out of stack space, and Microsoft attributes it to the size of the expanded query in any dimension – the number of tables pulled in by view definitions, or one very large scalar expression – rather than to nesting depth alone. 8628 is a timeout waiting to optimise, which is a concurrency symptom rather than a complexity one.
A generated IN list with thousands of values
You have this one if The captured statement is mostly commas, and the value count matches the size of a collection in the application.
- Load the values into a temporary table and join:
INSERT INTO #Ids (Id) VALUES (...); SELECT ... FROM dbo.T AS t JOIN #Ids AS i ON i.Id = t.Id; - Better, define a table type and pass a table-valued parameter, which avoids the parsing cost and the plan cache churn together.
- If the list arrives as a delimited string, use
STRING_SPLITrather than assembling the SQL around it. - Where the list is itself a query result from elsewhere, consider whether the two queries should be one.
A long chain of OR conditions or UNION ALL blocks
You have this one if The statement is one repeated block with different constants, produced by a loop in application code.
- Turn the repeated block into one parameterised statement executed per batch of criteria, or into a join against a set of criteria rows.
- For a search screen with many optional filters, use
(@p IS NULL OR col = @p)predicates withOPTION (RECOMPILE)rather than concatenating branches. - Break a very long
UNION ALLinto staged inserts into a temporary table, then query that.
One enormous expression rather than a complex plan
You have this one if 8632 rather than 8623, and the statement contains a very long concatenation, a huge CASE, or a select list built from several wide views.
- Count what is really in the expression after expansion. An asterisk over a view over a view expands to every column of every underlying table, and all of it counts toward the 65,535 identifiers and constants.
- Name the columns you need instead of using the asterisk.
- Split the expression across staged columns or a temporary table so no single expression carries the whole load.
Deeply layered views and functions
You have this one if 8621, and the statement itself looks short because the size is hidden behind view names.
- Expand the definition chain and count the tables it actually pulls in.
- Flatten the layers that exist only for convenience, and materialise genuinely reusable ones into a table or an indexed view.
- Replace multi-statement table-valued functions in the middle of a chain, which the optimiser cannot see through, with inline logic.
Compilation is queuing rather than failing outright
You have this one if 8628, with requests waiting for a compilation slot while a small number of very large statements are optimised.
- Find and fix the statements with long compile times first. They are usually the same generated monsters that produce 8623.
- Encourage plan reuse by parameterising, so the same shape is not compiled again on every call.
- Check for a flood of single-use plans in the cache, which is the signature of a workload compiling far more than it needs to.
Full reference
Which shape of statement produces which code
| Statement shape | Likely code | Published limit |
|---|---|---|
Thousands of literal values in an IN list |
8623 or 8632 | Internal optimiser resources; 65,535 identifiers and constants per expression |
Hundreds of OR branches built by concatenation |
8623 | No published number |
A very long UNION ALL chain of generated blocks |
8623 | No published number |
SELECT * over layered views |
8632 or 8621 | 65,535 after expansion of the asterisk, views and computed columns |
| Views on views on views | 8621 | No published number; the constraint is stack space for the expanded query |
| Normal queries failing only while a monster compiles | 8628 | No published number |
What the 65,535 limit is actually counting
This is the most useful published number in the group, and it is easy to misread. It is not a limit on values in an IN list, or on columns in a table. It counts identifiers and constants in a single expression of the query, measured after SQL Server has expanded everything that stands for something else – the asterisk in a select list, a view definition, a computed column definition. That is why a statement that looks short can hit it: a select list of three asterisks over three wide views is not three identifiers, it is every column of every table those views touch.
Rewrites that work, and one that does not
| Rewrite | Does it help? |
|---|---|
| Values into a temporary table, then join | Yes. The statement becomes fixed-shape and small |
| Table-valued parameter | Yes, and it removes the plan cache churn as well |
STRING_SPLIT on a delimited parameter |
Yes, for lists arriving as text |
| Split the query along its largest dimension into two statements | Yes; this is Microsoft’s own documented suggestion |
OPTION (FORCE ORDER) to force plan generation earlier |
Yes, documented as a workaround; treat it as a stopgap |
| Moving part of it into a subquery, a function or a CTE | No. Microsoft states the compiler recombines them into one query |
| Retrying the statement | No. It is refused during compilation and nothing about server state changes that |
| Adding RAM or changing edition | No. These are statement limits, identical on Express and Enterprise |
Finding the generated statement
- Log the fully rendered SQL in the application, not the template. Half the time nobody has ever seen the real text.
- Where an object-relational mapper is building it, look for a query constructed from a large in-memory collection and change the calling code to batch.
sys.dm_exec_query_statsjoined tosys.dm_exec_sql_textfinds the longest text the instance has cached, which is usually a sibling of the one that failed.- Extended events on
sql_statement_recompileandquery_pre_execution_showplanwill catch long compilations, but do not leave a showplan event running on a busy instance.
The plan cache side effect
A statement with thousands of literals is not only hard to compile; it is unique. Every variation gets its own plan, the cache fills with single-use entries, and compilation cost rises across the whole workload. That is why parameterising the generated SQL usually improves things well beyond the statement that was failing, and why 8628 tends to disappear at the same time as 8623.
Every code this article covers
| Code | What it points at | Source |
|---|---|---|
8623 |
The query processor ran out of internal resources and could not produce a plan. Two documented states: a timeout on plan complexity, and memory exhausted during optimisation | Microsoft Learn |
8632 |
An expression services limit has been reached. SQL Server allows 65,535 identifiers and constants in a single expression, counted after expansion | Microsoft Learn |
8621 |
The query processor ran out of stack space during query optimization, driven by the size of the expanded query | Microsoft Learn |
8628 |
A time out occurred while waiting to optimize the query. Rerun the query | Microsoft Learn |
Confirm the fix worked
- The rewritten statement compiles and returns results.
- The generated text is now a fixed shape with parameters rather than embedded literals.
- The plan cache shows fewer single-use plans for that workload than before the change.
- No 8628 appears during the next peak period.
- If you used
OPTION (FORCE ORDER), the plan it produces is checked against the workload rather than left unexamined.
Questions people ask about this
Will more RAM or a bigger edition fix it?
No. The limits are on the complexity of the statement, not on the memory available to the instance. The same statement fails the same way on the largest server you can buy, and Microsoft’s documented action is to simplify the query.
How many values in an IN list are too many?
There is no published number for IN lists specifically. There is one for 8632: 65,535 identifiers and constants in a single expression, after expansion. Treat any generated list longer than a few hundred as a design problem regardless, because it also destroys plan reuse.
Can I keep the generated SQL and just retry?
No. The statement is refused during compilation, so nothing about server state changes the outcome.
Does OPTION (RECOMPILE) help?
Not for 8623. It changes when the plan is built, not how complex the statement is. It is useful for the optional-filter pattern once you have already collapsed the generated branches into one statement.
Why does splitting the query into CTEs make no difference?
Because Microsoft documents that subqueries, functions and common table expressions are recombined by the compiler into a single query. To reduce complexity you have to break the execution into separate statements, usually with a temporary table between them.
