How to Monitor GCP BigQuery Slot Utilization and Job Failures: Step-by-Step Guide 2026

BigQuery’s slot model determines how fast your queries run and how much they cost. A query that sits waiting for slots during 70% of its execution drags down pipelines and burns compute time with no work happening. Job failures without context mean incidents that take hours to diagnose instead of minutes. The CNCF’s 2025 Annual Survey found that 68% of organizations now run analytics workloads on Kubernetes or cloud data warehouses, and 42% cite cost unpredictability as a top operational challenge.

This guide walks through setting up real time monitoring for BigQuery slot utilization and job failures using INFORMATION_SCHEMA views. You will learn how to query slot consumption patterns, detect reservation bottlenecks, surface job errors with full context, and set up automated alerts so issues surface before they cascade.

Prerequisites

Before starting, ensure you have the following:

• A Google Cloud project with BigQuery enabled
• IAM permissions: bigquery.jobs.listAll or bigquery.jobs.listExecutionMetadata on the organization or project you want to monitor
• For reservation level monitoring: bigquery.reservations.list and bigquery.reservationAssignments.list permissions on the administration project that created the reservations
• Access to Cloud Logging or a monitoring platform if you want to send alerts outside BigQuery
• Basic familiarity with SQL and BigQuery’s query editor

Step 1: Understand BigQuery’s Slot Model and INFORMATION_SCHEMA Views

BigQuery uses slots as units of computational capacity. When you submit a query, BigQuery breaks it into stages and assigns slots to execute those stages in parallel. The more slots available and actively working, the faster your query completes.

Slot utilization measures how many slots your queries consume over time. If you have a 500 slot reservation and queries consistently use only 200 slots, you are overprovisioned. If usage regularly hits the ceiling and queries queue, you need more capacity or better workload distribution.

BigQuery exposes two primary INFORMATION_SCHEMA views for monitoring:

INFORMATION_SCHEMA.JOBS contains one row per job with summary data including total_slot_ms (total slot milliseconds consumed), total_bytes_processed, job duration, and error details if the job failed.

INFORMATION_SCHEMA.JOBS_TIMELINE contains time sliced data with one row per job per second of execution. Each row shows period_slot_ms (slot milliseconds used during that second) and period_estimated_runnable_units (work waiting for slots). This view is what you need for understanding concurrent slot usage patterns and detecting contention.

Both views are regional. To query them, use the region-LOCATION prefix before INFORMATION_SCHEMA. For example:

SELECT * 
FROM `region-us-central1`.INFORMATION_SCHEMA.JOBS
LIMIT 10;

All timestamps in INFORMATION_SCHEMA are in UTC. Data retention is 180 days.

Step 2: Query Overall Slot Utilization by Hour or Day

The most basic question is: how many slots am I actually using? This query aggregates slot consumption by hour over the past 7 days.

-- Hourly slot utilization over the last 7 days
SELECT 
  TIMESTAMP_TRUNC(period_start, HOUR) AS hour,
  -- Each row in JOBS_TIMELINE represents one second
  -- Sum slot-milliseconds, then convert to slot hours
  ROUND(SUM(period_slot_ms) / (1000 * 3600), 2) AS total_slot_hours,
  -- Count unique jobs running during this hour
  COUNT(DISTINCT job_id) AS unique_jobs
FROM `region-us-central1`.INFORMATION_SCHEMA.JOBS_TIMELINE
WHERE period_start BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND CURRENT_TIMESTAMP()
  AND job_type = 'QUERY'
  AND (statement_type != 'SCRIPT' OR statement_type IS NULL)
GROUP BY hour
ORDER BY hour DESC;

This query converts period_slot_ms (slot milliseconds per second) into slot hours for easier interpretation. If you see consistent hourly usage well below your committed capacity, you are paying for slots you do not need. If hourly usage regularly spikes to the ceiling, jobs are likely queueing or slowing down due to contention.

For daily rollups, change TIMESTAMP_TRUNC(period_start, HOUR) to TIMESTAMP_TRUNC(period_start, DAY) and adjust the denominator to milliseconds in a day: (1000 * 60 * 60 * 24).

Step 3: Identify the Most Slot Intensive Queries

A small number of queries often consume a disproportionate share of slots. Finding these is the first step toward optimization. This query surfaces the top 20 most slot intensive queries in the past 24 hours.

-- Top 20 queries by slot consumption in the last 24 hours
SELECT 
  job_id,
  user_email,
  creation_time,
  -- Total slot time consumed by this query
  total_slot_ms,
  -- Convert to slot hours for readability
  ROUND(total_slot_ms / (1000 * 3600), 2) AS slot_hours,
  -- Query duration in seconds
  TIMESTAMP_DIFF(end_time, start_time, SECOND) AS duration_seconds,
  -- Data scanned in GB
  ROUND(total_bytes_processed / POW(1024, 3), 2) AS gb_processed,
  -- Reservation used (if applicable)
  reservation_id,
  -- First 200 characters of the query
  SUBSTR(query, 1, 200) AS query_preview
FROM `region-us-central1`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND (statement_type != 'SCRIPT' OR statement_type IS NULL)
ORDER BY total_slot_ms DESC
LIMIT 20;

This surfaces queries that burn the most compute. Often you will find queries that scan entire tables when they should use partition filters, or queries that run repeatedly when results could be cached or materialized into a summary table.

Link each job_id to the BigQuery job details page for full query text and execution plan: https://console.cloud.google.com/bigquery?project=YOUR_PROJECT&j=JOB_ID&page=queryresults.

Step 4: Track Slot Usage by User or Reservation

In shared environments, knowing who consumes the most slots helps with capacity planning and accountability. This query breaks down slot consumption by user over the past 7 days.

-- Slot consumption by user over the last 7 days
SELECT 
  user_email,
  COUNT(*) AS query_count,
  -- Total slot time in hours
  ROUND(SUM(total_slot_ms) / (1000 * 3600), 2) AS total_slot_hours,
  -- Average slot time per query in seconds
  ROUND(AVG(total_slot_ms) / 1000, 2) AS avg_slot_seconds_per_query,
  -- Total data processed in GB
  ROUND(SUM(total_bytes_processed) / POW(1024, 3), 2) AS total_gb_processed,
  -- Average query duration
  ROUND(AVG(TIMESTAMP_DIFF(end_time, start_time, SECOND)), 2) AS avg_duration_seconds
FROM `region-us-central1`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
  AND (statement_type != 'SCRIPT' OR statement_type IS NULL)
GROUP BY user_email
ORDER BY total_slot_hours DESC;

If you have multiple reservations, track utilization per reservation to balance capacity. Replace user_email with reservation_id and add a WHERE reservation_id IS NOT NULL filter to exclude on demand queries.

-- Slot utilization by reservation over the last 24 hours
SELECT 
  reservation_id,
  TIMESTAMP_TRUNC(period_start, HOUR) AS hour,
  -- Average slots used during this hour
  ROUND(SUM(period_slot_ms) / (1000 * 3600), 2) AS avg_slots_used,
  -- Number of jobs using this reservation
  COUNT(DISTINCT job_id) AS job_count
FROM `region-us-central1`.INFORMATION_SCHEMA.JOBS_TIMELINE
WHERE period_start > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND reservation_id IS NOT NULL
  AND (statement_type != 'SCRIPT' OR statement_type IS NULL)
GROUP BY reservation_id, hour
ORDER BY reservation_id, hour;

This shows whether specific reservations are saturated while others sit idle, indicating a need to rebalance assignments or resize reservations.

Step 5: Detect Slot Contention and Queueing

Slot contention happens when queries compete for limited slots, causing them to run slower than they would with dedicated resources. The period_estimated_runnable_units column in JOBS_TIMELINE shows how much work was waiting for slots during each second of execution.

This query identifies queries that spent a large share of their runtime waiting for slots.

-- Find queries experiencing slot contention in the last 24 hours
SELECT 
  job_id,
  ANY_VALUE(user_email) AS user_email,
  MIN(job_creation_time) AS creation_time,
  TIMESTAMP_DIFF(MAX(job_end_time), MIN(job_start_time), SECOND) AS duration_seconds,
  SUM(period_slot_ms) AS total_slot_ms,
  -- Percentage of execution seconds where the job had runnable units waiting
  ROUND(COUNTIF(period_estimated_runnable_units > 0) / COUNT(*) * 100, 1) AS runnable_units_waiting_pct,
  MAX(period_estimated_runnable_units) AS max_estimated_runnable_units,
  ANY_VALUE(reservation_id) AS reservation_id
FROM `region-us-central1`.INFORMATION_SCHEMA.JOBS_TIMELINE
WHERE period_start > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND job_type = 'QUERY'
  AND job_start_time IS NOT NULL
  AND job_end_time IS NOT NULL
  AND (statement_type != 'SCRIPT' OR statement_type IS NULL)
GROUP BY job_id
HAVING duration_seconds > 30
ORDER BY runnable_units_waiting_pct DESC, duration_seconds DESC
LIMIT 50;

Queries with a high runnable_units_waiting_pct are likely experiencing contention. This is a signal to either increase reservation capacity, distribute load more evenly across time, or move low priority workloads to separate reservations with lower slot allocations.

A Reddit user documented slot contention causing a 10 minute query to stretch to 45 minutes during peak hours because all slots were saturated by concurrent dashboard refreshes.

Step 6: Monitor Job Failures and Error Patterns

Job failures in BigQuery can happen for many reasons: query timeouts, exceeded resource limits, syntax errors, or permission issues. The INFORMATION_SCHEMA.JOBS view includes error_result and state columns that capture failure details.

This query surfaces all failed jobs in the past 24 hours with error codes and messages.

-- Failed jobs in the last 24 hours with error details
SELECT 
  job_id,
  user_email,
  creation_time,
  project_id,
  reservation_id,
  -- Error reason code
  error_result.reason AS error_reason,
  -- Full error message
  error_result.message AS error_message,
  -- Query preview
  SUBSTR(query, 1, 200) AS query_preview,
  -- Job state (should be 'DONE' with error_result populated)
  state
FROM `region-us-central1`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR)
  AND state = 'DONE'
  AND error_result IS NOT NULL
  AND job_type = 'QUERY'
ORDER BY creation_time DESC;

Common error_result.reason values include:

• resourcesExceeded: Query exceeded memory or shuffle disk limits
• timeout: Query exceeded maximum execution time
• accessDenied: User lacks permission to access a table or dataset
• invalidQuery: Syntax error or invalid operation

Grouping errors by reason helps identify systemic issues. For example, repeated resourcesExceeded errors point to queries that need optimization or larger slot allocations.

-- Error summary by reason in the last 7 days
SELECT 
  error_result.reason AS error_reason,
  COUNT(*) AS error_count,
  COUNT(DISTINCT user_email) AS affected_users,
  COUNT(DISTINCT project_id) AS affected_projects
FROM `region-us-central1`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND error_result IS NOT NULL
  AND job_type = 'QUERY'
GROUP BY error_reason
ORDER BY error_count DESC;

Link the error_reason to Google’s BigQuery error reference documentation for troubleshooting steps: https://cloud.google.com/bigquery/docs/error-messages.

Step 7: Set Up Automated Monitoring with Scheduled Queries

Running these queries manually is useful for ad hoc analysis, but real monitoring requires automation. BigQuery scheduled queries let you run SQL on a fixed schedule and write results to a destination table. You can then alert on that table using Cloud Monitoring or export metrics to external infrastructure monitoring platforms.

Here is how to create a scheduled query that writes hourly slot utilization to a monitoring table.

First, create a destination dataset and table:

CREATE SCHEMA IF NOT EXISTS `your-project.monitoring`;
CREATE TABLE IF NOT EXISTS `your-project.monitoring.slot_utilization_hourly` (
  hour TIMESTAMP,
  reservation_id STRING,
  avg_slots FLOAT64,
  peak_slots FLOAT64,
  job_count INT64,
  total_slot_hours FLOAT64
);

Then create a scheduled query to populate this table every hour:

-- Scheduled query: Write hourly slot metrics to monitoring table
INSERT INTO `your-project.monitoring.slot_utilization_hourly` 
  (hour, reservation_id, avg_slots, peak_slots, job_count, total_slot_hours)
WITH filtered AS (
  SELECT 
    period_start,
    COALESCE(reservation_id, 'ON_DEMAND') AS reservation_id,
    period_slot_ms,
    job_id
  FROM `region-us-central1`.INFORMATION_SCHEMA.JOBS_TIMELINE
  WHERE period_start >= TIMESTAMP_TRUNC(
      TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR), HOUR)
    AND period_start < TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), HOUR)
    AND job_type = 'QUERY'
    AND (statement_type != 'SCRIPT' OR statement_type IS NULL)
)
SELECT 
  TIMESTAMP_TRUNC(period_start, HOUR) AS hour,
  reservation_id,
  ROUND(AVG(period_slot_ms / 1000), 2) AS avg_slots,
  ROUND(MAX(period_slot_ms / 1000), 2) AS peak_slots,
  COUNT(DISTINCT job_id) AS job_count,
  ROUND(SUM(period_slot_ms) / (1000 * 3600), 2) AS total_slot_hours
FROM filtered
GROUP BY hour, reservation_id;

To create the scheduled query in the BigQuery console:

1. Open the BigQuery SQL workspace
2. Paste the query above
3. Click Schedule → Create new scheduled query
4. Set Repeat frequency to Every 1 hour
5. Set Destination table to the table you created
6. Choose Append to table as the write preference
7. Click Save

This table now accumulates hourly slot metrics. You can query it for trends, build dashboards in Looker Studio or Grafana, or set up Cloud Monitoring alerts when slot usage exceeds thresholds.

Similarly, create a scheduled query for job failures:

-- Scheduled query: Write failed jobs to monitoring table
CREATE TABLE IF NOT EXISTS `your-project.monitoring.failed_jobs` (
  job_id STRING,
  user_email STRING,
  creation_time TIMESTAMP,
  project_id STRING,
  reservation_id STRING,
  error_reason STRING,
  error_message STRING,
  query_preview STRING
);
INSERT INTO `your-project.monitoring.failed_jobs`
  (job_id, user_email, creation_time, project_id, reservation_id, error_reason, error_message, query_preview)
SELECT 
  job_id,
  user_email,
  creation_time,
  project_id,
  reservation_id,
  error_result.reason AS error_reason,
  error_result.message AS error_message,
  SUBSTR(query, 1, 200) AS query_preview
FROM `region-us-central1`.INFORMATION_SCHEMA.JOBS
WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
  AND creation_time < CURRENT_TIMESTAMP()
  AND error_result IS NOT NULL
  AND job_type = 'QUERY';

Schedule this to run every hour. Now you have a centralized table of all job failures with error context for post incident analysis.

Step 8: Monitor BigQuery with CubeAPM

CubeAPM provides unified observability for BigQuery alongside your application traces, logs, and infrastructure metrics. Instead of running manual INFORMATION_SCHEMA queries or building custom dashboards, CubeAPM connects to BigQuery via OpenTelemetry or Prometheus exporters and surfaces slot utilization, query latency, and job failure metrics in real time dashboards.

CubeAPM runs on your infrastructure, meaning all BigQuery telemetry stays within your VPC with no data egress to external SaaS platforms. This matters for teams with data residency requirements or those who want to avoid the $0.10/GB egress fees that SaaS observability platforms charge when pulling telemetry out of GCP.

To monitor BigQuery with CubeAPM:

1. Deploy the BigQuery exporter or configure OpenTelemetry Collector to scrape INFORMATION_SCHEMA metrics
2. Point the exporter to your CubeAPM instance
3. CubeAPM auto indexes all metrics and makes them searchable without configuration
4. Create dashboards for slot usage by reservation, job failure rate by project, or query latency percentiles
5. Set alerts on slot saturation thresholds, error rate spikes, or queries exceeding SLO duration

CubeAPM’s pricing is $0.15/GB for all ingested telemetry with unlimited retention. There are no per host fees, per user seats, or separate charges for metrics vs logs. A growing team ingesting 10TB/month of combined application and BigQuery telemetry pays $1,500/month total.

For teams already monitoring applications with CubeAPM, adding BigQuery metrics means all observability data lives in one place. You can correlate a slow BigQuery query with the API request that triggered it, see which application service is driving query volume spikes, or trace a job failure back to a specific deployment that changed query logic.

Troubleshooting Common Issues

Issue: Queries return no data from INFORMATION_SCHEMA views

Check that you are querying the correct region. INFORMATION_SCHEMA views are regional. If your jobs run in us-central1, query region-us-central1.INFORMATION_SCHEMA.JOBS. Querying a different region returns empty results.

Verify IAM permissions. You need bigquery.jobs.listAll or bigquery.jobs.listExecutionMetadata at the organization or project level. Without these, INFORMATION_SCHEMA views return no rows or only jobs you personally created.

Issue: Slot utilization numbers seem too low

The calculation SUM(total_slot_ms) / (1000 * 60 * 60 * 24) for daily utilization assumes consistent slot usage throughout the day. If your workload is bursty, running only during ETL windows, this average will be lower than peak slot consumption. Use hourly or minute granularity queries to see burst patterns.

Confirm you are filtering correctly. The query should exclude SCRIPT statement types and include only job_type = 'QUERY'. Loading jobs, exports, and scripts consume slots differently and can skew results.

Issue: Job failures show generic error messages with no useful context

Some error messages are intentionally vague for security reasons. Link the job_id to the BigQuery job details page in the Cloud Console for the full error and execution plan. The console often shows additional context not exposed in INFORMATION_SCHEMA.

Check Cloud Logging. BigQuery writes detailed error logs to Cloud Logging under the bigquery.googleapis.com resource. Filter by job_id to see the complete error trace.

Issue: Scheduled query fails with permission errors

Scheduled queries run with the permissions of the user who created them. If that user loses access to the source tables or destination dataset, the scheduled query fails. To avoid this, create scheduled queries using a service account with stable long term permissions.

Grant the service account bigquery.jobs.create, bigquery.tables.getData on source datasets, and bigquery.tables.updateData on the destination table.

Issue: Reservation utilization is low but queries still queue

Check for assignment mismatches. If projects are assigned to the wrong reservation or no reservation at all, they compete for shared on demand slots even though dedicated slots sit idle. Review reservation assignments in the BigQuery Reservations UI.

Verify reservation hierarchy. Child reservations inherit slots from parent reservations. If a child reservation is set to 100 slots but the parent only has 50 available, the child cannot use more than 50.

For detailed setup and cost modeling, teams often use tools like the Datadog pricing calculator or New Relic pricing calculator to compare observability platform costs before committing.

BigQuery monitoring is a subset of broader observability practices. For teams managing Kubernetes workloads alongside BigQuery, guides like what is real user monitoring or what is synthetic monitoring provide context on how frontend performance monitoring complements backend data pipeline observability.

Understanding BigQuery slot behavior is critical for cost control and performance. The queries in this guide give you the raw data. The next step is turning that data into actionable alerts and dashboards so your team knows about issues before users or pipelines notice.

This estimate models typical BigQuery monitoring telemetry ingestion for a mid sized team. Your actual data volume will vary based on job frequency, reservation count, and query complexity.

Disclaimer: The information in this article reflects the latest details available at the time of publication and may change as technologies and products evolve. Features, pricing, and plan limits can change over time. Always verify the latest information directly with the vendor before making purchasing or deployment decisions.

Frequently Asked Questions