LLI Wiki — My Digital Knowledge Base

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:

  1. 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.
  2. 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.
  3. 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.

Avoiding ping-pong

Ping-pong is a database moved to a pool, then moved back by the next run. What prevents it:

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 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

Mastodon LLIMastodon SocialFosstodon