GCP BigQuery FinOps slot reservations

BigQuery Editions - queries stuck in queue after switching to slots

Fix BigQuery queries stuck in queue: verify capacity commitment, assignment hierarchy, concurrency and slot autoscaling.

Jerzy Kopaczewski · 2026-07-29

After switching from on-demand to BigQuery Editions (slot reservations), queries that previously completed in seconds now hang for minutes or hours in a PENDING/QUEUED state. BI dashboards stop responding, data pipelines fall behind schedule, and users report timeouts. The problem appears immediately after enabling slot-based pricing - it looks like a performance regression, but the root cause is configuration.

This runbook covers diagnosing BigQuery queries stuck in queue after migrating to slot reservations. For a detailed guide on BigQuery cost optimisation, see BigQuery cost optimisation - financial data pipelines. For capacity planning and reservation configuration, book a consulting session.

Symptoms

Queries remain in a PENDING state instead of transitioning to RUNNING. Users see timeout errors or queries never start:

-- Check queries in PENDING state in the last hour
SELECT
  job_id,
  user_email,
  state,
  creation_time,
  start_time,
  TIMESTAMP_DIFF(COALESCE(start_time, CURRENT_TIMESTAMP()), creation_time, SECOND) AS queue_seconds,
  reservation_id,
  total_slot_ms
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  AND state IN ('PENDING', 'RUNNING')
ORDER BY creation_time ASC;

-- Queries that waited longer than 60 seconds to start
SELECT
  job_id,
  user_email,
  creation_time,
  start_time,
  TIMESTAMP_DIFF(start_time, creation_time, SECOND) AS wait_seconds,
  reservation_id,
  query
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND TIMESTAMP_DIFF(start_time, creation_time, SECOND) > 60
ORDER BY wait_seconds DESC
LIMIT 20;

# Check the status of a specific job via bq CLI
bq show --format=prettyjson -j <project_id>:<job_id>

# List active jobs in the project
bq ls -j --all --max_results=20 --format=prettyjson <project_id>

# Typical error messages:
# "Resources exceeded during query execution: The query could not be executed in the allotted slot capacity"
# "Query exceeded resource limits. Slot capacity: 0 slots available for this reservation."
# Job state: PENDING (not transitioning to RUNNING for minutes/hours)

Queries remain PENDING with reservation_id pointing to a reservation with insufficient capacity. In extreme cases, jobs are rejected with a Resources exceeded error.

Cause

After switching from on-demand (Google’s auto-scaled slots) to slot-based pricing (BigQuery Editions), the project loses access to Google’s shared slot pool and is limited exclusively to purchased capacity. Queries get blocked when:

Commitment too small for the workload: You purchased e.g. 100 slots, but peak-hour workloads require 300-500. In on-demand mode, Google dynamically allocated thousands of slots - after switching to editions, the project has a hard limit.
Assignment at the wrong hierarchy level: The reservation is assigned at the organisation or folder level, but the project uses a different path in the GCP hierarchy. The project does not inherit the assignment and effectively has zero slots.
No autoscaling: The commitment has only baseline capacity set without autoscaling. During peak hours, there is no way to temporarily increase capacity - queries wait in queue.
Poor reservation split between interactive and batch: All slots are assigned to a single reservation serving both interactive queries (dashboards, ad-hoc) and batch workloads (ETL, scheduled queries). Long-running batch jobs block short interactive queries.
Concurrent query limit: BigQuery in slot-based mode has a limit on concurrently executing queries per reservation (default 100 concurrent queries per reservation). With a large number of small queries from BI tools, the concurrency limit alone causes queuing, even if slots are available.

Fix

A) Check current slot usage vs capacity:

-- Slot usage in the last hour (per minute)
SELECT
  TIMESTAMP_TRUNC(period_start, MINUTE) AS minute,
  reservation_id,
  SUM(period_slot_ms) / 60000 AS avg_slots_used
FROM `region-eu`.INFORMATION_SCHEMA.JOBS_TIMELINE
WHERE period_start > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
GROUP BY minute, reservation_id
ORDER BY minute DESC;

-- Compare usage vs reservation capacity
SELECT
  r.reservation_name,
  r.slot_capacity,
  r.edition,
  COUNT(j.job_id) AS active_jobs,
  SUM(j.total_slot_ms) / 1000 AS total_slot_seconds
FROM `region-eu`.INFORMATION_SCHEMA.RESERVATIONS r
LEFT JOIN `region-eu`.INFORMATION_SCHEMA.JOBS j
  ON j.reservation_id = r.reservation_name
  AND j.creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
GROUP BY r.reservation_name, r.slot_capacity, r.edition;

# Check capacity commitments via gcloud
gcloud beta bq capacity-commitments list \
  --location=eu \
  --project=<project_id> \
  --format="table(name,slotCount,plan,state,edition)"

# Check reservations
gcloud beta bq reservations list \
  --location=eu \
  --project=<admin_project_id> \
  --format="table(name,slotCapacity,edition,autoscale.maxSlots)"

# Check assignments
gcloud beta bq reservations assignments list \
  --location=eu \
  --project=<admin_project_id> \
  --reservation=<reservation_name> \
  --format="table(name,assignee,jobType,state)"

B) Increase commitment or enable autoscaling:

# Option 1: Enable autoscaling on existing reservation (recommended)
gcloud beta bq reservations update <reservation_name> \
  --location=eu \
  --project=<admin_project_id> \
  --autoscale-max-slots=500

# Option 2: Increase baseline capacity (change commitment)
# First check current commitment
gcloud beta bq capacity-commitments list \
  --location=eu \
  --project=<admin_project_id>

# Create additional commitment (Editions - FLEX plan, can be cancelled after 1 min)
gcloud beta bq capacity-commitments create \
  --location=eu \
  --project=<admin_project_id> \
  --slots=200 \
  --plan=FLEX \
  --edition=ENTERPRISE

# Assign additional slots to the reservation
gcloud beta bq reservations update <reservation_name> \
  --location=eu \
  --project=<admin_project_id> \
  --slot-capacity=300

C) Fix assignment hierarchy:

# Check what the reservation is assigned to
gcloud beta bq reservations assignments list \
  --location=eu \
  --project=<admin_project_id> \
  --reservation=<reservation_name>

# If assignment is at org level but project does not inherit:
# Delete old assignment
gcloud beta bq reservations assignments delete \
  <assignment_id> \
  --location=eu \
  --project=<admin_project_id> \
  --reservation=<reservation_name>

# Create assignment directly on the project
gcloud beta bq reservations assignments create \
  --location=eu \
  --project=<admin_project_id> \
  --reservation=<reservation_name> \
  --assignee=projects/<target_project_id> \
  --job-type=QUERY

# Or on the folder containing the project
gcloud beta bq reservations assignments create \
  --location=eu \
  --project=<admin_project_id> \
  --reservation=<reservation_name> \
  --assignee=folders/<folder_id> \
  --job-type=QUERY

D) Separate reservations for interactive and batch:

# Create a separate reservation for batch/ETL queries
gcloud beta bq reservations create etl-batch \
  --location=eu \
  --project=<admin_project_id> \
  --slot-capacity=100 \
  --edition=ENTERPRISE

# Assign ETL projects to the batch reservation
gcloud beta bq reservations assignments create \
  --location=eu \
  --project=<admin_project_id> \
  --reservation=etl-batch \
  --assignee=projects/<etl_project_id> \
  --job-type=QUERY

# Main reservation (interactive) - keep for dashboards and ad-hoc
# Optional: use job-type=PIPELINE for scheduled queries
gcloud beta bq reservations assignments create \
  --location=eu \
  --project=<admin_project_id> \
  --reservation=etl-batch \
  --assignee=projects/<etl_project_id> \
  --job-type=PIPELINE

# Set concurrency target on the interactive reservation (optional)
gcloud beta bq reservations update interactive-prod \
  --location=eu \
  --project=<admin_project_id> \
  --concurrency=0
# concurrency=0 means automatic management by BigQuery

E) Temporary fallback to on-demand (emergency):

# WARNING: Remove assignment so the project reverts to on-demand billing
# This restores full performance at the cost of higher bills

# Find assignment ID
gcloud beta bq reservations assignments list \
  --location=eu \
  --project=<admin_project_id> \
  --reservation=<reservation_name> \
  --format="table(name,assignee)"

# Delete assignment (project immediately reverts to on-demand)
gcloud beta bq reservations assignments delete \
  <assignment_id> \
  --location=eu \
  --project=<admin_project_id> \
  --reservation=<reservation_name>

# Verify - project should have no reservation
bq show --reservation --location=eu --project_id=<target_project_id>
# Expected: "No reservation found" = on-demand mode

Verification

-- 1. No queries in PENDING state for longer than 10 seconds
SELECT
  COUNT(*) AS pending_jobs
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 MINUTE)
  AND state = 'PENDING'
  AND TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), creation_time, SECOND) > 10;
-- Expected: 0

-- 2. Average queue wait time below 5 seconds
SELECT
  AVG(TIMESTAMP_DIFF(start_time, creation_time, SECOND)) AS avg_queue_seconds,
  MAX(TIMESTAMP_DIFF(start_time, creation_time, SECOND)) AS max_queue_seconds,
  COUNT(*) AS total_jobs
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 MINUTE)
  AND state = 'DONE';
-- Expected: avg_queue_seconds < 5

-- 3. Slot usage does not exceed reservation capacity
SELECT
  TIMESTAMP_TRUNC(period_start, MINUTE) AS minute,
  SUM(period_slot_ms) / 60000 AS slots_used
FROM `region-eu`.INFORMATION_SCHEMA.JOBS_TIMELINE
WHERE period_start > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 MINUTE)
GROUP BY minute
ORDER BY slots_used DESC
LIMIT 5;
-- Expected: slots_used <= reservation slot_capacity (or autoscale max)

# 4. Check slot utilisation in Cloud Monitoring
gcloud monitoring metrics list \
  --filter='metric.type="bigquery.googleapis.com/slots/total_available" OR metric.type="bigquery.googleapis.com/slots/allocated_for_reservation"'

# 5. Confirm reservation and assignment are correct
gcloud beta bq reservations describe <reservation_name> \
  --location=eu \
  --project=<admin_project_id> \
  --format="json(slotCapacity,autoscale,edition)"

gcloud beta bq reservations assignments list \
  --location=eu \
  --project=<admin_project_id> \
  --reservation=<reservation_name> \
  --format="table(assignee,jobType,state)"
# Expected: state = "ACTIVE" for all assignments

If there are no PENDING queries, average wait time is below 5s, and slot utilisation is stable below the limit - the issue is resolved. Monitor for 24-48 hours covering peak hours (ETL + BI dashboards) to ensure capacity is sufficient.

Queries stuck in queue after enabling slot reservations have an immediate business impact. BI dashboards (Looker, Data Studio, Tableau) stop responding - users see blank reports or timeouts. ETL pipelines fall behind, causing stale data in the warehouse. Scheduled queries miss their time windows, generating cascading delays in downstream dependencies. In extreme cases, client-facing SLAs for report delivery are breached. The problem is not immediately obvious - there are no errors in the logs, only growing wait times.

BigQuery queries stuck in queue after enabling slots?

Book a free 30-minute call. We'll review your capacity commitments, reservations and autoscaling configuration to restore query performance.