Troubleshoot with information schema

As a BigQuery administrator or data analyst, managing enterprise workloads requires a reliable, scalable way to diagnose performance bottlenecks, query failures, capacity limits, and storage growth. BigQuery information schema views serve as an observability foundation, providing near real-time and historical metadata accessible through standard GoogleSQL queries.

This document outlines the core principles of troubleshooting BigQuery using information schema, provides a structured overview of the administrative troubleshooting toolbox, and directs you to specific views in the BigQuery library.

Information schema troubleshooting by task

The following table summarizes useful information schema views categorized by task and diagnostic use case:

Task Use cases Information schema views
Query performance and errors
  • Identify top slot-consuming and expensive queries.
  • Aggregate job error reasons and failure patterns.
  • Analyze per-stage execution times and spilled bytes.
Workload capacity and contention
  • Detect slot contention, throttling, and queue times.
  • Monitor in-memory shuffle memory saturation.
  • Audit reservation baseline and autoscaling slot usage.
  • Verify project and folder reservation assignments.
Storage costs and data architecture
  • Identify tables with runaway physical or logical storage.
  • Detect time-travel and fail-safe storage bloat.
  • Diagnose partition skew and tables nearing partition limits.
  • Discover expired or deleted tables in time-travel windows.
Access control and governance
  • Audit explicit Identity and Access Management (IAM) role grants on tables and datasets.
  • Troubleshoot access denied errors for users and service accounts.
  • Track cross-project dataset sharing and analytical usage.
Data ingestion pipelines
  • Monitor Storage Write API ingestion throughput and errors.
  • Diagnose streaming insert latency and rate limits.
  • Identify failing streams by stream type and error code.
Machine learning and vector search
  • Track model training duration and resource consumption.
  • Audit vector index build status and coverage percentage.
  • Troubleshoot stored procedure and Python UDF builds.
Workload optimization insights
  • Review automated partitioning and clustering recommendations.
  • Identify materialized view candidate tables.

Principles of troubleshooting with information schema

When you diagnose workload or environment issues in BigQuery, apply the following core principles:

  • Scope by region, dataset, and project. BigQuery workload management and compute resources execute within regional boundaries. Consider the following:

    • Always specify the correct regional qualifier (for example, region-REGION.INFORMATION_SCHEMA.JOBS_BY_PROJECT) or dataset qualifier.

    • Choose the appropriate hierarchy level (BY_PROJECT, BY_USER, BY_FOLDER, or BY_ORGANIZATION) based on whether you are investigating a single user issue, a project-specific workload, or a tenant-wide issue.

  • Correlate compute demand with capacity. Slow query performance is often the result of slot contention rather than inefficient SQL alone. Compare job resource requests (period_estimated_runnable_units) against allocated reservation slots (period_slot_ms) over identical time windows to distinguish between query tuning opportunities and issues caused by insufficient capacity.

  • Account for telemetry granularity and retention boundaries. Different information schema views operate on distinct refresh intervals and data retention windows. Job metadata in the JOBS view is available for 180 days, whereas high-resolution timeline metrics in the JOBS_TIMELINE and RESERVATIONS_TIMELINE views are retained for shorter periods (typically 14 to 30 days). For long-term audit and trend analysis, you should export telemetry to partitioned tables.

  • Avoid metric distortion in multi-statement queries. Multi-statement scripts (procedural SQL containing DECLARE, IF, or WHILE) generate a parent job with statement_type = 'SCRIPT' and individual child jobs for each statement. When aggregating metrics such as total_slot_ms or total_bytes_billed, filter out statement_type = 'SCRIPT' to prevent double-counting.

  • Filter on partition columns. To minimize query execution time and avoid unnecessary scan costs on on-demand analysis, always include restrictive time filters on partition columns such as creation_time, job_start_time, or period_start.

What's next