View slot recommendations and insights

The BigQuery slot recommender creates recommendations for customers using on-demand billing. These recommendations help you to understand your BigQuery capacity needs, as well as the cost and performance tradeoffs of purchasing different amounts of slot capacity. This page describes how this recommender works and how to use it.

For more information about the Recommender service, see the Recommender overview.

How it works

The slot recommender analyzes your slot usage over the past 30 days and buckets the usage data into percentiles. For example, if a project's slot usage is 2500 slots at the 99th percentile (p99), it means that project used fewer than 2500 slots over 99% of the measurement period. It also compares this slot value against on-demand charges over the same period to determine whether you can reduce costs by switching from on-demand pricing to flat-rate pricing.

The slot recommender API returns this information in the form of insights. Insights are findings that you can use to proactively focus on important patterns in resource usage, as well as the cost and performance tradeoffs of different purchase options.

In addition, the slot recommender API also returns this information in the form of recommendations if there are cost-saving benefits. Recommendations are machine-generated suggestion for optimizing Google Cloud resource usage.

You can get both insights and recommendations for an individual project or across an organization.

For example, a particular recommendation might give you the following information:

• Current plan: On-demand
• Recommended plan: Monthly
• Slots: 2500
• Percentile: 99
• Cost savings: $X

Based on this recommendation, if you switch to a monthly commitment of 2500 slots, you would save $X, with no performance impact 99% of the time, assuming no change in usage from the previous month. The other 1% of the time, you might see reduced query performance.

Note: The recommendations are based on historical data, so the actual results depend on the specific characteristics of your workloads.

Slot recommendations might include a range of options, depending on whether you want to optimize for cost or performance. For example, the recommender might recommend purchasing 5000 monthly slots at p99 or 2500 monthly slots at p90. The second option would cost less, but is based on the p90 usage, so it might result in lower performance some of the time. At p100, the recommendation would cover all queries in the historical data with no impact on performance.

Some options might not save costs or might cost more than your current on-demand spending. For example, in the preceding screenshot, the Covers all existing usage (p100) option costs more to ensure query performance of a similar workload pattern.

After you view the recommendations, it can be helpful to look at your usage data as a time series. For example, you might have a spike at a particular time every day, accounting for all of the slot usage above the 99% percentile. In that case, you might:

• Purchase flex slots for those peak periods.
• Schedule queries at different times.
• Use batch jobs to queue lower priority jobs.

Before you begin

Before you can view the insights and recommendations, you must enable the Recommender API. To view the recommendations within the Google Cloud console, you must also enable the Reservations API.

Required permissions

To view slot capacity recommendations, you need the following Identity and Access Management (IAM) permissions:

• recommender.bigqueryCapacityCommitmentsRecommendations.get
• recommender.bigqueryCapacityCommitmentsRecommendations.list

To view slot capacity insights, you need the following Identity and Access Management permissions:

• recommender.bigqueryCapacityCommitmentsInsights.get
• recommender.bigqueryCapacityCommitmentsInsights.list

Note: The Google Cloud console displays both recommendations and insights within the same panel, which requires both sets of permissions.

Each of the following predefined IAM roles includes these permissions:

• BigQuery Slot Recommender Viewer
• BigQuery Resource Admin
• BigQuery Slot Recommender Admin

In addition, to view recommendations and insights for an organization, you must have the permissions listed above at the organization level. You also need the resourcemanager.organizations.get permission. The Organization Viewer IAM role includes this permission.

For more information about IAM roles in BigQuery, see Predefined roles and permissions.

Pricing

This recommender falls under the Standard recommender pricing tier. For more details on pricing tiers, refer to the recommender pricing.

View slot capacity recommendations

This section describes how to get slot recommendations using the Google Cloud console or Google Cloud CLI.

Note: A positive cost option is returned as an insight when calling gcloud CLI.

View slot recommendations by organization

To view slot recommendations and insights for an organization, perform the following steps.

Console

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the navigation panel, go to the Capacity Management section. A summary of your recommendations appears under the list of slot commitments.

3. To see detailed recommendations, click Slot estimator.

4. If the Chart options pane is not visible, then click Show chart options.

5. In the Chart options pane, in the Pricing Model list, select On Demand.

6. Detailed recommendations appear under the graph of historical usage. To see the estimated performance effect of these recommendations, use the Slot Modeling drop-down. For more information, see Model slot performance.

gcloud

View recommendations

To view recommendations by organization using the gcloud CLI, run the following command:

gcloud recommender recommendations list \
--billing-project=BILLING_PROJECT_ID \
--organization=ORGANIZATION_ID \
--location=LOCATION \
--recommender=google.bigquery.capacityCommitments.Recommender

Replace the following:

• BILLING_PROJECT_ID: The ID of a project that you've enabled with the Recommender API.
• ORGANIZATION_ID: The Google Cloud organization ID.
• LOCATION: The location that contains the resources for which you want recommendations.

For more information, see Using the API - Recommendations.

View insights

To view insights by organization using the gcloud CLI, run the following command:

gcloud recommender insights list \
--billing-project=BILLING_PROJECT_ID \
--organization=ORGANIZATION_ID \
--location=LOCATION \
--insight-type=google.bigquery.capacityCommitments.Insight

Replace the following:

• BILLING_PROJECT_ID: The ID of a project that you've enabled with the Recommender API.
• ORGANIZATION_ID: The Google Cloud organization ID.
• LOCATION: The location that contains the resources for which you want insights.

For more information, see Using the API - Insights

View slot recommendations by project

Recommendations and insights are available for projects with at least 30 days of on-demand usage data. To view slot recommendations and insights for a project, perform the following steps.

Console

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the navigation panel, go to the Capacity Management section. A summary of your recommendations appears under the list of slot commitments.

3. To see detailed recommendations, click Slot estimator.

4. In the Chart options pane, in the Pricing Model list, select On Demand.

5. Under Filters, select the project to see recommendations for.

6. Detailed recommendations appear under the graph of historical usage. To see the estimated effect of these recommendations, use the Slot Modeling drop-down. For more information, see Model slot performance.

   Note: If you filter by multiple projects, there is no cost recommendation, although you can still see the estimated performance impact.

gcloud

View recommendations

To view recommendations by project using the gcloud CLI, run the following command:

gcloud recommender recommendations list \
--project=PROJECT_ID \
--location=LOCATION \
--recommender=google.bigquery.capacityCommitments.Recommender

Replace the following:

• PROJECT_ID: The project ID.
• LOCATION: The location that contains the resources for which you want recommendations.

For more information, see Using the API - Recommendations.

View insights

To view insights by project using the gcloud CLI, run the following command:

gcloud recommender insights list \
--project=PROJECT_ID \
--location=LOCATION \
--insight-type=google.bigquery.capacityCommitments.Insight

Replace the following:

• PROJECT_ID: The project ID.
• LOCATION: The location that contains the resources for which you want insights.

For more information, see Using the API - Insights