本頁面由 Cloud Translation API 翻譯而成。

運算單元自動調度簡介

如果您將預留項目設為使用運算單元自動調度功能，系統會自動調度已分配的容量，以配合工作負載需求。BigQuery 會根據工作負載的增減情形，動態調整運算單元數量。只有 BigQuery 版本提供運算單元自動調度資源的保留項目。

使用自動調度資源預留項目

您不需要購買運算單元承諾，即可建立自動調整規模的預留項目。運算單元承諾使用合約可為持續使用的運算單元提供折扣價，但使用自動調度預留資源時，這類合約並非必要。如要建立自動調度資源預留項目，請為預留項目指派運算單元數量上限 (預留項目大小上限)。如要找出自動調度資源運算單元數量上限，請從預留項目大小上限中，扣除指派給預留項目的任何基準運算單元。

建立自動調度預訂時，請注意下列事項：

BigQuery 會近乎即時地擴充預留項目，直到達到執行工作所需的運算單元數量，或是達到預留項目可用的運算單元數量上限為止。運算單元一律會自動調度至 50 的倍數。
系統會根據實際用量向上調整，並將用量進位至最接近的 50 個時段增量。
自動調整的運算單元會按照相關版本的容量運算定價計費。系統會依據運算單元數量收費，而非實際使用的運算單元數量。即使導致 BigQuery 擴充的工作失敗，我們還是會收取這筆費用。因此，請勿使用工作資訊結構定義來比對帳單。請改為參閱「使用資訊結構定義監控自動調度」。
雖然配額數量一律會以 50 的倍數擴充，但單一步驟中擴充的配額數量可能超過 50 個。舉例來說，如果工作負載需要額外 450 個運算單元，BigQuery 可以嘗試一次擴充 450 個運算單元，以滿足容量需求。
當與預留項目相關聯的工作不再需要容量時，BigQuery 會縮減容量 (最少 1 分鐘)。

系統會保留自動調整的容量至少 60 秒。這段 60 秒的期間稱為縮減視窗。容量的任何新尖峰都會重設縮減窗口，並將整個容量層級視為新的授權。不過，如果自上次增加容量以來已過 60 秒以上，且需求量較少，系統就會減少容量，但不會重設縮減時間範圍，因此可連續減少容量，不會造成延遲。

舉例來說，如果初始工作負載容量擴充至 100 個時段，系統會保留尖峰容量至少 60 秒。如果在縮減時間區間內，工作負載擴展至 200 個新尖峰時段，則會開始新的 60 秒縮減時間區間。如果縮減期間沒有出現新的尖峰，工作負載會在 60 秒結束時開始縮減。

請參考以下詳細範例：在 12:00:00，初始容量會擴充至 100 個運算單元，且用量會持續一秒。該尖峰值會保留至少 60 秒，從 12:00:00 開始。60 秒過後 (12:01:01)，如果新的用量為 50 個時段，BigQuery 會縮減至 50 個時段。如果 12:01:02 的新用量為 0 個時段，BigQuery 會再次立即縮減至 0 個時段。縮減時間範圍結束後，BigQuery 可以連續多次縮減，不需要新的縮減時間範圍。

如要瞭解如何使用自動調度功能，請參閱「使用自動調度功能」。

搭配基準和自動調度運算單元使用預留項目

除了指定預留項目大小上限，您也可以選擇指定每個預留項目的基準運算單元數量。「基準數量」是指一律會分配給預留項目的運算單元最小數量，而且必定會產生這個數量的運算單元費用。只有在所有基準運算單元 (和閒置運算單元，如有) 都用完後，才會新增自動調度運算單元。您可以將一項預留項目的閒置基準運算單元，分享給需要運算能力的預留項目。

每隔幾分鐘，您就可以增加預留項目的基準運算單元數量。如要減少基準運算單元，如果最近變更了基準運算單元處理量，且基準運算單元超出承諾使用運算單元，則每小時只能減少一次。否則，您可以每隔幾分鐘減少基準廣告空間。

基準和自動調度資源的運算單元會根據您最近的工作負載提供容量。如果您預期工作負載量會大幅增加，且與近期工作負載量差異極大，建議您在活動前增加基準容量，而不是依賴自動調度資源功能來涵蓋工作負載容量。如果增加基準容量時發生問題，請等待 15 分鐘後再重試要求。

如果預留項目沒有基準運算單元，或未設定從其他預留項目借用閒置運算單元，BigQuery 會嘗試擴充。否則，必須先充分運用基準時段，才能進行擴充。

預留項目會依據下列優先順序使用及新增運算單元：

基準運算單元。
閒置運算單元共用功能 (如果已啟用)。預留項目只能共用以相同版本和區域建立的其他預留項目中的閒置基準或已承諾運算單元。
自動調度運算單元。

在下列範例中，廣告空間會從指定基準金額開始調整。etl 和 dashboard 預留項目的基準大小分別為 700 和 300 個運算單元。

不含承諾的自動調整資源範例。

在這個範例中，etl 預留項目可調度至 1300 個運算單元 (700 個基準運算單元加上 600 個自動調度運算單元)。如果 dashboard 預留項目未使用中，且 dashboard 預留項目沒有執行任何工作，etl 預留項目就能使用 dashboard 預留項目的 300 個運算單元，因此最多可使用 1,600 個運算單元。

dashboard 預留項目可擴充至 1100 個運算單元 (300 個基準運算單元加上 800 個自動調度運算單元)。如果 etl 預留項目完全閒置，dashboard 預留項目最多可擴充至 1800 個運算單元 (300 個基準運算單元 + 800 個自動調度資源運算單元 + etl 預留項目中的 700 個閒置運算單元)。

如果 etl 預留項目需要超過 700 個一律可用的基準運算單元，系統會依序嘗試使用下列方法新增運算單元：

700 個基準運算單元。
與 dashboard 預留項目中的 300 個基準運算單元共用閒置運算單元。只有以相同版本建立的預留項目，才能共用閒置的基準運算單元。
將 600 個額外運算單元擴充至預留項目大小上限。

使用運算單元承諾

以下範例說明如何使用容量承諾自動調整時段大小。

自動調度資源範例

與預留項目的基準運算單元類似，運算單元承諾使用合約可讓您分配固定數量的運算單元，供所有預留項目使用。與基準運算單元不同，承諾用量在約期內無法減少。運算單元承諾使用合約為選用項目，但如果長期需要基準運算單元，這類合約可節省費用。

在本範例中，您需要為容量承諾運算單元支付預先定義的費率。自動調度資源功能啟用後，預訂項目會處於擴充狀態，這時系統會按照自動調度資源費率，針對自動調度資源配額數量向您收費。自動調度費率的收費依據為調度的運算單元數量，而非使用的運算單元數量。

以下範例顯示預訂內容，其中基準時段數超過已承諾時段數。

基準運算單元數量超過承諾運算單元數量。

在本範例中，這兩個預留項目共有 1000 個基準運算單元，其中 500 個來自 etl 預留項目，另外 500 個來自 dashboard 預留項目。不過，承諾僅涵蓋 800 個時段。在這種情況下，超出配額的運算單元會按照即付即用 (PAYG) 費率計費。

可用的運算單元數量上限

如要計算預留項目可使用的運算單元數量上限，請將基準運算單元數量、自動調度運算單元數量上限，以及以相同版本建立且不屬於基準運算單元的任何承諾運算單元數量加總。上圖中的範例設定如下：

承諾使用 1000 個年度運算單元。這些運算單元會指派為 etl 預留項目和 dashboard 預留項目的基準運算單元。
指派給 etl 預留項目的 700 個基準運算單元。
指派給 dashboard 預留項目的 300 個基準運算單元。
將「etl」預訂的運算單元自動調度上限設為 600。
將「dashboard」預留項目的運算單元自動調度上限設為 800。

以 etl 預留項目為例，運算單元數量上限等於 etl 基準運算單元 (700 個) 加上 dashboard 基準運算單元 (300 個，前提是所有運算單元都處於閒置狀態)，再加上自動調度資源的運算單元數量上限 (600 個)。因此，在這個範例中，etl 預留項目可使用的運算單元數量上限為 1600 個。這個數字超過容量承諾中的數字。

在下列範例中，年約方案的運算單元數量超過指派的基準運算單元。

計算可用運算單元

在這個範例中，我們有：

承諾每年使用 1600 個運算單元。
預留項目大小上限為 1500 (包括 500 個自動調度運算單元)。
指派給 etl 預留項目的 1000 個基準運算單元。

預留項目可用的運算單元數量上限，等於基準運算單元數量 (1000) 加上未專用於基準運算單元的任何承諾閒置運算單元 (1600 個年約運算單元 - 1000 個基準運算單元 = 600)，再加上自動調度運算單元數量 (500)。因此，這個預留項目的最大潛在運算單元數為 2100 個。自動調度的運算單元是容量承諾以外的額外運算單元。

自動調度最佳做法

首次使用自動調度資源功能時，請根據過去和預期成效，將自動調度資源運算單元數量設為有意義的數字。預訂建立完成後，請主動監控失敗率、效能和帳單，並視需要調整自動調度資源的配額數量。
自動調度器縮減資源前，至少會等待 1 分鐘，因此請務必設定自動調度資源的上限，在效能和成本之間取得平衡。如果自動調度運算單元數量上限過高，且工作可以在幾秒內用完所有運算單元，您仍須支付一整分鐘的運算單元費用。如果將最大時段數調降為目前的一半，預訂的時段數就會減少，工作在這段時間內可使用更多 slot_seconds，進而減少浪費。如需判斷時段需求的相關說明，請參閱「監控工作效能」。如要瞭解判斷運算單元需求的替代方法，請參閱「查看版本運算單元建議」。
運算單元用量有時可能會超過基準運算單元加上調度運算單元的總和。如果運算單元用量超過基準值加上擴充運算單元，您無需支付相關費用。
對於需要長時間執行的大量工作負載 (例如有多筆並行查詢的工作負載)，自動配置器最能發揮效率。請避免一次傳送一個查詢，因為每個查詢都會擴充預留量，且至少會維持 1 分鐘。如果您持續傳送查詢，導致工作負載不斷增加，設定基準並購買承諾可提供持續的容量，且享有折扣價。
BigQuery 自動調度資源取決於運算能力可用性。BigQuery 會根據過往用量，盡量滿足客戶的容量需求。如要確保容量，您可以設定選用的運算單元基準，也就是保留項目中保證的運算單元數量。使用基準時，系統會立即提供配額，無論您是否使用，都必須支付費用。如要確保有足夠的容量來因應大型非自然需求 (例如高流量的節慶假日)，請提前幾週與 BigQuery 團隊聯絡。
系統一律會收取基準運算單元費用。如果容量承諾到期，您可能需要手動調整預留項目中的基準運算單元數量，以免產生不必要的費用。舉例來說，假設您有 1 年期承諾，其中包含 100 個運算單元，且預訂項目包含 100 個基準運算單元。承諾期限到期，且沒有續約方案。使用承諾到期後，您需要按照即付即用費率，支付 100 個基準運算單元的費用。

監控自動調度資源

使用管理資源圖表監控運算單元用量時，您可能會發現調度的運算單元數量遠多於運算單元用量，這是因為圖表會根據校正間隔，平滑處理運算單元用量。如要查看自動調度資源的用量，並取得更精確的詳細資料，請縮短時間範圍選項。這會自動將對齊週期更新為較小的增量。

在下列範例中，圖表顯示的縮放比例明顯高於工作負載需求。

校正週期設為一分鐘間隔，且縮放的運算單元數超過運算單元用量需求。

不過，如果縮短時間範圍選項，將對齊週期設為兩秒，您會發現自動調度器會根據工作負載需求進行調度，並顯示更準確的資料。如要調整時間範圍選項，請拖曳時間範圍選項的開始和結束範圍。如要顯示最準確的工作負載需求資料，請從「指標」清單中選取「p99」。

對齊週期設為兩秒間隔，且縮放的時段適合工作負載需求。

如要盡可能準確地查看自動調度資源用量，請使用 1 到 15 秒的對齊週期。

如要瞭解如何查看時段使用量，請參閱「查看管理資源圖表」。

使用資訊結構定義監控自動調度資源

您可以使用下列 SQL 指令碼，查看特定版本的計費時段秒數。您必須在建立預訂的專案中執行這些指令碼。第一個指令碼會顯示commitment_plan涵蓋的計費廣告時段秒數，第二個指令碼則會顯示承諾未涵蓋的計費廣告時段秒數。

您只需要設定三個變數的值，即可執行這些指令碼：

start_time
end_time
edition_to_check

使用這些指令碼時，請注意下列事項：

資料保留期限結束時，系統會從資訊結構定義檢視畫面中移除已刪除的預留項目和容量使用承諾。指定最近的時間範圍，其中不含已刪除的預訂和約定，確保結果正確。
由於四捨五入的小誤差，指令碼結果可能與帳單不完全一致。

下列指令碼會檢查特定版本的承諾使用合約涵蓋的時段用量。

展開即可查看指令碼，根據約定計算時段秒數。

DECLARE start_time,end_time TIMESTAMP;

DECLARE
  edition_to_check STRING;

/* 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. */

SET start_time = "2023-07-20 00:00:00-07";
SET end_time = "2023-07-28 00:00:00-07";
SET edition_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. */

CREATE TEMP FUNCTION
GetSlotSecondsBetweenChanges(
  slots FLOAT64,
  range_begin_timestamp_unix_millis FLOAT64,
  range_end_timestamp_unix_millis FLOAT64,
  script_start_timestamp_unix_millis FLOAT64,
  script_end_timestamp_unix_millis FLOAT64)
RETURNS INT64
LANGUAGE js
AS r"""
    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_plan AS (
    SELECT
      *,
      IFNULL(
        LEAD(commitment_plan)
          OVER (
            PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC
          ),
        commitment_plan)
        next_plan,
      IFNULL(
        LEAD(change_timestamp)
          OVER (
            PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC
          ),
        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_plan AS (
    SELECT
      next_plan_change_timestamp AS change_timestamp,
      project_id,
      project_number,
      capacity_commitment_id,
      commitment_plan,
      state,
      slot_count,
      'DELETE' AS action,
      commitment_start_time,
      commitment_end_time,
      failure_status,
      renewal_plan,
      user_email,
      edition,
      is_flat_rate,
    FROM commitments_with_next_plan
    WHERE commitment_plan <> next_plan
    UNION ALL
    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_data AS (
    SELECT
      change_timestamp,
      capacity_commitment_id,
      CASE
        WHEN action = "CREATE" OR action = "UPDATE"
          THEN
            IFNULL(
              IF(
                LAG(action)
                  OVER (
                    PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC, action ASC
                  )
                  IN UNNEST(['CREATE', 'UPDATE']),
                slot_count - LAG(slot_count)
                  OVER (
                    PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC, action ASC
                  ),
                slot_count),
              slot_count)
        ELSE
          IF(
            LAG(action)
              OVER (PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC, action ASC)
              IN UNNEST(['CREATE', 'UPDATE']),
            -1 * slot_count,
            0)
        END
        AS slot_count_delta,
      commitment_plan
    FROM
      capacity_changes_with_additional_deleted_event_for_changed_plan
    WHERE
      state = "ACTIVE"
      AND edition = edition_to_check
      AND change_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_data AS (
    SELECT
      change_timestamp,
      SUM(slot_count_delta)
        OVER (
          PARTITION BY commitment_plan
          ORDER BY change_timestamp
          RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )
        AS capacity_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_data AS (
    SELECT
      change_timestamp,
      GetSlotSecondsBetweenChanges(
        capacity_slot,
        UNIX_MILLIS(change_timestamp),
        UNIX_MILLIS(
          IFNULL(
            LEAD(change_timestamp)
              OVER (PARTITION BY commitment_plan ORDER BY change_timestamp ASC),
            CURRENT_TIMESTAMP())),
        UNIX_MILLIS(start_time),
        UNIX_MILLIS(end_time)) AS slot_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) AS total_slot_seconds
FROM
  slot_seconds_data
GROUP BY
  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
DECLARE start_time, end_time TIMESTAMP;

DECLARE
  edition_to_check STRING;

/* 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. */

SET start_time = "2023-07-20 00:00:00-07";
SET end_time = "2023-07-28 00:00:00-07";
SET edition_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. */

CREATE TEMP FUNCTION GetSlotSecondsBetweenChanges(
  slots FLOAT64,
  range_begin_timestamp_unix_millis FLOAT64,
  range_end_timestamp_unix_millis FLOAT64,
  script_start_timestamp_unix_millis FLOAT64,
  script_end_timestamp_unix_millis FLOAT64)
RETURNS INT64
LANGUAGE js
AS r"""
    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_data AS (
    SELECT
      change_timestamp,
      reservation_name,
      CASE action
        WHEN "CREATE" THEN autoscale.current_slots
        WHEN "UPDATE"
          THEN
            IFNULL(
              autoscale.current_slots - LAG(autoscale.current_slots)
                OVER (
                  PARTITION BY project_id, reservation_name
                  ORDER BY change_timestamp ASC, action ASC
                ),
              IFNULL(
                autoscale.current_slots,
                IFNULL(
                  -1 * LAG(autoscale.current_slots)
                    OVER (
                      PARTITION BY project_id, reservation_name
                      ORDER BY change_timestamp ASC, action ASC
                    ),
                  0)))
        WHEN "DELETE"
          THEN
            IF(
              LAG(action)
                OVER (
                  PARTITION BY project_id, reservation_name
                  ORDER BY change_timestamp ASC, action ASC
                )
                IN UNNEST(['CREATE', 'UPDATE']),
              -1 * autoscale.current_slots,
              0)
        END
        AS autoscale_current_slot_delta,
      CASE action
        WHEN "CREATE" THEN slot_capacity
        WHEN "UPDATE"
          THEN
            IFNULL(
              slot_capacity - LAG(slot_capacity)
                OVER (
                  PARTITION BY project_id, reservation_name
                  ORDER BY change_timestamp ASC, action ASC
                ),
              IFNULL(
                slot_capacity,
                IFNULL(
                  -1 * LAG(slot_capacity)
                    OVER (
                      PARTITION BY project_id, reservation_name
                      ORDER BY change_timestamp ASC, action ASC
                    ),
                  0)))
        WHEN "DELETE"
          THEN
            IF(
              LAG(action)
                OVER (
                  PARTITION BY project_id, reservation_name
                  ORDER BY change_timestamp ASC, action ASC
                )
                IN UNNEST(['CREATE', 'UPDATE']),
              -1 * slot_capacity,
              0)
        END
        AS baseline_slot_delta,
    FROM
      `region-us.INFORMATION_SCHEMA.RESERVATION_CHANGES`
    WHERE
      edition = edition_to_check
      AND change_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_data AS (
    SELECT
      change_timestamp,
      SUM(autoscale_current_slot_delta)
        OVER (ORDER BY change_timestamp RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS autoscale_current_slots,
      SUM(baseline_slot_delta)
        OVER (ORDER BY change_timestamp RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS baseline_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_data AS (
    SELECT
      change_timestamp,
      capacity_commitment_id,
      CASE
        WHEN action = "CREATE" OR action = "UPDATE"
          THEN
            IFNULL(
              IF(
                LAG(action)
                  OVER (
                    PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC, action ASC
                  )
                  IN UNNEST(['CREATE', 'UPDATE']),
                slot_count - LAG(slot_count)
                  OVER (
                    PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC, action ASC
                  ),
                slot_count),
              slot_count)
        ELSE
          IF(
            LAG(action)
              OVER (PARTITION BY capacity_commitment_id ORDER BY change_timestamp ASC, action ASC)
              IN UNNEST(['CREATE', 'UPDATE']),
            -1 * slot_count,
            0)
        END
        AS slot_count_delta
    FROM
      `region-us.INFORMATION_SCHEMA.CAPACITY_COMMITMENT_CHANGES_BY_PROJECT`
    WHERE
      state = "ACTIVE"
      AND edition = edition_to_check
      AND change_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_data AS (
    SELECT
      change_timestamp,
      SUM(slot_count_delta)
        OVER (ORDER BY change_timestamp RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS capacity_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_change AS (
    SELECT
      change_timestamp,
      IFNULL(LEAD(change_timestamp) OVER (ORDER BY change_timestamp ASC), CURRENT_TIMESTAMP())
        AS next_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_timestamp AS (
    SELECT
      change_timestamp
    FROM
      running_reservation_slot_data
    UNION DISTINCT
    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_timestamp AS (
    SELECT
      change_timestamp,
      IFNULL(
        autoscale_current_slots,
        IFNULL(
          LAST_VALUE(autoscale_current_slots IGNORE NULLS) OVER (ORDER BY change_timestamp ASC), 0))
        AS autoscale_current_slots,
      IFNULL(
        baseline_slots,
        IFNULL(LAST_VALUE(baseline_slots IGNORE NULLS) OVER (ORDER BY change_timestamp ASC), 0))
        AS baseline_slots
    FROM
      running_reservation_slot_data
    RIGHT JOIN
      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_commitments AS (
    SELECT
      r.change_timestamp,
      IFNULL(LEAD(r.change_timestamp) OVER (ORDER BY r.change_timestamp ASC), CURRENT_TIMESTAMP())
        AS next_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) AS baseline_not_covered_by_commitment
    FROM
      running_reservation_slot_data_with_merged_timestamp r
    LEFT JOIN
      running_capacity_commitment_slot_data_with_next_change c
      ON
        r.change_timestamp >= c.change_timestamp
        AND r.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_data AS (
    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)) AS slot_seconds
    FROM
      scaled_slots_and_baseline_not_covered_by_commitments
    WHERE
      change_timestamp <= end_time AND next_change_timestamp > start_time
  )

/*
Final result for this example:
+--------------------+
| total_slot_seconds |
+--------------------+
|           13045560 |
*/
SELECT
  SUM(slot_seconds) AS total_slot_seconds
FROM
  slot_seconds_data

監控工作成效

您可能需要調整自動調度資源 max_slots，避免產生較高的費用。下列查詢會提供工作效能的相關資訊，方便您為工作負載選擇適當的自動調度資源運算單元數量。

下列查詢提供預訂過去工作成效的詳細資料：

SELECT
    AVG(TIMESTAMP_DIFF(end_time, creation_time, MILLISECOND)) as avg_latency_ms,
    SUM(total_bytes_processed) as total_bytes,
    COUNT(*) as query_numbers,
FROM
    `PROJECT_ID.region-REGION_NAME`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE creation_time >= START_TIME
    AND creation_time < END_TIME
    AND (statement_type != "SCRIPT" OR statement_type IS NULL)
    AND reservation_id = RESERVATION_ID

更改下列內容：

PROJECT_ID：專案 ID
REGION_NAME：專案的區域
START_TIME：您要開始查看資料的建立時間
END_TIME：您要停止查看資料的建立時間
RESERVATION_ID：預訂 ID

以下範例會取得五天內的工作詳細資料：

SELECT
    AVG(TIMESTAMP_DIFF(end_time, creation_time, MILLISECOND)) as avg_latency_ms,
    SUM(total_bytes_processed) as total_bytes,
    COUNT(*) as query_numbers,
FROM
    `myproject.region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE creation_time >= '2024-06-25 00:00:00-07'
    AND creation_time < '2024-06-30 00:00:00-07'
    AND (statement_type != "SCRIPT" OR statement_type IS NULL)
    AND reservation_id = reservationID

配額

預留項目大小總和不得超過運算單元配額。

如需配額相關資訊，請參閱「配額與限制」。

後續步驟

如要進一步瞭解 BigQuery 版本，請參閱「BigQuery 簡介」。
如要進一步瞭解運算單元，請參閱「瞭解運算單元」一文。
如要進一步瞭解預留項目，請參閱「預留項目簡介」。