Fabrikam Manufacturing: Capacity Saturation, Release Discipline, and a Failing 14 GB Refresh

Sequence matters: isolate, then resize

1. Move dev and test to a separate A SKU or a separate capacity. They consume CU, they are not business critical, and 380 workspaces almost certainly include abandoned experiments. Removing them often recovers the overage on its own.
2. Use the Capacity Metrics app to find the top items during 09:00-11:00 and separate interactive from background. If refreshes or subscriptions overlap the peak, move them; that is free capacity.
3. Enable the large semantic model storage format and set a per-dataset memory limit so the 14 GB finance model cannot evict smaller datasets and force repeated eviction and reload.
4. Only then resize.

When resizing is the answer

P1 is 8 backends and 25 GB memory per replica. Sustained 130% of the 30-minute CU limit means roughly 10.4 backends of demand; P2 doubles the headroom. If after removing dev/test and rebalancing schedules the peak still exceeds 100%, the workload genuinely needs P2 or autoscale.

Autoscale caveat

Autoscale adds one vCore per 24 hours and can stay on silently. Set a maximum and treat it as a month-end safety net, not a permanent substitute for a correctly sized SKU. Isolate month-end by placing finance on its own capacity if the budget allows.

Pipeline design

• Assign the dev, test and prod workspaces to a deployment pipeline. Use parameter rules so each stage points at its own SQL source, and deployment settings to override dataset parameters and data source rules at deploy time.
• Enable autobind so reports reconnect to the dataset when names differ between stages.
• Use selective deployment and compare stages before deploying, so only changed items move.

Stopping direct publishing

• Give developers Viewer or Build on prod, and make the release service principal the only Contributor. Permissions, not exhortation, change behaviour.
• Remove standing access to the prod workspace and grant time-bound access through a group for incidents only.

Zero-downtime rules

• Deploy backward-compatible schema changes only: add columns in one release, stop using old ones in the next, delete later. Renaming a column in place breaks live reports instantly.
• Keep measures and report names stable across stages.
• Put the model in source control using .pbip with TMDL and XMLA read/write for scripted deployments; a pipeline without version history still loses work.

Result

Deployment becomes a deliberate, auditable action rather than a side effect of fixing a number.

Fix the refresh itself

• Convert the fact tables to incremental refresh: archive three years, refresh the last ten days. Six full refreshes a day of 14 GB is roughly 84 GB of reads; incremental reduces that to a partition each time.
• Use the enhanced refresh API to refresh individual partitions in parallel and control commit mode, rather than one monolithic job that must finish inside the timeout.
• Verify query folding against SQL Server. A single non-folding step can pull the full table through the gateway and is a common cause of intermittent timeouts that correlate with data volume.
• Confirm the model fits: 14 GB compressed against 25 GB per replica on P1 leaves little room for refresh overhead. Memory pressure, not the timeout, may be the real failure.

Fix the schedule

• Stagger the 220 refreshes. All at 01:00 saturates the gateway cluster and the capacity's background pool. Spread them across the night and put finance first.
• Move any refresh that overlaps 09:00-11:00; that alone reduces the throttling users feel.

Fix the gateway

Two nodes is fine for availability but not for memory-heavy jobs. Confirm both nodes are healthy and that the finance source uses a dedicated gateway cluster so a large refresh cannot starve the other eight sources.