# 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. Source: https://docs.querylab.io/bigquery-errors/resources-exceeded-allotted-memory/ Updated: 2026-10-25 ```text 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. ```sql 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: ```sql 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: ```sql 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 ```text 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 ```text 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 ```text 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](https://docs.querylab.io/bigquery/on-demand-vs-capacity-pricing/). Filtering and aggregating before the join is usually cheaper than buying slots. See [Optimizing joins](https://docs.querylab.io/bigquery/optimizing-joins/). ### Query is too complex ```text 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](https://docs.querylab.io/bigquery/temporary-tables/) 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](https://docs.querylab.io/bigquery/query-execution/) 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: ```sql 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](https://docs.querylab.io/writing-sql/code-quality/#performance)). ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [Troubleshoot query issues, Resources exceeded issues](https://cloud.google.com/bigquery/docs/troubleshoot-queries#resources_exceeded_issues) - [Optimize query computation](https://cloud.google.com/bigquery/docs/best-practices-performance-compute) - [Insufficient shuffle quota](https://cloud.google.com/bigquery/docs/query-insights#insufficient_shuffle_quota)