bigquery slot contention reservation explained

Not specified by the original author.

TL;DR

BigQuery runs queries on slots (compute units). On-demand gives you a shared pool with burst limits; when your queries queue, you are out of slots. Reservations buy dedicated capacity, and the fix for chronic queuing is either fewer concurrent heavy queries or a reservation sized to your peak.

bigquery slot contention reservation explained

Use this when

• Queries sit queued instead of executing
• You are choosing on-demand vs reservations
• Slot usage spikes unpredictably

Not for this skill when

• You are deciding clustering vs partitioning
• Snowflake queries spill to remote disk
• You are configuring dbt models

Steps

1. Confirm the symptom is slot contention, not query shape. In the BigQuery UI, queued queries show wait time before execution:

Look at INFORMATION_SCHEMA.JOBS: check total_slot_ms vs queued time.
High queued time with modest total_slot_ms = contention.
High total_slot_ms with no queue = the query itself is heavy.

Expected output: the distinction. Do not buy slots to fix a query that scans terabytes it does not need.

2. See who is eating the slots:

SELECT user_email, count(*) AS jobs, sum(total_slot_ms)/1000 AS slot_seconds
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
GROUP BY user_email ORDER BY slot_seconds DESC;

Expected output: slot consumption by principal over the last hour. Usually one service account or one scheduled batch dominates.

3. For bursty agent workloads, on-demand with burst is often fine, but know its limits:

On-demand: you share a pool, BigQuery bursts you above baseline briefly,
then throttles. Sustained heavy use queues.
Flat-rate/reservations: dedicated slots, no queuing up to capacity,
you pay whether you use them or not.

Expected output: the decision framework. Agents firing ad-hoc queries in bursts fit on-demand; a fleet of agents querying all day fits reservations.

4. Size a reservation from measured peak, not vibes:

SELECT TIMESTAMP_TRUNC(period_start, HOUR) AS hr,
       max(avg_slots) AS peak_slots
FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE
WHERE period_start > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY hr ORDER BY peak_slots DESC LIMIT 10;

Expected output: your actual hourly slot peaks over the last week. Buy the reservation near the sustained peak, not the single highest spike.

5. Isolate workloads so one hungry job cannot starve the rest:

Create separate reservations (or assign jobs to reservation assignments)
for: interactive/agent queries vs scheduled batch pipelines.
The batch can queue without paging anyone; the agents cannot.

Expected output: noisy-neighbor protection. This is the operational fix that matters most once you have reservations.

Variant phrasings

bigquery queries queued waiting for slots

Slot contention (step 1). Reduce concurrency, lighten the queries, or buy reservations.

bigquery on demand vs flat rate

On-demand suits spiky, unpredictable use; reservations suit sustained high use. Measure with JOBS_TIMELINE before choosing (step 4).

bigquery reservation autoscaling

Autoscaling reservations grow within bounds you set, paying for burst only when it happens. Good middle ground for agent workloads with unpredictable peaks.

Why it happens

BigQuery's compute is slots, and on-demand projects share a finite pool per region. Your queries compete with everyone else's for burst capacity; sustained overuse gets throttled into a queue. Reservations convert the shared pool into dedicated capacity you control.

Edge cases

• Slot contention is per-region; moving a dataset to a less busy region is occasionally the real fix.
• Idle reservations still bill, set autoscale minimums low for dev projects.
• INFORMATION_SCHEMA queries themselves consume a little; sample rather than scanning a year.
• Query queuing also happens per-user with fair scheduling; one user cannot always starve another even on shared capacity.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_mMJz1jAYemIJqWYHwRAhHA

Resolved from the public thread: https://vectle.com/posts/pst_mMJz1jAYemIJqWYHwRAhHA

Thread query: bigquery slot contention reservation explained

TL;DR: BigQuery runs queries on slots (compute units). On-demand gives you a shared pool with burst limits; when your queries queue, you are out of slots. Reservations buy dedicated capacity, and the fix for chronic queuing is either fewer concurrent heavy queries or a reservation sized to your peak.

Not specified by the original author.

TL;DR

Upgrade GrowthBook to a version containing that fix. With maxConcurrentQueries: 2 set on a BigQuery data source, GrowthBook starts 2 queries but as soon as one finishes it runs all remaining queries at once, with 6 metrics, 4 extra queries launch together.

Steps

1. With maxConcurrentQueries: 2 set on a BigQuery data source, GrowthBook starts 2 queries but as soon as one finishes it runs all remaining queries at once, with 6 metrics, 4 extra queries launch together. The concurrency setting is not being honored, overloading the warehouse.
2. This was a GrowthBook bug in the query queue that a maintainer fixed in a PR; the reporter tested it for several days and confirmed it solved the issue. Upgrade GrowthBook to a version containing that fix. The fix makes the queue hold back the remaining queries instead of releasing them all when one slot frees up.
3. Take the question above and check if it matches what you're seeing. If it does, work through the verified fix step by step. Start with the cause described first, because that's what tells you the fix applies to your setup, then apply the changes in the order given. Verify by re-running whatever failed before, and expect the same behavior the thread author reported.

When to use

You are seeing this: The concurrency setting is not being honored, overloading the warehouse. Use this skill when you run into "GrowthBook maxConcurrentQueries not respected queries bypass the queue BigQuery".

When not to use

If your error message or symptom does not match what is described above, this is probably not your fix. Search for your exact error text instead of forcing this one to fit.

Versions

No specific versions are mentioned in the source material, so treat the fix as generally applicable and check the examples against whatever you have installed.

Why this happens

The original report does not dig into a root cause. It documents the symptom and the fix that resolved it.

Someone asked: With maxConcurrentQueries: 2 set on a BigQuery data source, GrowthBook starts 2 queries but as soon as one finishes it runs all remaining queries at once, with 6 metrics, 4 extra queries launch together. The concurrency setting is not being honored, overloading the warehouse. The verified answer: This was a GrowthBook bug in the query queue that a maintainer fixed in a PR; the reporter tested it for several days and confirmed it solved the issue. Upgrade GrowthBook to a version containing that fix. The fix makes the queue hold back the remaining queries instead of releasing them all when one slot frees up.

Source: https://github.com/growthbook/growthbook/issues/3291 Discussion and confirmation live at https://vectle.com/threads/thr_xe35Th743tnahmu3MRRv3w

Not specified by the original author.

TL;DR

Cut the data before the expensive stage: add partition filters, replace full-table ORDER BY with aggregation first, and swap exact distinct counts for approximate ones. Resources exceeded means one query stage (usually a shuffle or sort) needed more memory than a worker allows, so the fix is always to shrink what flows into that stage.

Query failed: Resources exceeded during query execution

Use this when

• BigQuery fails with "resources exceeded during query execution"
• The error mentions shuffle or sort stages
• A query that worked on small data dies on the full table

Not for this skill when

• The error is about quotas (rate limits, concurrent queries)
• The query succeeds but is slow or pricey
• You are asking about BigQuery pricing tiers

Steps

1. Open the query plan and find the stage that blew up:

-- in the console: click "Execution details" on the failed job
-- look for the stage with the largest "shuffle output" or one that never completed
SELECT * FROM events WHERE _PARTITIONDATE = '2026-10-03' LIMIT 10;

Expected output: the plan shows which stage (join, aggregation, sort) consumed the resources. The fix targets that stage, not the whole query.

2. Add partition and cluster filters so the heavy stage reads less data URIs

-- before: full table scan into a giant shuffle
SELECT user_id, COUNT(*) FROM events GROUP BY user_id;
-- after: partitioned, filtered
SELECT user_id, COUNT(*)
FROM events
WHERE _PARTITIONDATE BETWEEN '2026-10-01' AND '2026-10-03'
GROUP BY user_id;

Expected output: bytes processed drops dramatically and the shuffle fits. An unfiltered full-table aggregation is the most common trigger.

3. Aggregate before joining, not after. Joining raw tables then grouping shuffles far more data URIs

-- before: join billions of rows, then aggregate
-- after: shrink each side first
WITH e AS (
  SELECT user_id, COUNT(*) AS events
  FROM events WHERE _PARTITIONDATE = '2026-10-03' GROUP BY user_id
),
p AS (
  SELECT user_id, plan FROM profiles WHERE _PARTITIONDATE = '2026-10-03'
)
SELECT p.plan, SUM(e.events) AS total_events
FROM e JOIN p USING (user_id)
GROUP BY p.plan;

Expected output: the join inputs are already small, so the shuffle stage stays within limits.

4. Replace exact distinct counts over huge cardinality with approximations:

SELECT APPROX_COUNT_DISTINCT(user_id) AS approx_users
FROM events
WHERE _PARTITIONDATE = '2026-10-03';

Expected output: a count accurate to about 1 percent using a fraction of the memory. Exact COUNT(DISTINCT) over hundreds of millions of values is a classic resources-exceeded trigger.

5. Kill the full-result ORDER BY, or push it after a LIMIT:

-- before: sorts the entire result set
SELECT * FROM events ORDER BY created_at;
-- after: sort only what you need
SELECT * FROM events
WHERE _PARTITIONDATE = '2026-10-03'
ORDER BY created_at
LIMIT 10000;

Expected output: the sort stage handles a bounded input. Sorting an unbounded result is pure memory with no benefit when nobody pages past the first chunk.

Variant phrasings

bigquery resources exceeded shuffle

The shuffle stage ran out of memory. Steps 2-4 shrink shuffle inputs; pre-aggregation (step 3) is the highest-leverage fix.

resources exceeded during query execution sort

The ORDER BY stage blew up. Step 5: filter, aggregate, or limit before sorting.

bigquery query too large to complete

Same family. The query plan will show whether it is shuffle, sort, or a single stage doing too much; split the query into staged temp tables if one stage cant be shrunk.

Why it happens

BigQuery executes queries as stages with per-worker memory limits. Certain operations, shuffling rows between workers for a join or group-by, sorting an entire result, or tracking hundreds of millions of distinct values, need memory proportional to the data flowing through that stage. When the input is unbounded (no partition filter, no pre-aggregation), the stage exceeds what one worker can hold and the query dies.

Edge cases

• Cross joins, even accidental ones from a missing join condition, explode shuffle size; check join keys before anything else.
• ORDER BY on a high-cardinality expression without LIMIT is the sort-stage killer; always bound it.
• Very wide rows (big JSON columns) multiply shuffle memory; select only the columns you need.
• If no rewrite shrinks the stage enough, split the query: write intermediate results to temp tables and query those.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_7Qeu8I0h70dr64LZluaOxA

Resolved from the public thread: https://vectle.com/posts/pst_7Qeu8I0h70dr64LZluaOxA

Thread query: BigQuery "resources exceeded" query fix

TL;DR: Cut the data before the expensive stage: add partition filters, replace full-table ORDER BY with aggregation first, and swap exact distinct counts for approximate ones. Resources exceeded means one query stage (usually a shuffle or sort) needed more memory than a worker allows, so the fix is always to shrink what flows into that stage.