Introducing Active Assist recommendations for BigQuery capacity planning

BigQuery already offers highly flexible pricing models, such as the on-demand and flat-rate pricing for running queries, to meet the diverse needs of our users. 

Today, we’re excited to make it even easier for you to optimize BigQuery usage with new BigQuery slot recommendations powered by Active Assist, a part of Google Cloud’s AIOps solution that uses data, intelligence, and machine learning to reduce cloud complexity and administrative toil. 

This feature is available in preview to all BigQuery customers who run queries using on-demand pricing but are exploring flat-rate pricing. It helps answer questions like “How many slots do I need?” based on your BigQuery usage history, spend, and other signals. With actionable and automatic insights and recommendations, you can easily understand the cost benefits and performance tradeoffs of switching to the longer-term reservations for a given project or organization. 

You can access these recommendations in:

• Slot Estimator in the Capacity Management section within BigQuery Administration,

• Recommendation Hub,

• Recommender API and BigQuery export of recommendations to make it easy for you to integrate with your company’s existing workflow management tools.

How many BigQuery slots do I need?

As a reminder, BigQuery uses slots (virtual unit of compute and memory) to run your queries. With the on-demand pricing, you don’t need to think about pre-provisioning slots. However, depending on the characteristics of a given workload, monthly flat-rate pricing could be a more cost effective option compared to on-demand pricing. 

If you are exploring flat-rate pricing, choosing the optimal number of slots to purchase becomes an important question. You don’t want to buy too many and end up with idle/unused capacity, and you don’t want to buy too few and end up not meeting your query performance requirements. The answer to “How many slots should I buy” question would depend on your requirements for performance, throughput, and utility. Some key things to consider:

• Type of the workload and its tolerance to a potential query performance impact

• Shape of the workload (e.g. predictable/unpredictable pattern, spikes) 

• The historical slot usage of your projects

• Your desired monthly budget

Until now, you would have to gather empirical data with slot purchases at different levels and analyze the current slot usage of your projects with a given workload using Cloud Logging or INFORMATION_SCHEMA to estimate how many slots to purchase. Today, we are making this a whole lot easier with the BigQuery slot recommendations.

Discovering and acting on BigQuery slot recommendations

BigQuery Slot Recommender analyzes your BigQuery usage activity across all projects under your organization during the last 30 days. The recommender analyzes your BigQuery on-demand slot usage and presents cost/performance tradeoffs in percentiles. For example, if a project used 1500 on-demand slots at the 95th percentile, it means that it used less than 1500 slots over 95% of the time, and if you switch to the corresponding monthly commitment of 1500 slots then you might see a reduced query performance 5% of the time, given no substantial changes to the characteristics of your workload.

For projects and organizations that can benefit from a longer-term monthly commitment, it can generate recommendations to consider switching from the on-demand to monthly slots. Recommendations include a range of options along the spectrum of optimizing for cost or performance and associated cost savings estimates. Here’s what an example recommendation looks like in Cloud Console UI:

This particular example recommendation provides you with the following alternative options:

• Cost optimized (90th percentile): If you switch to a monthly commitment of 5000 slots, 10% queries may see reduced performance with estimated monthly savings of $60,000.

• Balanced (95th percentile): If you switch to a monthly commitment of 6200 slots, 5% queries may see reduced performance with estimated monthly savings of $36,000.

• Performance optimized (99th percentile): If you switch to a monthly commitment of 8500 slots, 1% queries may see reduced performance with estimated monthly savings of $10,000.

• Cover all usage (100th percentile): This option will not save you any money or might cost more. Please note that in such cases BigQuery Slot Recommender shows “No cost savings” during the preview, and we are working on providing you with the “negative cost” estimates as a part of our next iteration on this product.

In addition to these recommendations, you can also examine your usage data presented as a time series. For example, you might have a spike at a particular time every day that accounts for all your slot usage above the 95th percentile that could be potentially augmented by purchasing flex slots or scheduling queries at different times, to mitigate any potential performance impact.

Getting started with the BigQuery Slot Recommender

To get started with the slot recommendations, check Slot Estimator, BigQuery’s interactive capacity management tool, under the BigQuery Capacity Management in Google Cloud Console (check out this blog to learn more about Slot Estimator). To view the recommendations, you will need the appropriate IAM role such as BigQuery Resource Admin or individual set of permissions for BigQuery Slot Recommender, BigQuery Slot Estimator, and to view resources in a given organization.

You can also automatically export the recommendations to BigQuery and then investigate slot recommendations with DataStudio or Looker. Or, you can use Connected Sheets to use Google Workspace Sheets to interact with the data without having to write SQL queries.

As with any other Recommender, you can choose to opt out of data processing for your organization or your projects at any time by disabling the appropriate data groups in the Transparency & Control tab under Privacy & Security settings.

We hope you use BigQuery Slot Recommender to optimize your usage of BigQuery, and can’t wait to hear your feedback and thoughts about this feature! Please feel free to reach us at active-assist-feedback@google.com. We also invite you to sign up for our Active Assist Trusted Tester Group if you would like to get early access to new features as they are developed.