Rebalancing a large Azure SQL estate: presenting results, tolerances, avoiding ping-pong and lessons learned
Before/after views, choosing tolerances, avoiding ping-pong moves, move windows, what went wrong and the role of human review.
Collecting metrics and writing a move script is the easy half. The other half is deciding what "balanced" means, showing it convincingly, and not making things worse. These are the lessons from rebalancing a large estate of elastic pools, including the mistakes.
The loop
flowchart LR
A["Scores per database"] --> B["Suggestions with a tolerance band"]
B --> C["Saved plan"]
C --> D["Human review"]
D -->|"cancel or adjust"| C
D --> E["Moves in a quiet window"]
E --> F["Inventory refresh"]
F --> G["Before / after report"]
G --> A
Showing before and after
A rebalancing is only credible if people can see it. Three views did the job:
- Overview per pool: total score before and after, average, upper band, database count and allocated storage against their limits. One row per pool, sorted by score. This is the table the plan is judged on.
- Top N databases per pool: the five or ten heaviest databases with their share of the pool. It explains why a pool is hot, usually one or two tenants, and it shows which database the plan will pick.
- Heatmap of hours: pools (or databases) in rows, hours of the day in columns, the worst value per cell. A recurring night window shows up as a coloured stripe.
For the heatmap I avoid fancy panels at large scale: with thousands of rows a timeline draws sub-pixel lines. A plain table, with the 24 hour columns written out explicitly and one colour rule, scrolls and stays readable:
AzureMetrics
| where TimeGenerated > ago(14d)
| where ResourceProvider =~ "MICROSOFT.SQL" and MetricName == "cpu_percent"
| where _ResourceId has "/elasticpools/"
| extend Pool = extract(@"elasticpools/([^/]+)$", 1, tolower(_ResourceId)), H = hourofday(TimeGenerated)
| summarize h00 = maxif(Maximum, H == 0), h01 = maxif(Maximum, H == 1), h02 = maxif(Maximum, H == 2),
h03 = maxif(Maximum, H == 3), h04 = maxif(Maximum, H == 4), h05 = maxif(Maximum, H == 5)
// ... write every hour out, up to h23
by Pool
| order by Pool asc
I write the columns by hand rather than using a pivot operator: a pivot only emits columns that have data, so one quiet hour shifts the layout. Collapsing two weeks onto 24 columns is intentional: each cell becomes the worst value at that hour across all days, which exposes recurring windows.
Choosing tolerances
The reference is the average score of the production pools, and the tolerance is the band around it: upper = average * (1 + tolerance). Only pools above the band are sources, and a target is filled up to the band, never above.
- A tight band (a few percent) produces many moves for little gain and invites oscillation.
- A wide band leaves hot pools hot.
- I started at 10 % and raised the tolerance rather than touching the scoring grid when a plan was too long. Changing the grid invalidates the history and every open plan.
Avoiding ping-pong
Ping-pong is a database moved to a pool, then moved back by the next run. What prevents it:
- Fill targets only up to the band, so a target never becomes a source after receiving.
- Check the effect before the move: do not take a database out of the source if that pushes the source below the average. If none fits, take the smallest one a target can absorb.
- Skip recently moved databases and those with a planned move still open.
- One plan at a time: save a new plan only when the previous one is done or cancelled and the inventory has been refreshed.
- Give every pool a cap on the number of moves per run.
Scheduling
Pool moves are online but not free: they consume IO and cause a short reconnect. I run pool changes in quiet daytime hours and group changes in the evening, never close to a month-end close or a heavy nightly window.
What went wrong
- The pool that looked idle. A pool with a low daily average was picked as a target. Its hourly profile showed a heavy nightly peak, and adding databases pushed it into saturation every night. Lesson: always look at the hourly maximum and at the hour-of-day profile before accepting a "cold" target.
- Storage limits. A target had CPU room but not storage room. The plan now requires allocated space after the move to stay a safe margin under the limit, and checks the maximum number of databases.
- Inventory lag. The inventory that tells the plan where each database lives is refreshed once a night. A second plan generated the same day still saw moved databases in their old pool and emptied the same source twice. Lesson: wait for the refresh before saving a new plan.
- Scoring grid mismatch. The composite score ranked pools differently from their real CPU. Fixed by weighting CPU more and validating the ranking against pool-level CPU.
- A re-fetch that erased data. Reprocessing days that had already left the workspace deleted them for good. Always check retention first.
The human review
The plan is saved in a table and nothing moves until I have read it. Review caught more than any automation: a database with a known one-off job that made its history unrepresentative, a tenant that must not leave a specific pool, a move that was technically right but planned the day before a deadline. Cancelling a line is one UPDATE, and the open row keeps that database out of the next suggestions. The script does the boring part; a person owns the decision.
Lessons learned
- Show the overview, the top N and the hourly heatmap together.
- Tolerance and grid are different knobs: turn the first one first.
- Prevent ping-pong structurally, not with hope.
- Look at the peak hours of a "cold" pool.
- One plan at a time, after the inventory refresh.
- Keep a human in the loop.