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.
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
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.
View slot recommendations by organization
To view slot recommendations and insights for an organization, perform the following steps.
Console
In the Google Cloud console, go to the BigQuery page.
In the navigation panel, go to the Capacity Management section. A summary of your recommendations appears under the list of slot commitments.
To see detailed recommendations, click Slot estimator.
If the Chart options pane is not visible, then click Show chart options.
In the Chart options pane, in the Pricing Model list, select On Demand.
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
In the Google Cloud console, go to the BigQuery page.
In the navigation panel, go to the Capacity Management section. A summary of your recommendations appears under the list of slot commitments.
To see detailed recommendations, click Slot estimator.
In the Chart options pane, in the Pricing Model list, select On Demand.
Under Filters, select the project to see recommendations for.
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.
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