Estimate slot capacity requirements
When you purchase reserved slots in BigQuery, you must estimate the
right number of slots for your particular workload. The BigQuery
slot estimator helps you to manage slot capacity based on historical performance
metrics.
You can use the slot estimator for your edition, reservation and on-demand
workloads to perform the following tasks:
For the selected
edition
workloads:
- View slot capacity and utilization data for the past 30 days and identify
periods of peak utilization when the most slots are used.
- View cost-optimal recommendations for commitment and autoscaling slots with
similar performance.
- View your current reservation settings for a specific edition.
For specific reservation workloads:
- View slot capacity and utilization data for the past 30 days and identify
periods of peak utilization when the most slots are used.
- View job latency percentiles (P90, P95, etc.) to understand query
performance.
- Model how increasing or reducing max reservation slots might affect
performance.
For on-demand billing workloads:
- View on-demand slot usage data of the entire organization or an individual
project for the past 30 days.
- View cost-optimal recommendations for commitment and autoscaling slots with
similar performance if you move to the Enterprise edition.
Customers who use Enterprise edition, Enterprise Plus edition,
or on-demand billing can use BigQuery slot recommender to view
slot usage, optimize commitments, and improve performance. For more
information, see
View edition slot
recommendations
.
Limitations
- Data is limited to the past 30 days.
- The models do not include
ML_EXTERNAL
assignments.
If a large percentage of your slots are used for
ML_EXTERNAL
assignments,
then the modeled results are less accurate.
Before you begin
Grant Identity and Access Management (IAM) roles that give users the necessary permissions
to perform each task in this document.
Required permissions
To use the slot estimator for reservations data, you need the following
IAM permissions on the administration project:
bigquery.reservations.list
bigquery.reservationAssignments.list
bigquery.capacityCommitments.list
Each of the following predefined IAM roles includes the
permissions that you need in order to use the slot estimator:
roles/bigquery.admin
roles/bigquery.resourceAdmin
roles/bigquery.resourceEditor
roles/bigquery.resourceViewer
roles/bigquery.user
To use the slot estimator for on-demand usage data, you need to
enable the
Reservations
API
on a
project you intend to use as the administration project to manage reservations.
Other than the permissions above, you also need one of the following
IAM permissions on your organization to see organization-level
data or the project to see project-level data:
bigquery.jobs.listExecutionMetadata
(can only be applied on organization
level)
bigquery.jobs.listAll
(can be applied on both organization or project
level)
Each of the following predefined IAM roles includes the
permissions that you need in order to use the slot estimator:
roles/bigquery.admin
roles/bigquery.resourceAdmin
roles/bigquery.resourceEditor
roles/bigquery.resourceViewer
To view the commitment slots recommendations, you also need the permissions
described in
View edition slot
recommendations
.
For more information about IAM roles in BigQuery,
see
Predefined roles and permissions
.
View slot capacity and utilization
To view slot capacity and utilization over time, navigate to the slot estimator:
In the Google Cloud console, open the BigQuery page.
Go to BigQuery
Select your administration project.
- Click the
Select from
drop-down list at the top of the page.
- In the
Select from window
that appears, select your project.
In the navigation panel, go to the
Capacity management
section.
Click the
Slot estimator
tab.
The utilization chart shows slot capacity and utilization over the past 30 days,
calculated using hourly granularity.
The
Usage and utilization by percentage
tab shows slot utilization as a
percentage of slot usage by max slots.
The
Usage and utilization by capacity
tab shows max slots and usage as
absolute values.
You can choose an edition or on-demand option from the
Source
drop-down to
view statistics for different scopes. Selecting an edition populates the
Reservation
drop-down with relevant reservations.
For on-demand options, you can choose either an individual project or the entire
organization from the
Recommendations for
drop-down if you have organization level permissions.
The Slot Estimator page only shows the project-level information if you only
have project level permissions.
The statistics for
Usage and utilization by capacity
tab may vary slightly
based on different scope:
- For edition source, it shows max slots available for the entire edition,
commitment slots, sum of baseline slots, average slot usage, P99 slot usage
and P50 slot usage.
- For specific reservation, it shows max reservation slots, baseline slots,
average slot usage, P99 slot usage and P50 slot usage.
- For on-demand source, it shows average slot usage, P99 slot usage and P50
slot usage.
When a reservation is selected, you can use the slot estimator to view job
performance data and to model the effect of changing the number of max slots.
The slot estimator lets you model how performance might change at different
capacity levels, ranging from 80% to 150% of the current max slots. The
decrement option cannot be less than 80% of the minimum max slots in the
timeframe.
The models assume a replay of the previous 30 days' usage pattern, where
everything remains the same except for a change in slots.
The estimated performance improvement is based on several factors. The most
important factors are the number of slots in the model, and the proportion of
jobs in each percentile bucket that ran during peak periods versus regular
periods. Peak periods are defined as durations in which almost all slots were
used. Jobs running during these times are most impacted by slot contention, and
therefore see the most performance gain from additional slots. As a result,
different buckets of jobs can see different effects from the same capacity
increase, depending on when they are run.
To model slot performance, perform the following steps:
In the Google Cloud console, open the BigQuery page.
Go to BigQuery
Select your administration project.
- Click the
Select from
drop-down list at the top of the page.
- In the
Select from window
that appears, select your project.
In the navigation panel, go to the
Capacity management
section.
Click the
Slot estimator
tab.
In the
Reservation
drop-down, select a particular reservation. The model
includes the number of idle slots that the reservation was able to borrow at
any given time.
In the
Model with additional slots on max slots
drop-down, select one or
more values of slots to model and click
OK
.
The table under
Changes in job performance with additional slots
shows job
performance data from the past 30 days, along with the estimated change in
performance from increasing or decreasing max slots. The data is grouped into
percentages by job duration for all jobs that ran in the selected timeframe.
The column denoted by the light bulb icon corresponds to the performance-enhancing
recommendation for the selected reservation.
The performance data is broken down by percentile. The table splits the data
into at most 12 buckets: P10 through P90, plus P95, P99, and P100. The P100
bucket represents the top 1% of jobs that took the longest time to run; P99
includes the top 96% to 99%; P95 includes the top 91% to 95%; P90 includes 81%
to 90%; and so forth. Depending on the data, the table may group the data into
fewer buckets. In that case, the table contains fewer rows.
For each percentile bucket, the table shows the following information:
- Job duration percentile: The percentile bucket for this row.
- Average job duration: The average time that jobs in that percentile bucket
took to run.
- Number of jobs: The number of jobs in that percentile bucket.
- For each model, the estimated average duration for jobs in that percentile.
The table also lists an estimated "30-day change" statistic for each model. This
value is the estimated change in total hours spent processing the jobs in the
30-day history at different slot capacities.
Understand the modeling results with slot usage
For fixed-capacity reservations, if idle slot sharing is enabled, then jobs in
that reservation can borrow idle slots from other reservations. As a result,
utilization can exceed 100% of allocated slots. If a reservation consistently
borrows idle slots from other reservations, this might be a signal to increase
the reservation size. On the other hand, if a reservation seldom uses its full
capacity, the reservation might be too large.
Reservations that use
autoscaling
use
and add slots in the following priority:
- Baseline slots.
- Idle slot sharing (if enabled).
- Autoscale slots.
If an autoscaling reservation is consistently maxing out autoscaling slots, this
might be a signal to increase the max reservation slots. For information about
viewing your slot usage, see
View administrative resource
charts
.
Pricing
You can use the slot estimator at no charge.