Interpreting BigQuery BI Engine Metrics

Published in

Google Cloud - Community

4 min readDec 10, 2022

What is BI Engine?

BigQuery BI Engine is a fast, in-memory analysis service that accelerates many SQL queries in BigQuery by intelligently caching the data that is used most frequently. BI Engine can accelerate SQL queries from any source, including those written by data visualization tools, and can manage cached tables for on-going optimization.

Learn more about: What is BI Engine? | BigQuery | Google Cloud

BI Engine provides the following benefits

In-memory intelligent caching to accelerate analytical queries
Higher throughput for well optimized queries
Easy to manually scale the capacity as required by the use-case

Reserving BI Engine capacity

It is very straightforward to reserve or edit BI Engine capacity by a click of a button.

The reservation is attached to a project identified when the reservation is created.

For more information on: Reserving BI Engine capacity | BigQuery | Google Cloud

Once we reserve BI Engine capacity, the queries in the project start getting accelerated via BI Engine.

After running a SQL query, it is observed that BI Engine was able to accelerate the query from the query job information.

But how can we understand the impact of BI Engine at a deeper level?

Interpreting BI Engine utilization

We can use the below ways to analyse & interpret BI Engine usage & performance.

1. Monitoring Dashboards

We can use monitoring dashboards for better understanding of the utilization of BI Engine.

For example: we could create a chart in Cloud Monitoring plotting the:

bigquerybiengine.googleapis.com/reservation/used_bytes over the 
bigquerybiengine.googleapis.com/reservation/total_bytes.

For more information please refer -Monitor BI Engine with Cloud Monitoring | BigQuery | Google Cloud

For a complete list of available Google Cloud metrics, see Google Cloud metrics.

Insights from this can help:

Check the total BI Engine cache capacity that is used in one Google Cloud project
Analyse the capacity required as per utilization patterns and scale it accordingly
Understand cache usage per table. This metric displays the top N tables per region report usage. For more information details here
Analyse the need to add any tables in preferred tables

Alerting

We have reserved the BI Engine capacity for the project and location that’s running SQL queries from BQ and other BI applications. While it is expected for the BI Engine utilization to fluctuate, we may want to proactively monitor the peaks that have observed a high utilization for a significant time period due to reasons such as:

Monitoring and alerting when BI Engine utilization goes above 90%.
Reservation utilization of specific tables going extremely under or above expected value.

To ensure that the team is notified when the BI Engine utilization is consistently crossing such high thresholds, we can create the following alerting policy:

We can create alerts on a specific project or location we want to monitor utilization threshold going above or under a specific value.

In the below example, we have added alerts for metric: BigQuery Project — Reservation used bytes >90%

Alert set if BI engine reservation goes beyond 90%

2. We can create alerts on a specific table if we want to monitor utilization threshold going above/ under a specific value or absent in Top N values to indicate & analyse the cause of why it is not getting accelerated as intended.

In the below example, we have added alerts for a specific table for metric: BigQuery Project — Reservation used bytes by table — metric absence

Alert set on a specific table if reservation used by table has no data for a specific duration

2. INFORMATION_SCHEMA

We can also view Acceleration statistics from INFORMATION_SCHEMA. To better understand the reasons for BI Engine acceleration leverage the statistics in INFORMATION_SCHEMA.

For more details, please refer: Monitor BI Engine with Cloud Monitoring | BigQuery | Google Cloud

Following are examples of some reason codes-