The Busy Hour

Mara runs the seller-cohort query most mornings — 38 lines over fct_sales and dim_seller, unedited since June. On Tuesday she ran it at 08:41 while waiting for the standup (8 s) and again at 10:06 for the trading deck (4 min 05 s). She spent the rest of the morning looking for what she had broken. She had broken nothing.

The same query was then run 481 times across one simulated day — every two minutes from 05:00 to 21:00, each run replaying the day from that second with the query inserted:

For 304 of 481 runs the query returns in its minimum 4 s. That is why nobody could reproduce the problem: every engineer who sat down to check, checked at a moment when the answer was four seconds.

A slot is “a virtual compute unit used by BigQuery to execute SQL queries”, and “you cannot manually change the number of slots used”. A query is therefore two numbers that have nothing to do with the clock: the work it needs (slot-seconds) and the most slots it can hold at once. Mara’s query needs 400 slot-seconds and can use up to 200 slots — so four seconds is its floor.

The allocation rule, verbatim from Google Cloud’s slots documentation:

“BigQuery enforces the equal sharing of slots among projects with running queries within a reservation, and then within jobs of a given project.”

Two levels. Buddy & Selly runs every workload in one project (bs-prod, because that is where billing was set up in 2023), so the first level does nothing and the second does everything. At 08:41 her query was one of 2 on the reservation and was given 53.79 slots; at 10:06 it was one of 48 and was given 2.08 slots. 400 slot-seconds at 2.08 slots is four minutes.

A query’s work is a property of the query. A query’s duration is a property of the company.

The bill went from $56 597 a year to $47 304 a year — a saving of $9 293 (16.4%). It was filed as a finance ticket. The median 10:06 reading went from 2 s to 4 min 04 s, with no overlap: slowest pre-move day 2 s, fastest post-move day 4 min 02 s.

• Mean slot utilisation over the day: 35.83% — reads as “we are over-provisioned”.
• Utilisation inside the busy hour: 100%% — reads as “nothing is being wasted”.
• Time at full occupancy: 7h 38m of the 24.

A saturated reservation and a well-used one are the same picture. Worse, utilisation is structurally unable to recommend growth — more capacity always lowers it:

Of the free options: staggering dashboard tiles five seconds apart moves the 10:06 run from 4 min 05 s to 4 min 03 s (the contention is a two-hour overload, not a five-second burst). Capping concurrency at 8 makes it 15 min 51 s — nearly four times worse — because dilution becomes queueing and a four-second query has no advantage in a FIFO line. Splitting into four projects improves the 10:06 run to 2 min 23 s but makes the median interactive job worse, 3 min 28 s → 6 min 49 s, because the dashboard tiles now share a third of the reservation between 30 of them. A fairness knob redistributes pain; it does not remove it.

At 10:06, with her query admitted as the 48th:

Across the day, dashboard tiles are 744 of 1 117 jobs (66.6%) and 17.9% of the work. Under per-job fair sharing, a tool that submits many small queries takes a share proportional to how many queries it submits — not to the work it needs, its value, or its share of the bill. A dashboard with fourteen tiles is fourteen claims on the pool, and opening it is an act of capacity allocation.

The move to a reservation saved $9 293 a year. The smallest capacity that fixes the mornings is 150 slots — $23 652 more than today, $70 956 a year in total, which is $14 359 a year more than the on-demand bill they left. The trade was never “cheaper”; it was “cheaper if you are willing to queue”, and the queue was not priced.

Waiting costs 30.0 hours a day of analyst wall-clock (plus 28.0 hours of people watching tiles load). Inverted: 100 → 150 slots costs €21 900 a year and returns 6 968 analyst-hours a year, so it pays for itself if an analyst’s hour is worth more than €3.14.

A reservation can have a baseline of zero and autoscale on demand (“Slots always autoscale to a multiple of 50”; “billed per second with a one-minute minimum duration”). Modelled with both rules and no reaction lag:

• p95 for the query: 2 s — its floor, the same as five times the fixed capacity.
• Cost: $30 656 a year — $16 648 less than today’s fixed reservation and $25 941 less than the on-demand bill they left.
• The price: the one-minute minimum bills 1 555.3 slot-hours against 859.8 slot-hours of work — 80.9% of billing overhead.

Caveat stated plainly: a real autoscaler reacts after demand appears and Google does not publish that latency, so this is a floor, not a promise.

with per_second as (
  select period_start,
         count(distinct job_id)        as jobs_running,
         sum(period_slot_ms) / 1000.0  as slots_used
  from `region-eu`.INFORMATION_SCHEMA.JOBS_TIMELINE
  where reservation_id = 'bs-eu'
    and period_start >= timestamp_sub(current_timestamp(), interval 1 day)
  group by period_start
)
select extract(hour from period_start)                    as hour,
       sum(slots_used) / 3600.0 / 100.0                   as utilisation,
       median(slots_used / jobs_running)                  as median_slots_per_job,
       100.0 * countif(round(slots_used / jobs_running, 6) < 10)
             / count(*)                                   as pct_of_hour_below_10
from per_second
group by hour order by hour;

Utilisation reaches 100% in hours 1, 2, 3, 4, 9, 10 — night and morning — and cannot separate them. Median circuits per running job can: at night eight dbt models share the pool, in the morning the same saturation is cut 47 ways.

Scoped to 07:00–19:00, the share of seconds in which a job holds fewer than ten slots is 0.129% before the move and 42.02% after — 325×. Its limit, stated: the same measure reads 90.7% across the night shift, when nobody minds. The scope is the decision.

1. “What is our grade of service?” Telephone engineers have specified one since 1917. If the answer is a shrug, that is the finding (and it is Lesson 25’s gap).
2. “How many jobs are on the reservation at 09:30, and how many slots does each get?” Not utilisation.
3. “Which projects are assigned to the reservation?” If the answer is “one”, the first half of the fairness rule is switched off.
4. “What did we give up when we bought the reservation, and did we write it down?” A pricing change is a performance change.
5. “Is our baseline the right shape, or just the right size?” A flat reservation buys a flat day; our day is not flat.

1. Run the query above over yesterday for your own reservation.
2. Find your busy hour — the hour with the lowest median_slots_per_job, not the highest utilisation.
3. Take a query your team runs often; get total_slot_ms from INFORMATION_SCHEMA.JOBS_BY_PROJECT, divide by 1000 for its work in slot-seconds, and divide that by your busy hour’s median slots per job. That is what it costs someone to ask the question at 09:30.
4. Divide the same work by your full reservation size — that is what it costs at 06:00. The ratio is your company’s answer to this lesson.
5. Multiply the difference by how often that query runs, and compare with fifty slots a year at your edition’s price.

Event / constant (11) / drift (12) / unwatched (13) / reversible (14) / unreproduced (15) / bundled (16) / transient (17) / extremal (18) / referential (19) / plural (20) / remote (21) / absent (22) / permitted (23) / detached (24) / unsettled (26) — and now the contended.

It is the first shape that is not a property of any pipeline. Every job that Tuesday was correct, well written, correctly sized and successful. The failure lives in the interleaving — which jobs happened to overlap — and the interleaving is stored nowhere, belongs to no team, and appears in no job’s own record. The unit of failure is a neighbour: you cannot see it from inside the thing that failed, cannot reproduce it on demand, and cannot fix it by changing the thing that failed. It generalises to any shared Kubernetes node, connection pool, consumer group, rate limit or build farm: wherever a resource is divided by a rule, the rule’s denominator is a design decision somebody has usually made by accident.

It also explains Lesson 24. Under contention the dashboard tiles’ p95 goes from 1 min 21 s to 9 min 49 s; the cheapest response available to a BI team is to stop querying the warehouse and cache an extract — which is exactly how one Monday meeting ended up with three different answers. Contention is where detached copies are born.

A query’s work is fixed; its duration is divided. The divisor is the number of jobs running beside it, which belongs to nobody. Utilisation cannot see it and can only ever argue for buying less. And the shape of the day, not its total, is what you are buying capacity for.

One deterministic generator — no random seed, no wall clock, no hand-typed number. It builds a day of 1 117 jobs on a 100-slot reservation and runs a max-min fair-share scheduler one second at a time, dividing capacity among projects then among jobs. For each of the 481 probe minutes the day is replayed from that second with the query inserted, and the query’s work is asserted to be 400 slot-seconds in every run and on all 90 days of history.

The published SQL was re-run in DuckDB over the generator’s own per-second timeline (758 588 rows): 98 checks, 0 failed. Two real bugs came out of it — cast(x / 3600 as int) rounds in DuckDB rather than truncating, and a float sum is order-dependent, so the “fewer than ten” comparison has to be rounded or SQL and Python disagree about exactly ten.

The generator was extracted back out of the rendered page, run in a clean directory and diffed key by key: 18 top-level keys / 4 644 scalar values, 0 differences, byte-identical to source.

Honestly soft: the workload is synthetic (its shape is the realistic part, and every ratio is scale-free, but the seconds are not yours); the autoscaling model has no reaction lag, so that row is a floor; the fair-share model is max-min over stated parallelism, which is the documented behaviour rather than a reimplementation of Google’s scheduler; and the JOBS_TIMELINE schema page renders its column table client-side and could not be read from the sandbox, so confirm your own period granularity before comparing numbers.

A planned claim died on contact with the arithmetic: the draft argued that splitting workloads into separate projects was the free fix. Measured, it moves the 10:06 run from 4 min 05 s to 2 min 23 s and makes the median interactive job worse — because Mara’s contention is with her own colleagues inside her own project.

Sources. Google Cloud: Slots, Query queues, Workload management, Pricing.