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. It 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 across all reservations in your organization.
  • 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 will affect performance.

View slot capacity and utilization

To view slot capacity and utilization over time, navigate to the slot estimator:

  1. In the 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 Reservations 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.

Utilization percentage.

The Usage and capacity tab shows capacity and utilization as absolute values.

Usage and capacity.

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.

Model slot performance

You can use the slot estimator to view job performance data and to model the effect of changing the number of slots. The slot estimator lets you model how performance might change at different capacity levels, ranging from 80% to 150% of the current capacity.

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.

To model slot performance, perform the following steps:

  1. In the 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 Reservations 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 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 adding or removing slots. The data is grouped into percentiles by job duration for all jobs that ran in the selected time frame.

Slot modeling

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.

Permissions

To use the slot estimator, you need the bigquery.reservations.list and bigquery.reservationsAssignments.list Identity and Access Management (IAM) permissions on the administration project.

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

Pricing

During the Preview period, there is no charge for using the slot estimator.

Limitations

  • The slot estimator only works with reserved slots. On-demand slots are not shown.
  • Data is limited to the past seven days. To use the slot estimator, you must have existing Reservations usage in the past seven 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 will be less accurate.