Nightly ETL Window Missed After Audience Growth
A retailer runs a nightly workflow that builds a 12M-recipient delivery. The build consists of a Query activity joining the recipient table (40M rows) to a 90M-row transaction table, followed by a Deduplication and an Enrichment. Historically the build finished in 3h10m inside a 4h maintenance window. Over six weeks the recipient base grew 18% and average transactions per recipient doubled. The build now takes 5h45m and the delivery launches late. The database server shows 70% average CPU but peaks at 95% during two hash-join steps, disk I/O queue depth spikes during those same steps, and tempdb growth is the largest single consumer of disk. No workflow step has changed. The DBA reports that the transaction table has no index on the recipient foreign key and statistics were last updated eight months ago.
1. Diagnose the root cause and propose the highest-leverage changes, ordered by effort vs. impact.
Read the worked answer
Diagnosis. The symptom pattern (CPU pegged only during two steps, concurrent I/O queue spikes, dominant tempdb growth) points to spill-to-disk hash joins and stale cardinality estimates, not a general capacity problem - average CPU is only 70%. The join key recipient_id on the transaction table has no index, so the optimizer likely chooses a hash join that cannot be probed efficiently, and 8-month-old statistics mean the estimated row counts feeding that join are wrong. The 18% recipient growth alone would not cause a 80% runtime increase; the doubling of transactions per recipient changed the join's cardinality by roughly 2.4x, which is exactly the kind of change that flips a plan from in-memory to spilling.
Order of attack (cheap and reversible first):
- Update statistics on the transaction table and recipient table. This is minutes of work, zero schema risk, and frequently restores the prior plan.
- Add an index on the transaction foreign key (with included columns for whatever the Query selects). This is the structural fix; without it the join will always be fragile as volume grows.
- Re-measure. If still slow, inspect the actual execution plan for the two hot steps and confirm whether tempdb spill persists.
- Only then consider hardware or partitioning - do not buy capacity to mask a missing index.
Verification. Capture baseline runtime and tempdb peak before and after each change so the impact of statistics vs. index is attributable.
2. The business wants the delivery to go out on time tonight, before any schema change can be approved. What is a defensible interim mitigation?
Read the worked answer
Split the problem rather than the outcome. The safest interim move is to decouple the expensive build from the send: run the Query/Dedup/Enrichment earlier in the evening as a standalone workflow that writes to a dedicated staging schema, then have the delivery read from that pre-built audience. This preserves the send time without touching production indexes.
Additional interim levers, in order of preference:
- Filter earlier. Push the transaction date range and any business segmentation into the Query so fewer rows enter the join. Reducing input cardinality is the most reliable way to stop a spill.
- Reduce columns selected. Narrow projections lower memory grant requirements and tempdb pressure.
- Stagger or throttle the two hot steps if they can run in separate workflows without a dependency conflict.
What I would not do tonight: change indexes during a live send window, or increase parallelism blindly - more parallelism on a spilling hash join often increases tempdb contention.
Governance note: document the interim staging workflow as temporary with an owner and a review date, otherwise it silently becomes permanent technical debt.