Resources exceeded during query execution: The query could not be executed in the allotted memory

A single worker ran out of memory because of the shape of the query, which has nothing to do with how many bytes it reads. The text after the colon names the stage, and ORDER BY is the usual one.

Resources exceeded during query execution: The query could not be executed in the allotted memory. Peak usage: PERCENTAGE% of limit. Top memory consumer(s): ORDER BY operations.

What BigQuery means

One worker ran out of memory. One worker was handed more data than it could hold, because of how the query is shaped. The project and the reservation are not involved.

This has nothing to do with how much the query costs or how many bytes it scans. A query that reads 50 GB can fail this way while a query that reads 5 TB succeeds. What matters is whether any single stage has to hold a large amount of data in one place.

Read the text after the colon. It names the stage.

Top memory consumer: ORDER BY operations

The common case by a wide margin. A global ORDER BY has to see every row to decide the order, and BigQuery does that sort on one compute unit.

SELECT title
FROM `my-project.mydataset.mytable`
ORDER BY title

With millions of distinct values in title, this fails.

Add a LIMIT. With a limit, BigQuery only has to keep that many rows:

SELECT title
FROM `my-project.mydataset.mytable`
ORDER BY title DESC
LIMIT 1000

Drop the ORDER BY if the result goes to a table. Sorting a destination table buys nothing. BigQuery does not keep the order and a later reader will not see it.

Do not sort in the middle of a query. An ORDER BY inside a subquery or a CTE is work that the outer query throws away. Keep it in the outermost query, or inside a window function’s OVER clause where it belongs.

Replace a large OFFSET with ROW_NUMBER(). ORDER BY ... LIMIT 10 OFFSET 1000000 makes the sort stage process a million rows before it can discard them. Google’s own suggestion is to rank instead and filter the rank:

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY created_at) AS rn
  FROM `my-project.mydataset.mytable`
)
WHERE rn > @start_index AND rn <= @page_size + @start_index

ROW_NUMBER() starts at 1.

The other variants

Each one has its own text and its own fix.

Query exceeded resource limits

Query exceeded resource limits

An on-demand query that used a lot of CPU relative to the bytes it scanned. Usually heavy per-row work: regular expressions, JSON parsing, user-defined functions over every row. Push the expensive expressions to the end of the query so they run on fewer rows.

Out of stack space

Out of stack space due to deeply nested query expression during query resolution.

Too many nested function calls. This bites people who build an expression in a loop, because A || B || C || ... becomes CONCAT(A, CONCAT(B, CONCAT(C, ...))) during parsing. Rewrite with ARRAY_TO_STRING over an array, or split the expression.

Query exceeds shuffle resources

Resources exceeded during query execution: Your project or organization exceeded the maximum disk and memory limit available for shuffle operations

Too much data moved between stages. Usually a join that multiplies rows, or a join key with heavy skew so one worker gets most of the rows.

This is the one variant that also responds to capacity. Google suggests more slots or less data. On-demand pricing caps you at 2,000 slots per project, so going above that means moving to capacity pricing. Filtering and aggregating before the join is usually cheaper than buying slots. See Optimizing joins.

Query is too complex

Resources exceeded during query execution: Not enough resources for query planning - too many subqueries or query is too complex

The planner gave up, before anything ran. The causes Google names are deeply nested or repeatedly used WITH clauses, views stacked on views, and repeated UNION ALL.

A WITH clause is not a materialized result. BigQuery may recompute it every time it is referenced, so a CTE used five times can be five full computations. Writing the intermediate result to a temporary table and selecting from that is the standard fix, and it is also the fix for the slow version of the same query that does not fail outright.

What does not fix it

• Raising maximum_bytes_billed. That is a different limit, with a different message.
• Retrying. The memory variant is deterministic: the same query on the same data fails again. The shuffle variant depends on what else is running, so a retry sometimes gets through, which is luck rather than a fix.
• Buying slots, except for the shuffle variant.

The execution graph tells you which stage failed, which is the fastest way to confirm you are fixing the right part of the query.

Finding out before it fails

BigQuery writes a warning into INFORMATION_SCHEMA.JOBS for queries that are getting close to the complexity limit, before they start failing:

SELECT
  ANY_VALUE(query) AS query,
  MAX(query_info.resource_warning) AS resource_warning
FROM `my-project.region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 DAY)
  AND query_info.resource_warning IS NOT NULL
GROUP BY query_info.query_hashes.normalized_literals
LIMIT 1000

The job’s error_result.reason is resourcesExceeded.

In Querylab.io

Querylab.io warns on an ORDER BY with no LIMIT over a large table, the case above, and offers an Add LIMIT quick fix (code quality).