Am I actually using the BigQuery slots I am paying for?
Reservation utilisation by hour — baseline paid for against slot-time consumed.
Free to run — INFORMATION_SCHEMA scans no billable bytesChecked 4 Aug 2026
JOBS and TABLE_STORAGE are region-scoped.
-- Utilisation against the reservations assigned to this admin project.
WITH usage AS (
SELECT
reservation_id,
TIMESTAMP_TRUNC(period_start, HOUR) AS hour,
SUM(period_slot_ms) / 3600000 AS avg_slots_used
FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT
WHERE period_start >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 14 DAY)
AND reservation_id IS NOT NULL
GROUP BY reservation_id, hour
)
SELECT
reservation_id,
COUNT(*) AS hours_observed,
ROUND(AVG(avg_slots_used), 1) AS mean_slots_used,
ROUND(APPROX_QUANTILES(avg_slots_used, 100)[OFFSET(50)], 1) AS median_slots_used,
ROUND(APPROX_QUANTILES(avg_slots_used, 100)[OFFSET(95)], 1) AS p95_slots_used,
ROUND(MAX(avg_slots_used), 1) AS peak_slots_used
FROM usage
GROUP BY reservation_id
ORDER BY mean_slots_used DESC;What it returns
- median_slots_used
- The typical hour. Compare this against your baseline.
- p95_slots_used
- The busy hour. Compare this against baseline + autoscale ceiling.
How to read it
- A median far below your baseline means you are paying for idle capacity. A p95 far above it means you are autoscaling constantly and a bigger commitment would be cheaper.
- This query only sees hours in which something ran. Idle hours are invisible here and still billed — subtract hours_observed from the window length to see how many.
- Utilisation of 100% is not the goal. A reservation pinned at its ceiling is queueing work.
Take it further
Run this once, or have it run every day.
Finitizer evaluates this class of question continuously against your live BigQuery estate, tracks how each number moves between runs, and turns findings into assigned tasks. Read-only and keyless.