BigQuery idle reservation capacity: slots billed, not used
A BigQuery reservation bills its baseline slots all the time and its autoscaled slots until they scale down, used or not.
When less than half of what is billed does work, the baseline or commitment may be larger than the work.
Run read-only queries in your own console and paste the results. The report is worked out in your browser, with no access to your account.
What the check looks at
Baseline and autoscaled slot-seconds by hour from INFORMATION_SCHEMA.RESERVATIONS_TIMELINE, against the reserved slot time your jobs used.
Why it costs money
- Baseline slots bill every second of the month.
- Autoscaled slots are added in steps of 50 and bill for at least 60 seconds, so short bursts bill more than they use.
- A capacity commitment bills until its term ends, whatever you run.
How the tool measures it
To investigate. The report shows what this costs now and where to look. It claims no saving, because the results cannot prove one.
Utilization is reserved slot-hours used divided by baseline and autoscaled slot-hours billed. Below 50%, the report shows the unused part at pay-as-you-go prices. It claims no saving.
When the report raises it. Optional query 4 is provided and less than half of the billed capacity was used.
In the sample report
The tool's sample does not raise this check, because its made-up account does not meet the condition above.
What to do
- 1Compare the baseline and any commitment with your busiest hours.
- 2Check which projects are assigned to the reservation; at project scope, the reservation may serve others too.
How to tell it worked. Read slots_assigned and period_autoscale_slot_seconds in RESERVATIONS_TIMELINE for two weeks after any change.
Trade-offs. A lower baseline can slow the first queries of a burst while autoscaling reacts.
The read-only queries behind it
The same SQL the tool gives you. Each reads usage views and changes nothing.
Query 4 (optional): reservation capacity
-- Finitizer BigQuery Cost Check, optional query 4: reservation capacity by hour.
-- Only for reservations. Run it in the reservation administration project: this
-- view is per administration project, whatever scope you picked above.
-- Baseline slots bill all the time, used or idle. Autoscale slot-seconds are the
-- slots Google charged for. Returns no query text and no reservation names.
-- With no reservation here, it returns one row with edition NONE, so the result
-- can still be saved and the report can say so.
-- Window: the last 30 complete UTC days. Run it unchanged.
WITH capacity AS (
SELECT
TIMESTAMP_TRUNC(period_start, HOUR) AS hour,
IFNULL(edition, 'UNKNOWN') AS edition,
SUM(IFNULL(slots_assigned, 0)) * 60 AS baseline_slot_seconds,
SUM(IFNULL(period_autoscale_slot_seconds, 0)) AS autoscale_slot_seconds,
COUNT(DISTINCT reservation_id) AS reservations
FROM `region-us`.INFORMATION_SCHEMA.RESERVATIONS_TIMELINE
WHERE period_start >= TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 30 DAY)
AND period_start < TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)
AND is_creation_region IS NOT FALSE
GROUP BY hour, edition
),
result AS (
SELECT * FROM capacity
UNION ALL
SELECT TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 30 DAY), 'NONE', 0, 0, 0
FROM UNNEST([1])
WHERE NOT EXISTS (SELECT 1 FROM capacity)
)
SELECT
*,
COUNT(*) OVER () AS result_rows,
TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 30 DAY) AS window_start,
TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY) AS window_end,
'us' AS location,
'project' AS scope,
'2026-11-07' AS query_version
FROM result
ORDER BY 1, 2;Query 1: compute, by hour and minute
-- Finitizer BigQuery Cost Check, query 1 of 3: compute, hourly totals only.
-- Returns no query text, no table names and no user identities.
-- Window: the last 30 complete UTC days.
-- Slot time is booked to the second it was used (JOBS_TIMELINE). Bytes and job
-- counts are booked to the hour each job was created (JOBS). Script parents are
-- left out because their child jobs are counted. Run it unchanged.
WITH seconds AS (
SELECT
period_start,
SUM(period_slot_ms) AS slot_ms,
SUM(IF(reservation_id IS NOT NULL, period_slot_ms, 0)) AS reserved_slot_ms,
SUM(IF(reservation_id IS NOT NULL AND edition = 'STANDARD', period_slot_ms, 0))
AS reserved_standard_slot_ms,
SUM(IF(reservation_id IS NOT NULL AND edition = 'ENTERPRISE', period_slot_ms, 0))
AS reserved_enterprise_slot_ms,
SUM(IF(reservation_id IS NOT NULL AND edition = 'ENTERPRISE_PLUS', period_slot_ms, 0))
AS reserved_enterprise_plus_slot_ms,
SUM(IF(reservation_id IS NULL, period_slot_ms, 0)) AS on_demand_slot_ms
FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
WHERE job_creation_time >= TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 31 DAY)
AND job_creation_time < TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)
AND period_start >= TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 30 DAY)
AND period_start < TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)
AND job_type = 'QUERY'
AND (statement_type != 'SCRIPT' OR statement_type IS NULL)
GROUP BY period_start
),
minutes AS (
-- The slots autoscaling would bill for on-demand work: rounded up to 50, and
-- held for the whole minute at that minute's busiest second, or per second
-- with fluid scaling. A model of the bill, not the bill.
SELECT
TIMESTAMP_TRUNC(period_start, MINUTE) AS minute,
MAX(CEIL(on_demand_slot_ms / 1000 / 50) * 50) AS minute_slots,
SUM(CEIL(on_demand_slot_ms / 1000 / 50) * 50) AS fluid_slot_seconds,
MAX(on_demand_slot_ms / 1000) AS peak_slots
FROM seconds
GROUP BY minute
),
on_demand_shape AS (
SELECT
TIMESTAMP_TRUNC(minute, HOUR) AS hour,
SUM(minute_slots) * 60000 AS on_demand_autoscale_slot_ms,
SUM(fluid_slot_seconds) * 1000 AS on_demand_fluid_slot_ms,
MAX(peak_slots) AS peak_on_demand_slots
FROM minutes
GROUP BY hour
),
slot_time AS (
SELECT
TIMESTAMP_TRUNC(period_start, HOUR) AS hour,
SUM(slot_ms) AS slot_ms,
SUM(reserved_slot_ms) AS reserved_slot_ms,
SUM(reserved_standard_slot_ms) AS reserved_standard_slot_ms,
SUM(reserved_enterprise_slot_ms) AS reserved_enterprise_slot_ms,
SUM(reserved_enterprise_plus_slot_ms) AS reserved_enterprise_plus_slot_ms
FROM seconds
GROUP BY hour
),
job_totals AS (
SELECT
TIMESTAMP_TRUNC(creation_time, HOUR) AS hour,
COUNT(*) AS jobs,
COUNTIF(cache_hit) AS cached_jobs,
COUNTIF(error_result IS NOT NULL) AS failed_jobs,
COUNTIF(state != 'DONE') AS unfinished_jobs,
COUNTIF(state = 'DONE' AND error_result IS NULL AND reservation_id IS NULL
AND total_bytes_billed IS NULL AND cache_hit IS NOT TRUE) AS jobs_missing_bytes,
COUNTIF(statement_type = 'CREATE_MODEL') AS model_jobs,
SUM(total_bytes_billed) AS bytes_billed,
SUM(total_bytes_processed) AS bytes_processed,
COUNTIF(reservation_id IS NOT NULL) AS reserved_jobs,
SUM(IF(reservation_id IS NULL, total_bytes_billed, 0)) AS on_demand_bytes_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time >= TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 30 DAY)
AND creation_time < TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY)
AND job_type = 'QUERY'
AND (statement_type != 'SCRIPT' OR statement_type IS NULL)
GROUP BY hour
)
SELECT
COALESCE(j.hour, s.hour) AS hour,
IFNULL(j.jobs, 0) AS jobs,
IFNULL(j.cached_jobs, 0) AS cached_jobs,
IFNULL(j.failed_jobs, 0) AS failed_jobs,
IFNULL(j.unfinished_jobs, 0) AS unfinished_jobs,
IFNULL(j.jobs_missing_bytes, 0) AS jobs_missing_bytes,
IFNULL(j.model_jobs, 0) AS model_jobs,
IFNULL(j.bytes_billed, 0) AS bytes_billed,
IFNULL(j.bytes_processed, 0) AS bytes_processed,
IFNULL(j.on_demand_bytes_billed, 0) AS on_demand_bytes_billed,
IFNULL(s.slot_ms, 0) AS slot_ms,
IFNULL(j.reserved_jobs, 0) AS reserved_jobs,
IFNULL(s.reserved_slot_ms, 0) AS reserved_slot_ms,
IFNULL(s.reserved_standard_slot_ms, 0) AS reserved_standard_slot_ms,
IFNULL(s.reserved_enterprise_slot_ms, 0) AS reserved_enterprise_slot_ms,
IFNULL(s.reserved_enterprise_plus_slot_ms, 0) AS reserved_enterprise_plus_slot_ms,
CAST(IFNULL(o.on_demand_autoscale_slot_ms, 0) AS INT64) AS on_demand_autoscale_slot_ms,
CAST(IFNULL(o.on_demand_fluid_slot_ms, 0) AS INT64) AS on_demand_fluid_slot_ms,
ROUND(IFNULL(o.peak_on_demand_slots, 0), 1) AS peak_on_demand_slots,
COUNT(*) OVER () AS result_rows,
TIMESTAMP_SUB(TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY), INTERVAL 30 DAY) AS window_start,
TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), DAY) AS window_end,
'us' AS location,
'project' AS scope,
'2026-11-07' AS query_version
FROM job_totals j
FULL OUTER JOIN slot_time s ON s.hour = j.hour
LEFT JOIN on_demand_shape o ON o.hour = COALESCE(j.hour, s.hour)
ORDER BY 1;Other checks in the report
- BigQuery on-demand vs Editions: which costs less for your queriesPriced
- BigQuery repeated scans: queries that run every dayTo investigate
- BigQuery large scans: queries reading 100 GiB or moreTo investigate
- BigQuery physical storage billing: when it costs lessPriced
- BigQuery logical storage billing: when to move backPriced
- BigQuery time travel window: the cost on physical billingTo investigate
- BigQuery storage billing model: when the report cannot tellTo investigate
What BigQuery charges in each location: BigQuery pricing in 2026.
Run this check every day, not once.
Finitizer reads your BigQuery job history and storage directly, raises these findings as they appear, and tracks each fix on the bill.
See how Finitizer works