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 both flat-rate billing and on-demand billing.

• For on-demand billing, the slot estimator displays job performance data for the past 30 days and models the possible impact of moving to reservations.

• For reservations, the slot estimator displays job performance data for the past seven days and models the possible impact of increasing or reducing your slot capacity.

You can use the slot estimator to perform the following tasks:

• View slot capacity and utilization data for your organization and for specific projects or reservations.
• 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 slots might affect performance.
• For on-demand billing, view cost recommendations on how many slots to purchase if you move to reservations.

Limitations

• Data is limited to the past seven days for reservations, and 30 days for projects that use on-demand billing.
• 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
• bigquery.jobs.listAll

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

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:

1. In the Google Cloud console, open the BigQuery page.

   Go to BigQuery

2. Select your administration project.

   1. Click the Select from drop-down list at the top of the page.
   2. In the Select from window that appears, select your project.

3. In the navigation panel, go to the Capacity management section.

4. Click the Slot estimator tab.

The utilization chart shows slot capacity and utilization over the past seven days.

The Utilization percentage tab shows slot utilization as a percentage of capacity.

The Usage and capacity tab shows organization level max slots and usage as absolute values.

If utilization is often close to peak, then adding additional slots might improve performance. If the chart shows lower utilization, then it means you had sufficient capacity during the period shown.

View utilization by reservation

By default, the slot estimator shows total capacity and utilization across all reservations in the entire organization. To view theses statistics for a particular reservation, select the reservation in the Break down drop-down list.

When you filter the chart by reservation, the capacity is the size of the reservation. 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:

1. Baseline slots.
2. Idle slot sharing (if enabled).
3. Autoscale slots.

The Utilization percentage tab shows slot utilization as a percentage of slot usage by max reservation slots. The Usage and capacity tab shows baseline slots, max slots and average slot usage.

If a reservation is consistently maxing out autoscaling slots, this might be a signal to increase the max reservation slots. To view the autoscaling slots usage details, see Using administrative resource charts.

View slot recommendations

For customers who use on-demand billing, the BigQuery slot recommender creates recommendations that help you to the cost and performance tradeoffs of purchasing different amounts of slot capacity. You can use these recommendations to evaluate how many slots to purchase if you switch to reservations. The recommendations appear under the graph of historical usage.

For more information, see View slot recommendations and insights.

Model slot performance

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 can not be less than 80% of the minimum max slots in the timeframe.

The models assume a replay of the previous seven 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.

Caution: The actual impact on job performance can vary based on future usage. The estimated performance information is only for guidance.

To model slot performance, perform the following steps:

1. In the Google Cloud console, open the BigQuery page.

   Go to BigQuery

2. Select your administration project.

   1. Click the Select from drop-down list at the top of the page.
   2. In the Select from window that appears, select your project.

3. In the navigation panel, go to the Capacity management section.

4. Click the Slot estimator tab.

5. In the Break down drop-down, select "Entire organization" or a particular reservation. If you select a reservation, the model includes the number of idle slots that the reservation was able to borrow at any given time.

6. 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 seven days, along with the estimated change in performance from increasing or decreasing max slots. The data is grouped into percentiles by job duration for all jobs that ran in the selected timeframe.

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 "7-day change" statistic for each model. This value is the estimated change in total hours spent processing the jobs in the seven-day history at different slot capacities.

Pricing

You can use the slot estimator at no charge.