Introduction to slots autoscaling

Reservations that you configure to use slots autoscaling automatically scale the allocated capacity to accommodate your workload demands. As your workload increases or decreases, BigQuery dynamically adjusts your slots to an appropriate level. Reservations with slots autoscaling are only available with BigQuery editions.

Use autoscaling reservations

You don't need to purchase slot commitments before creating autoscaling reservations. Slot commitments provide a discounted rate for consistently used slots but are optional with autoscaling reservations. To create an autoscaling reservation, you assign a reservation a maximum number of slots (the max reservation size). You can identify the maximum number of autoscaling slots by subtracting the max reservation size by any optional baseline slots assigned to the reservation.

When you create autoscaling reservations, consider the following:

• BigQuery scales reservations almost instantly until it has reached the number of slots needed to execute the jobs, or it reaches the maximum number of slots available to the reservation. Slots always autoscale to a multiple of 50.
• Scaling up is based on actual usage, and is rounded up to the nearest 50 slot increment.
• Your autoscaled slots are charged at capacity compute pricing for your associated edition while scaling up. You are charged for the number of scaled slots, not the number of slots used. This charge applies even if the job that causes BigQuery to scale up fails. For this reason, don't use the jobs information schema to match the billing. Instead, see Monitor autoscaling with information schema.
• While the number of slots always scales by multiples of 50, it may scale more than 50 slots within one step. For example, if your workload requires an additional 450 slots, BigQuery can attempt to scale by 450 slots at once to meet the capacity requirement.
• BigQuery scales down when the jobs associated with the reservation no longer need the capacity (subject to a 1 minute minimum).

Any autoscaled capacity is retained for at least 60 seconds. This 60-second period is called the scale-down window. Any new peak in capacity resets the scale-down window, treating the entire capacity level as a new grant. However, if 60 seconds or more have passed since the last capacity increase and there is less demand, the system reduces the capacity without resetting the scale-down window, enabling consecutive decreases without an imposed delay.

For example, if your initial workload capacity scales to 100 slots, the peak is retained for at least 60 seconds. If, during that scale-down window, your workload scales to a new peak of 200 slots, a new scale-down window begins for 60 seconds. If there is no new peak during this scale-down window, your workload begins to scale down at the end of the 60 seconds.

Consider the following detailed example: At 12:00:00, your initial capacity scales to 100 slots and the usage lasts for one second. That peak is retained for at least 60 seconds, beginning at 12:00:00. After the 60 seconds have elapsed (at 12:01:01), if the new usage is 50 slots, BigQuery scales down to 50 slots. If, at 12:01:02, the new usage is 0 slots, BigQuery again scales down immediately to 0 slots. After the scale-down window has ended, BigQuery can scale down multiple times consecutively without requiring a new scale-down window.

To learn how to work with autoscaling, see Work with slots autoscaling.

Using reservations with baseline and autoscaling slots

In addition to specifying the maximum reservation size, you can optionally specify a baseline number of slots per reservation. The baseline is the minimum number of slots that will always be allocated to the reservation, and you will always be charged for them. Autoscaling slots are only added after all of the baseline slots (and idle slots if applicable) are consumed. You can share idle baseline slots in one reservation with other reservations that need capacity.

You can increase the number of baseline slots in a reservation every few minutes. If you want to decrease your baseline slots, you are limited to once an hour if you have recently changed your baseline slot capacity and your baseline slots exceed your committed slots. Otherwise, you can decrease your baseline slots every few minutes.

Baseline and autoscaling slots are intended to provide capacity based on your recent workload. If you anticipate a large workload that is very different from your workloads in the recent past, we recommend increasing your baseline capacity ahead of the event rather than rely on autoscaling slots to cover the workload capacity. If you encounter an issue with increasing your baseline capacity, retry the request after waiting 15 minutes.

If the reservation doesn't have baseline slots or is not configured to borrow idle slots from other reservations, then BigQuery attempts to scale. Otherwise, baseline slots must be fully utilized before scaling.

Reservations use and add slots in the following priority:

1. Baseline slots.
2. Idle slot sharing (if enabled). Reservations can only share idle baseline or committed slots from other reservations that were created with the same edition and the same region.
3. Autoscale slots.

In the following example, slots scale from a specified baseline amount. The etl and dashboard reservations have a baseline size of 700 and 300 slots respectively.

Autoscaling example with no commitments.

In this example, the etl reservation can scale to 1300 slots (700 baseline slots plus 600 autoscale slots). If the dashboard reservation is not in use, the etl reservation can use the 300 slots from the dashboard reservation if no job is running there, leading to a maximum of 1600 possible slots.

The dashboard reservation can scale to 1100 slots (300 baseline slots plus 800 autoscale slots). If the etl reservation is totally idle, the dashboard reservation can scale to a maximum of 1800 slots (300 baseline slots plus 800 autoscale slots plus 700 idle slots in the etl reservation).

If the etl reservation requires more than 700 baseline slots, which are always available, it attempts to add slots by using the following methods in order:

1. 700 baseline slots.
2. Idle slot sharing with the 300 baseline slots in the dashboard reservation. Your reservation only shares idle baseline slots with other reservations that are created with the same edition.
3. Scaling up 600 additional slots to the maximum reservation size.

Using slot commitments

The following example shows slots autoscaling using capacity commitments.

Autoscaling example

Like reservation baselines, slot commitments allow you to allocate a fixed number of slots that are available to all reservations. Unlike baseline slots, a commitment cannot be reduced during the term. Slot commitments are optional but can save costs if baseline slots are required for long periods of time. Slot commitments are used to cover baseline slots for your reservations. Any unused slot capacity is then shared as idle slots across other reservations. Slot commitments don't apply to autoscaling slots. To ensure that you receive the discounted rate for your committed slots, make sure that your slot commitments are sufficient to cover your baseline slots.

In this example, you are charged a predefined rate for the capacity commitment slots. You are charged at the autoscaling rate for the number of autoscaling slots after autoscaling activates and reservations are in an upscaled state. For autoscaling rate, you are charged for the number of scaled slots, not the number of slots used.

The following example shows reservations when the number of baseline slots exceeds the number of committed slots.

Baseline slots exceed the number of committed slots.

In this example, there is a total of 1000 baseline slots between the two reservations, 500 from the etl reservation and 500 from the dashboard reservation. However, the commitment only covers 800 slots. In this scenario, the excess slots are charged at the pay as you go (PAYG) rate.

Maximum available slots

You can calculate the maximum number of slots a reservation can use by adding the baseline slots, the maximum number of autoscale slots, and any slots in commitments that were created with the same edition and are not covered by the baseline slots. The example in the previous image is set up as follows:

• A capacity commitment of 1000 annual slots. Those slots are assigned as baseline slots in the etl reservation and the dashboard reservation.
• 700 baseline slots assigned to the etl reservation.
• 300 baseline slots assigned to the dashboard reservation.
• Autoscale slots of 600 for the etl reservation.
• Autoscale slots of 800 for the dashboard reservation.

For the etl reservation, the maximum number of slots possible is equal to the etl baseline slots (700) plus the dashboard baseline slots (300, if all slots are idle) plus the maximum number of autoscale slots (600). So the maximum number of slots the etl reservation could use in this example is 1600. This number exceeds the number in the capacity commitment.

In the following example, the annual commitment exceeds the assigned baseline slots.

Calculating available slots

In this example, we have:

• A capacity commitment of 1600 annual slots.
• A maximum reservation size of 1500 (including 500 autoscaling slots).
• 1000 baseline slots assigned to the etl reservation.

The maximum number of slots available to the reservation is equal to the baseline slots (1000) plus any committed idle slots not dedicated to the baseline slots (1600 annual slots - 1000 baseline slots = 600) plus the number of autoscaling slots (500). So the maximum potential slots in this reservation is 2100. The autoscaled slots are additional slots above the capacity commitment.

Autoscaling best practices

1. When first using autoscaler, set the number of autoscaling slots to a meaningful number based on past and expected performance. Once the reservation is created, actively monitor the failure rate, performance, and bill and adjust the number of autoscaling slots as needed.

2. Autoscaler has a 1 minute minimum before scaling down so it is important to set the maximum number of autoscaled slots to balance between performance and cost. If the maximum number of autoscale slots is too large and your job can use all the slots to complete a job in seconds, you still incur costs for the maximum slots for the full minute. If you lower your max slots to half the current amount, your reservation is scaled to a lower number and the job can use more slot_seconds during that minute, reducing waste. For help determining your slot requirements, see Monitor job performance. As an alternative approach to determine your slot requirements, see View edition slot recommendations.

3. Slot usage can occasionally exceed the sum of your baseline plus scaled slots. You are not billed for slot usage that is greater than your baseline plus scaled slots.

4. Autoscaler is most efficient for heavy, long-running workloads, such as workloads with multiple concurrent queries. Avoid sending queries one at a time, since each query scales the reservation where it will remain scaled for a 1 minute minimum. If you continuously send queries, causing a constant workload, setting a baseline and buying a commitment provides constant capacity at a discounted price.

5. BigQuery autoscaling is subject to capacity availability. BigQuery attempts to meet customer capacity demand based on historical usage. To achieve capacity guarantees, you can set an optional slot baseline, which is the number of guaranteed slots in a reservation. With baselines, slots are immediately available and you pay for them whether you use them or not. To ensure capacity is available for large, inorganic demands, such as high-traffic holidays, contact the BigQuery team several weeks in advance.

6. Baseline slots are always charged. If a capacity commitment expires, you might need to manually adjust the amount of baseline slots in your reservations to avoid any unwanted charges. For example, consider that you have a 1-year commitment with 100 slots and a reservation with 100 baseline slots. The commitment expires and doesn't have a renewal plan. Once the commitment expires, you pay for 100 baseline slots at the pay as you go rate.

Monitor autoscaling

When you monitor slot usage with administrative resource charts, you might see significantly more scaled slots than your slot usage because the charts smooth the number of used slots over the alignment period. To view autoscale slot usage with more precise details, reduce the time frame option. This automatically updates the alignment period to a smaller increment.

In the following example, the chart displays significantly more scaled slots than the workload demands.

The alignment period is set to a one minute interval and the scaled slots appear more than the slot usage demands.

However, if you shorten the time frame option so that the alignment period is two seconds, you can see that the autoscaler scales to the workload demand and displays more accurate data. You can adjust the time frame option by dragging the start and end ranges of the time frame option. To display the most accurate workload demand data, select p99 from the Metric list.

The alignment period is set to a two second interval and the scaled slots are appropriate for the workload demand.

For the most accurate view of autoscale usage, use an alignment period between 1 and 15 seconds.

For information about viewing your slot usage, see View administrative resource charts

Monitor autoscaling with information schema

You can use the following SQL scripts to check the billed slot seconds for a particular edition. You must run these scripts in the same project the reservations were created. The first script shows billed slot seconds covered by commitment_plan while the second script shows billed slot seconds that aren't covered by a commitment.

You only need to set the value of three variables to run these scripts:

• start_time
• end_time
• edition_to_check

These scripts are subject to the following caveats:

• Deleted reservations and capacity commitments are removed from information schema views at the end of the data retention period. Specify a recent window of time which doesn't contain deleted reservations and commitments for correct results.

• The result of the scripts may not exactly match the bill due to small rounding errors.

The following script aggregates autoscaling slots per edition.

SELECT
edition,
SUM(s.autoscale_current_slots)ASautoscale_slot_seconds
FROM
`region-us.INFORMATION_SCHEMA.RESERVATIONS_TIMELINE`m
JOIN
m.per_second_detailss
WHERE
period_startBETWEEN'2025-09-28'
AND'2025-09-29'
GROUPBY
edition
ORDERBY
edition

The following script aggregates autoscaling slots per reservation.

selectreservation_id,sum(s.autoscale_current_slots)asautoscale_slot_seconds
from`region-us.INFORMATION_SCHEMA.RESERVATIONS_TIMELINE`m
LEFTJOINm.per_second_detailss
WHEREperiod_startbetween'2025-09-28'and'2025-09-29'
groupbyreservation_id
orderbyreservation_id

DECLAREstart_time,end_timeTIMESTAMP;
DECLARE
edition_to_checkSTRING;
/* Google uses Pacific Time to calculate the billing period for all customers,
regardless of their time zone. Use the following format if you want to match the
billing report. Change the start_time and end_time values to match the desired
window. */
/* The following three variables (start_time, end_time, and edition_to_check)
are the only variables that you need to set in the script.
During daylight savings time, the start_time and end_time variables should
follow this format: 2024年02月20日 00:00:00-08. */
SETstart_time="2023年07月20日 00:00:00-07";
SETend_time="2023年07月28日 00:00:00-07";
SETedition_to_check='ENTERPRISE';
/* The following function returns the slot seconds for the time window between
two capacity changes. For example, if there are 100 slots between (2023年06月01日
10:00:00, 2023年06月01日 11:00:00), then during that window the total slot seconds
will be 100 * 3600.
This script calculates a specific window (based on the variables defined above),
which is why the following script includes script_start_timestamp_unix_millis
and script_end_timestamp_unix_millis. */
CREATETEMPFUNCTION
GetSlotSecondsBetweenChanges(
slotsFLOAT64,
range_begin_timestamp_unix_millisFLOAT64,
range_end_timestamp_unix_millisFLOAT64,
script_start_timestamp_unix_millisFLOAT64,
script_end_timestamp_unix_millisFLOAT64)
RETURNSINT64
LANGUAGEjs
ASr"""
 if (script_end_timestamp_unix_millis < range_begin_timestamp_unix_millis || script_start_timestamp_unix_millis> range_end_timestamp_unix_millis) {
 return 0;
 }
 var begin = Math.max(script_start_timestamp_unix_millis, range_begin_timestamp_unix_millis)
 var end = Math.min(script_end_timestamp_unix_millis, range_end_timestamp_unix_millis)
 return slots * Math.ceil((end - begin) / 1000.0)
""";
/*
Sample CAPACITY_COMMITMENT_CHANGES data (unrelated columns ignored):
+---------------------+------------------------+-----------------+--------+------------+--------+
| change_timestamp | capacity_commitment_id | commitment_plan | state | slot_count | action |
+---------------------+------------------------+-----------------+--------+------------+--------+
| 2023年07月20日 19:30:27 | 12954109101902401697 | ANNUAL | ACTIVE | 100 | CREATE |
| 2023年07月27日 22:29:21 | 11445583810276646822 | FLEX | ACTIVE | 100 | CREATE |
| 2023年07月27日 23:10:06 | 7341455530498381779 | MONTHLY | ACTIVE | 100 | CREATE |
| 2023年07月27日 23:11:06 | 7341455530498381779 | FLEX | ACTIVE | 100 | UPDATE |
The last row indicates a special change from MONTHLY to FLEX, which happens
because of commercial migration.
*/
WITH
/*
 Information containing which commitment might have plan
 updated (e.g. renewal or commercial migration). For example:
 +------------------------+------------------+--------------------+--------+------------+--------+-----------+----------------------------+
 | change_timestamp | capacity_commitment_id | commitment_plan | state | slot_count | action | next_plan | next_plan_change_timestamp |
 +---------------------+------------------------+-----------------+--------+------------+--------+-----------+----------------------------+
 | 2023年07月20日 19:30:27 | 12954109101902401697 | ANNUAL | ACTIVE | 100 | CREATE | ANNUAL | 2023年07月20日 19:30:27 |
 | 2023年07月27日 22:29:21 | 11445583810276646822 | FLEX | ACTIVE | 100 | CREATE | FLEX | 2023年07月27日 22:29:21 |
 | 2023年07月27日 23:10:06 | 7341455530498381779 | MONTHLY | ACTIVE | 100 | CREATE | FLEX | 2023年07月27日 23:11:06 |
 | 2023年07月27日 23:11:06 | 7341455530498381779 | FLEX | ACTIVE | 100 | UPDATE | FLEX | 2023年07月27日 23:11:06 |
 */
commitments_with_next_planAS(
SELECT
*,
IFNULL(
LEAD(commitment_plan)
OVER(
PARTITIONBYcapacity_commitment_idORDERBYchange_timestampASC
),
commitment_plan)
next_plan,
IFNULL(
LEAD(change_timestamp)
OVER(
PARTITIONBYcapacity_commitment_idORDERBYchange_timestampASC
),
change_timestamp)
next_plan_change_timestamp
FROM
`region-us.INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT`
),
/*
 Insert a 'DELETE' action for those with updated plans. The FLEX commitment
 '7341455530498381779' is has no 'CREATE' action, and is instead labeled as an
 'UPDATE' action.
 For example:
 +---------------------+------------------------+-----------------+--------+------------+--------+
 | change_timestamp | capacity_commitment_id | commitment_plan | state | slot_count | action |
 +---------------------+------------------------+-----------------+--------+------------+--------+
 | 2023年07月20日 19:30:27 | 12954109101902401697 | ANNUAL | ACTIVE | 100 | CREATE |
 | 2023年07月27日 22:29:21 | 11445583810276646822 | FLEX | ACTIVE | 100 | CREATE |
 | 2023年07月27日 23:10:06 | 7341455530498381779 | MONTHLY | ACTIVE | 100 | CREATE |
 | 2023年07月27日 23:11:06 | 7341455530498381779 | FLEX | ACTIVE | 100 | UPDATE |
 | 2023年07月27日 23:11:06 | 7341455530498381779 | MONTHLY | ACTIVE | 100 | DELETE |
 */
capacity_changes_with_additional_deleted_event_for_changed_planAS(
SELECT
next_plan_change_timestampASchange_timestamp,
project_id,
project_number,
capacity_commitment_id,
commitment_plan,
state,
slot_count,
'DELETE'ASaction,
commitment_start_time,
commitment_end_time,
failure_status,
renewal_plan,
user_email,
edition,
is_flat_rate,
FROMcommitments_with_next_plan
WHEREcommitment_plannext_plan
UNIONALL
SELECT*FROM`region-us.INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT`
),
/*
 The committed_slots change the history. For example:
 +---------------------+------------------------+------------------+-----------------+
 | change_timestamp | capacity_commitment_id | slot_count_delta | commitment_plan |
 +---------------------+------------------------+------------------+-----------------+
 | 2023年07月20日 19:30:27 | 12954109101902401697 | 100 | ANNUAL |
 | 2023年07月27日 22:29:21 | 11445583810276646822 | 100 | FLEX |
 | 2023年07月27日 23:10:06 | 7341455530498381779 | 100 | MONTHLY |
 | 2023年07月27日 23:11:06 | 7341455530498381779 | -100 | MONTHLY |
 | 2023年07月27日 23:11:06 | 7341455530498381779 | 100 | FLEX |
 */
capacity_commitment_slot_dataAS(
SELECT
change_timestamp,
capacity_commitment_id,
CASE
WHENaction="CREATE"ORaction="UPDATE"
THEN
IFNULL(
IF(
LAG(action)
OVER(
PARTITIONBYcapacity_commitment_idORDERBYchange_timestampASC,actionASC
)
INUNNEST(['CREATE','UPDATE']),
slot_count-LAG(slot_count)
OVER(
PARTITIONBYcapacity_commitment_idORDERBYchange_timestampASC,actionASC
),
slot_count),
slot_count)
ELSE
IF(
LAG(action)
OVER(PARTITIONBYcapacity_commitment_idORDERBYchange_timestampASC,actionASC)
INUNNEST(['CREATE','UPDATE']),
-1*slot_count,
0)
END
ASslot_count_delta,
commitment_plan
FROM
capacity_changes_with_additional_deleted_event_for_changed_plan
WHERE
state="ACTIVE"
ANDedition=edition_to_check
ANDchange_timestamp<=end_time
),
/*
 The total_committed_slots history for each plan. For example:
 +---------------------+---------------+-----------------+
 | change_timestamp | capacity_slot | commitment_plan |
 +---------------------+---------------+-----------------+
 | 2023年07月20日 19:30:27 | 100 | ANNUAL |
 | 2023年07月27日 22:29:21 | 100 | FLEX |
 | 2023年07月27日 23:10:06 | 100 | MONTHLY |
 | 2023年07月27日 23:11:06 | 0 | MONTHLY |
 | 2023年07月27日 23:11:06 | 200 | FLEX |
 */
running_capacity_commitment_slot_dataAS(
SELECT
change_timestamp,
SUM(slot_count_delta)
OVER(
PARTITIONBYcommitment_plan
ORDERBYchange_timestamp
RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW
)
AScapacity_slot,
commitment_plan,
FROM
capacity_commitment_slot_data
),
/*
 The slot_seconds between each changes, partitioned by each plan. For example:
 +---------------------+--------------+-----------------+
 | change_timestamp | slot_seconds | commitment_plan |
 +---------------------+--------------+-----------------+
 | 2023年07月20日 19:30:27 | 64617300 | ANNUAL |
 | 2023年07月27日 22:29:21 | 250500 | FLEX |
 | 2023年07月27日 23:10:06 | 6000 | MONTHLY |
 | 2023年07月27日 23:11:06 | 0 | MONTHLY |
 | 2023年07月27日 23:11:06 | 5626800 | FLEX |
 */
slot_seconds_dataAS(
SELECT
change_timestamp,
GetSlotSecondsBetweenChanges(
capacity_slot,
UNIX_MILLIS(change_timestamp),
UNIX_MILLIS(
IFNULL(
LEAD(change_timestamp)
OVER(PARTITIONBYcommitment_planORDERBYchange_timestampASC),
CURRENT_TIMESTAMP())),
UNIX_MILLIS(start_time),
UNIX_MILLIS(end_time))ASslot_seconds,
commitment_plan,
FROM
running_capacity_commitment_slot_data
WHERE
change_timestamp<=end_time
)
/*
The final result is similar to the following:
+-----------------+--------------------+
| commitment_plan | total_slot_seconds |
+-----------------+--------------------+
| ANNUAL | 64617300 |
| MONTHLY | 6000 |
| FLEX | 5877300 |
*/
SELECT
commitment_plan,
SUM(slot_seconds)AStotal_slot_seconds
FROM
slot_seconds_data
GROUPBY
commitment_plan

/*
This script has several parts:
1. Calculate the baseline and scaled slots for reservations
2. Calculate the committed slots
3. Join the two results above to calculate the baseline not covered by committed
 slots
4. Aggregate the number
*/
-- variables
DECLAREstart_time,end_timeTIMESTAMP;
DECLARE
edition_to_checkSTRING;
/* Google uses Pacific Time to calculate the billing period for all customers,
regardless of their time zone. Use the following format if you want to match the
billing report. Change the start_time and end_time values to match the desired
window. */
/* The following three variables (start_time, end_time, and edition_to_check)
are the only variables that you need to set in the script.
During daylight savings time, the start_time and end_time variables should
follow this format: 2024年02月20日 00:00:00-08. */
SETstart_time="2023年07月20日 00:00:00-07";
SETend_time="2023年07月28日 00:00:00-07";
SETedition_to_check='ENTERPRISE';
/*
The following function returns the slot seconds for the time window between
two capacity changes. For example, if there are 100 slots between (2023年06月01日
10:00:00, 2023年06月01日 11:00:00), then during that window the total slot seconds
will be 100 * 3600.
This script calculates a specific window (based on the variables defined above),
which is why the following script includes script_start_timestamp_unix_millis
and script_end_timestamp_unix_millis. */
CREATETEMPFUNCTIONGetSlotSecondsBetweenChanges(
slotsFLOAT64,
range_begin_timestamp_unix_millisFLOAT64,
range_end_timestamp_unix_millisFLOAT64,
script_start_timestamp_unix_millisFLOAT64,
script_end_timestamp_unix_millisFLOAT64)
RETURNSINT64
LANGUAGEjs
ASr"""
 if (script_end_timestamp_unix_millis < range_begin_timestamp_unix_millis || script_start_timestamp_unix_millis> range_end_timestamp_unix_millis) {
 return 0;
 }
 var begin = Math.max(script_start_timestamp_unix_millis, range_begin_timestamp_unix_millis)
 var end = Math.min(script_end_timestamp_unix_millis, range_end_timestamp_unix_millis)
 return slots * Math.ceil((end - begin) / 1000.0)
""";
/*
Sample RESERVATION_CHANGES data (unrelated columns ignored):
+---------------------+------------------+--------+---------------+---------------+
| change_timestamp | reservation_name | action | slot_capacity | current_slots |
+---------------------+------------------+--------+---------------+---------------+
| 2023年07月27日 22:24:15 | res1 | CREATE | 300 | 0 |
| 2023年07月27日 22:25:21 | res1 | UPDATE | 300 | 180 |
| 2023年07月27日 22:39:14 | res1 | UPDATE | 300 | 100 |
| 2023年07月27日 22:40:20 | res2 | CREATE | 300 | 0 |
| 2023年07月27日 22:54:18 | res2 | UPDATE | 300 | 120 |
| 2023年07月27日 22:55:23 | res1 | UPDATE | 300 | 0 |
Sample CAPACITY_COMMITMENT_CHANGES data (unrelated columns ignored):
+---------------------+------------------------+-----------------+--------+------------+--------+
| change_timestamp | capacity_commitment_id | commitment_plan | state | slot_count | action |
+---------------------+------------------------+-----------------+--------+------------+--------+
| 2023年07月20日 19:30:27 | 12954109101902401697 | ANNUAL | ACTIVE | 100 | CREATE |
| 2023年07月27日 22:29:21 | 11445583810276646822 | FLEX | ACTIVE | 100 | CREATE |
| 2023年07月27日 23:10:06 | 7341455530498381779 | MONTHLY | ACTIVE | 100 | CREATE |
*/
WITH
/*
 The scaled_slots & baseline change history:
 +---------------------+------------------+------------------------------+---------------------+
 | change_timestamp | reservation_name | autoscale_current_slot_delta | baseline_slot_delta |
 +---------------------+------------------+------------------------------+---------------------+
 | 2023年07月27日 22:24:15 | res1 | 0 | 300 |
 | 2023年07月27日 22:25:21 | res1 | 180 | 0 |
 | 2023年07月27日 22:39:14 | res1 | -80 | 0 |
 | 2023年07月27日 22:40:20 | res2 | 0 | 300 |
 | 2023年07月27日 22:54:18 | res2 | 120 | 0 |
 | 2023年07月27日 22:55:23 | res1 | -100 | 0 |
 */
reservation_slot_dataAS(
SELECT
change_timestamp,
reservation_name,
CASEaction
WHEN"CREATE"THENautoscale.current_slots
WHEN"UPDATE"
THEN
IFNULL(
autoscale.current_slots-LAG(autoscale.current_slots)
OVER(
PARTITIONBYproject_id,reservation_name
ORDERBYchange_timestampASC,actionASC
),
IFNULL(
autoscale.current_slots,
IFNULL(
-1*LAG(autoscale.current_slots)
OVER(
PARTITIONBYproject_id,reservation_name
ORDERBYchange_timestampASC,actionASC
),
0)))
WHEN"DELETE"
THEN
IF(
LAG(action)
OVER(
PARTITIONBYproject_id,reservation_name
ORDERBYchange_timestampASC,actionASC
)
INUNNEST(['CREATE','UPDATE']),
-1*autoscale.current_slots,
0)
END
ASautoscale_current_slot_delta,
CASEaction
WHEN"CREATE"THENslot_capacity
WHEN"UPDATE"
THEN
IFNULL(
slot_capacity-LAG(slot_capacity)
OVER(
PARTITIONBYproject_id,reservation_name
ORDERBYchange_timestampASC,actionASC
),
IFNULL(
slot_capacity,
IFNULL(
-1*LAG(slot_capacity)
OVER(
PARTITIONBYproject_id,reservation_name
ORDERBYchange_timestampASC,actionASC
),
0)))
WHEN"DELETE"
THEN
IF(
LAG(action)
OVER(
PARTITIONBYproject_id,reservation_name
ORDERBYchange_timestampASC,actionASC
)
INUNNEST(['CREATE','UPDATE']),
-1*slot_capacity,
0)
END
ASbaseline_slot_delta,
FROM
`region-us.INFORMATION_SCHEMA.RESERVATION_CHANGES`
WHERE
edition=edition_to_check
ANDchange_timestamp<=end_time
),
-- Convert the above to running total
/*
 +---------------------+-------------------------+----------------+
 | change_timestamp | autoscale_current_slots | baseline_slots |
 +---------------------+-------------------------+----------------+
 | 2023年07月27日 22:24:15 | 0 | 300 |
 | 2023年07月27日 22:25:21 | 180 | 300 |
 | 2023年07月27日 22:39:14 | 100 | 300 |
 | 2023年07月27日 22:40:20 | 100 | 600 |
 | 2023年07月27日 22:54:18 | 220 | 600 |
 | 2023年07月27日 22:55:23 | 120 | 600 |
 */
running_reservation_slot_dataAS(
SELECT
change_timestamp,
SUM(autoscale_current_slot_delta)
OVER(ORDERBYchange_timestampRANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)
ASautoscale_current_slots,
SUM(baseline_slot_delta)
OVER(ORDERBYchange_timestampRANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)
ASbaseline_slots,
FROM
reservation_slot_data
),
/*
 The committed_slots change history. For example:
 +---------------------+------------------------+------------------+
 | change_timestamp | capacity_commitment_id | slot_count_delta |
 +---------------------+------------------------+------------------+
 | 2023年07月20日 19:30:27 | 12954109101902401697 | 100 |
 | 2023年07月27日 22:29:21 | 11445583810276646822 | 100 |
 | 2023年07月27日 23:10:06 | 7341455530498381779 | 100 |
 */
capacity_commitment_slot_dataAS(
SELECT
change_timestamp,
capacity_commitment_id,
CASE
WHENaction="CREATE"ORaction="UPDATE"
THEN
IFNULL(
IF(
LAG(action)
OVER(
PARTITIONBYcapacity_commitment_idORDERBYchange_timestampASC,actionASC
)
INUNNEST(['CREATE','UPDATE']),
slot_count-LAG(slot_count)
OVER(
PARTITIONBYcapacity_commitment_idORDERBYchange_timestampASC,actionASC
),
slot_count),
slot_count)
ELSE
IF(
LAG(action)
OVER(PARTITIONBYcapacity_commitment_idORDERBYchange_timestampASC,actionASC)
INUNNEST(['CREATE','UPDATE']),
-1*slot_count,
0)
END
ASslot_count_delta
FROM
`region-us.INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT`
WHERE
state="ACTIVE"
ANDedition=edition_to_check
ANDchange_timestamp<=end_time
),
/*
 The total_committed_slots history. For example:
 +---------------------+---------------+
 | change_timestamp | capacity_slot |
 +---------------------+---------------+
 | 2023年07月20日 19:30:27 | 100 |
 | 2023年07月27日 22:29:21 | 200 |
 | 2023年07月27日 23:10:06 | 300 |
 */
running_capacity_commitment_slot_dataAS(
SELECT
change_timestamp,
SUM(slot_count_delta)
OVER(ORDERBYchange_timestampRANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)
AScapacity_slot
FROM
capacity_commitment_slot_data
),
/* Add next_change_timestamp to the above data,
 which will be used when joining with reservation data. For example:
 +---------------------+-----------------------+---------------+
 | change_timestamp | next_change_timestamp | capacity_slot |
 +---------------------+-----------------------+---------------+
 | 2023年07月20日 19:30:27 | 2023年07月27日 22:29:21 | 100 |
 | 2023年07月27日 22:29:21 | 2023年07月27日 23:10:06 | 200 |
 | 2023年07月27日 23:10:06 | 2023年07月31日 00:14:37 | 300 |
 */
running_capacity_commitment_slot_data_with_next_changeAS(
SELECT
change_timestamp,
IFNULL(LEAD(change_timestamp)OVER(ORDERBYchange_timestampASC),CURRENT_TIMESTAMP())
ASnext_change_timestamp,
capacity_slot
FROM
running_capacity_commitment_slot_data
),
/*
 Whenever we have a change in reservations or commitments,
 the scaled_slots_and_baseline_not_covered_by_commitments will be changed.
 Hence we get a collection of all the change_timestamp from both tables.
 +---------------------+
 | change_timestamp |
 +---------------------+
 | 2023年07月20日 19:30:27 |
 | 2023年07月27日 22:24:15 |
 | 2023年07月27日 22:25:21 |
 | 2023年07月27日 22:29:21 |
 | 2023年07月27日 22:39:14 |
 | 2023年07月27日 22:40:20 |
 | 2023年07月27日 22:54:18 |
 | 2023年07月27日 22:55:23 |
 | 2023年07月27日 23:10:06 |
 */
merged_timestampAS(
SELECT
change_timestamp
FROM
running_reservation_slot_data
UNIONDISTINCT
SELECT
change_timestamp
FROM
running_capacity_commitment_slot_data
),
/*
 Change running reservation-slots and make sure we have one row when commitment changes.
 +---------------------+-------------------------+----------------+
 | change_timestamp | autoscale_current_slots | baseline_slots |
 +---------------------+-------------------------+----------------+
 | 2023年07月20日 19:30:27 | 0 | 0 |
 | 2023年07月27日 22:24:15 | 0 | 300 |
 | 2023年07月27日 22:25:21 | 180 | 300 |
 | 2023年07月27日 22:29:21 | 180 | 300 |
 | 2023年07月27日 22:39:14 | 100 | 300 |
 | 2023年07月27日 22:40:20 | 100 | 600 |
 | 2023年07月27日 22:54:18 | 220 | 600 |
 | 2023年07月27日 22:55:23 | 120 | 600 |
 | 2023年07月27日 23:10:06 | 120 | 600 |
 */
running_reservation_slot_data_with_merged_timestampAS(
SELECT
change_timestamp,
IFNULL(
autoscale_current_slots,
IFNULL(
LAST_VALUE(autoscale_current_slotsIGNORENULLS)OVER(ORDERBYchange_timestampASC),0))
ASautoscale_current_slots,
IFNULL(
baseline_slots,
IFNULL(LAST_VALUE(baseline_slotsIGNORENULLS)OVER(ORDERBYchange_timestampASC),0))
ASbaseline_slots
FROM
running_reservation_slot_data
RIGHTJOIN
merged_timestamp
USING(change_timestamp)
),
/*
 Join the above, so that we will know the number for baseline not covered by commitments.
 +---------------------+-----------------------+-------------------------+------------------------------------+
 | change_timestamp | next_change_timestamp | autoscale_current_slots | baseline_not_covered_by_commitment |
 +---------------------+-----------------------+-------------------------+------------------------------------+
 | 2023年07月20日 19:30:27 | 2023年07月27日 22:24:15 | 0 | 0 |
 | 2023年07月27日 22:24:15 | 2023年07月27日 22:25:21 | 0 | 200 |
 | 2023年07月27日 22:25:21 | 2023年07月27日 22:29:21 | 180 | 200 |
 | 2023年07月27日 22:29:21 | 2023年07月27日 22:39:14 | 180 | 100 |
 | 2023年07月27日 22:39:14 | 2023年07月27日 22:40:20 | 100 | 100 |
 | 2023年07月27日 22:40:20 | 2023年07月27日 22:54:18 | 100 | 400 |
 | 2023年07月27日 22:54:18 | 2023年07月27日 22:55:23 | 220 | 400 |
 | 2023年07月27日 22:55:23 | 2023年07月27日 23:10:06 | 120 | 400 |
 | 2023年07月27日 23:10:06 | 2023年07月31日 00:16:07 | 120 | 300 |
 */
scaled_slots_and_baseline_not_covered_by_commitmentsAS(
SELECT
r.change_timestamp,
IFNULL(LEAD(r.change_timestamp)OVER(ORDERBYr.change_timestampASC),CURRENT_TIMESTAMP())
ASnext_change_timestamp,
r.autoscale_current_slots,
IF(
r.baseline_slots-IFNULL(c.capacity_slot,0)>0,
r.baseline_slots-IFNULL(c.capacity_slot,0),
0)ASbaseline_not_covered_by_commitment
FROM
running_reservation_slot_data_with_merged_timestampr
LEFTJOIN
running_capacity_commitment_slot_data_with_next_changec
ON
r.change_timestamp>=c.change_timestamp
ANDr.change_timestamp<c.next_change_timestamp
),
/*
 The slot_seconds between each changes. For example:
 +---------------------+--------------------+
 | change_timestamp | slot_seconds |
 +---------------------+--------------+
 | 2023年07月20日 19:30:27 | 0 |
 | 2023年07月27日 22:24:15 | 13400 |
 | 2023年07月27日 22:25:21 | 91580 |
 | 2023年07月27日 22:29:21 | 166320 |
 | 2023年07月27日 22:39:14 | 13200 |
 | 2023年07月27日 22:40:20 | 419500 |
 | 2023年07月27日 22:54:18 | 40920 |
 | 2023年07月27日 22:55:23 | 459160 |
 | 2023年07月27日 23:10:06 | 11841480 |
 */
slot_seconds_dataAS(
SELECT
change_timestamp,
GetSlotSecondsBetweenChanges(
autoscale_current_slots+baseline_not_covered_by_commitment,
UNIX_MILLIS(change_timestamp),
UNIX_MILLIS(next_change_timestamp),
UNIX_MILLIS(start_time),
UNIX_MILLIS(end_time))ASslot_seconds
FROM
scaled_slots_and_baseline_not_covered_by_commitments
WHERE
change_timestamp<=end_timeANDnext_change_timestamp>start_time
)
/*
Final result for this example:
+--------------------+
| total_slot_seconds |
+--------------------+
| 13045560 |
*/
SELECT
SUM(slot_seconds)AStotal_slot_seconds
FROM
slot_seconds_data

SELECT
AVG(TIMESTAMP_DIFF(end_time,creation_time,MILLISECOND))asavg_latency_ms,
SUM(total_bytes_processed)astotal_bytes,
COUNT(*)asquery_numbers,
FROM
`PROJECT_ID.region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHEREcreation_time>=START_TIME
ANDcreation_time<END_TIME
AND(statement_type!="SCRIPT"ORstatement_typeISNULL)
ANDreservation_id=RESERVATION_ID

SELECT
AVG(TIMESTAMP_DIFF(end_time,creation_time,MILLISECOND))asavg_latency_ms,
SUM(total_bytes_processed)astotal_bytes,
COUNT(*)asquery_numbers,
FROM
`myproject.region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHEREcreation_time>='2024-06-25 00:00:00-07'
ANDcreation_time<'2024-06-30 00:00:00-07'
AND(statement_type!="SCRIPT"ORstatement_typeISNULL)
ANDreservation_id=reservationID