A join that used to be free is now the whole job
A job that joins a large fact to a small dimension ran comfortably for a year. Over the past two months its runtime has climbed sharply. Nothing in the code changed, and the fact table has grown only modestly.
What you would do first
Answer before revealing anything. The value of the exercise is entirely in committing to a diagnosis you can be wrong about.
- 1Read the query plan for a recent run and for an old one. A broadcast that has become a shuffle is visible in the plan and nowhere else.
- 2Plot the dimension's size over time against the runtime. The inflection points will coincide.
- 3Check the engine's broadcast threshold and how the dimension's size is being estimated, which may be stale statistics rather than actual growth.
- 4Look at per-task metrics to distinguish a large shuffle from a skewed one, because the remedies differ.
What is actually going on
The trap
The fix that looks right. Read it even if you got the answer — especially then.
Raise the broadcast threshold until the plan switches back. It works immediately, and every executor now holds a copy of a dimension that no longer fits comfortably — so the failure mode changes from a slow job to memory pressure and spilling, which is harder to diagnose and arrives without warning.