Monitoring BigQuery reservations and slot utilization with INFORMATION_SCHEMAMonitoring BigQuery reservations and slot utilization with INFORMATION_SCHEMAStrategic Cloud Engineer, Data AnalyticsTechnical Account Manager

BigQuery Reservations help manage your BigQuery workloads. With flat-rate pricing, you can purchase BigQuery slot commitments in 100-slot increments in either flex, monthly, or yearly plans instead of paying for queries on demand. You can then create/manage buckets of slots called reservations and assign projects, folders, or organizations to use the slots in these reservations. By default, queries running in a reservation automatically use idle slots from other reservations. In this way, organizations have greater control over workload management in a way that ensures high-priority jobs always have access to the resources they need without contention. Currently, two ways to monitor these reservations and slots are via the BigQuery Reservations UI or Cloud Monitoring.

But how does an organization know how many slots to delegate to a reservation? Or if a reservation is being over or underutilized? Or what the overall slot utilization is across all reservations? In this blog post, we will discuss how we used BigQuery’s INFORMATION_SCHEMA system tables to create the System Tables Reports Dashboard and answer these questions.

Using INFORMATION_SCHEMA tables

The INFORMATION_SCHEMA metadata tables contain relevant, granular information about jobs, reservations, capacity commitments, and assignments. Using the data from these tables, users can create custom dashboards to report on the metrics they are interested in in ways that inform their decision making.

While there are several tables that make up INFORMATION_SCHEMA, there are a few that are specifically relevant to monitoring slot utilization across jobs and reservations. The JOBS_BY_ORGANIZATION table is the primary table to extract job-level data across all projects in the organization. This information can be supplemented with data from the CAPACITY_COMMITMENT_CHANGES_BY_PROJECT, RESERVATION_CHANGES_BY_PROJECT, and ASSIGNMENT_CHANGES_BY_PROJECT tables to include details about specific capacity commitments, reservations, and assignments. It’s worth noting that the data retention period for INFORMATION_SCHEMA is 180 days and all timestamps are in UTC. For information about the permissions required to query these tables, follow the links above.

Monitoring with the System Tables Reports Dashboard

The System Tables Reports Dashboard is a Data Studio dashboard that queries data from INFORMATION_SCHEMA by using Data Studio’s BigQuery connector. Organizations can use this dashboard and/or its underlying queries as-is or as a starting point for more complex solutions in Data Studio or any other dashboarding tools.

Daily Utilization Report

The Daily Utilization Report gives an overview of an organization’s daily slot utilization measured in slot days. The primary chart in the report is for overall slot utilization per day alongside the active capacity commitments for the organization. This chart is ideal for gaining a high-level understanding of how an organization’s usage compares to the total number of slots it has committed to (or purchased).

Leave a Comment