Skip to content

Est. 2011ยทMicrosoft Partner ID 7033487ยทDelivery under 3 minยทSupport 7 days a week

Your vault is empty.

Free Fix 8623

Error 8623: The Query Processor Ran Out of Internal Resources

10 min read Updated October 5, 2026 SQL Server

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.

Run this to find the longest statements the instance has compiled recently

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;
  1. Capture the statement that failed. If the application builds SQL at run time, log the generated text and not the template.
  2. Count the elements. Thousands of literals in an IN list, or hundreds of OR branches, is almost always the cause.
  3. Replace the value list with a set: insert into a temporary table, or pass a table-valued parameter, and join to it.
  4. Where the list arrives as a delimited string, split it in the database with STRING_SPLIT rather than building SQL text around it.
  5. 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.

  1. 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;
  2. Better, define a table type and pass a table-valued parameter, which avoids the parsing cost and the plan cache churn together.
  3. If the list arrives as a delimited string, use STRING_SPLIT rather than assembling the SQL around it.
  4. 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.

  1. Turn the repeated block into one parameterised statement executed per batch of criteria, or into a join against a set of criteria rows.
  2. For a search screen with many optional filters, use (@p IS NULL OR col = @p) predicates with OPTION (RECOMPILE) rather than concatenating branches.
  3. Break a very long UNION ALL into 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.

  1. 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.
  2. Name the columns you need instead of using the asterisk.
  3. 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.

  1. Expand the definition chain and count the tables it actually pulls in.
  2. Flatten the layers that exist only for convenience, and materialise genuinely reusable ones into a table or an indexed view.
  3. 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.

  1. Find and fix the statements with long compile times first. They are usually the same generated monsters that produce 8623.
  2. Encourage plan reuse by parameterising, so the same shape is not compiled again on every call.
  3. 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_stats joined to sys.dm_exec_sql_text finds the longest text the instance has cached, which is usually a sibling of the one that failed.
  • Extended events on sql_statement_recompile and query_pre_execution_showplan will 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

  1. The rewritten statement compiles and returns results.
  2. The generated text is now a fixed shape with parameters rather than embedded literals.
  3. The plan cache shows fewer single-use plans for that workload than before the change.
  4. No 8628 appears during the next peak period.
  5. 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.

Related error codes

Was this article helpful?

Your feedback helps us improve our documentation.

Related articles

Free Fix Setup Error 0x851A0019: Could Not Find the Database Engine Startup Handle Free Fix Error 9001: The Log for the Database Is Not Available After a Crash License Error Error 3202 and 3271: Backup Write Failures and Nonrecoverable I/O Errors Free Fix Errors 823, 824 and 825: I/O Errors That Signal Real Storage Damage
โ† Back to Knowledge Base