LLI Wiki — My Digital Knowledge Base

Collecting per-database metrics to drive elastic pool placement

What to measure, where to read it (DMVs, Azure Monitor, Log Analytics), how to store history, score a database and handle seasonality.

Moving a database from one elastic pool to another is easy. Knowing which database to move, and where, is the hard part, and the answer is only as good as the numbers behind it. This article is about the measurement side of my rebalancing work: what I collect per database, where it comes from, how I store it and how I turn it into a score. The move itself is covered in the next two articles.

Data flow

flowchart LR
    A["Azure Monitor metrics (per database and per pool)"] --> B["Log Analytics (AzureMetrics)"]
    C["Pool DMV in master"] --> D["Spot checks"]
    B --> E["Nightly job (KQL, one day at a time)"]
    E --> F["History table"]
    F --> G["Scoring procedure"]
    G --> H["Score table (per database and per day)"]
    H --> I["Move suggestions"]

What to collect

A pool is a shared budget, so a database matters through the share of the pool budget it consumes. These are the signals I keep:

Signal Why it matters Source metric
CPU The first limit hit on most pools cpu_percent, cpu_used (vCore)
Data IO Read pressure, shows scan-heavy workloads physical_data_read_percent
Log write Write-heavy batches saturate it quickly log_write_percent
Workers / sessions Many concurrent requests, even with low CPU workers_percent, sessions_percent
Storage Hard limit of the pool, cannot be bursted storage, storage_percent
Share of the pool Normalises everything to "what this database costs its neighbours" derived

Percent metrics of a database are relative to the per-database limit, which is the same for every database of a pool. That makes them comparable inside a pool, but not across pools of different sizes. So I always convert to a share of the pool: vCores used divided by pool vCores, or the pool percentage split proportionally to the database percentages.

Where to read it

Spot check of a pool from master:

SELECT TOP (12) end_time, elastic_pool_name,
       avg_cpu_percent, avg_data_io_percent, avg_log_write_percent,
       max_worker_percent, avg_storage_percent
FROM sys.elastic_pool_resource_stats
ORDER BY end_time DESC;

Nightly collection with KQL

Every night a job fetches yesterday, hour by hour, and writes it to a history table. I keep the maximum and the average per hour, because the average alone hides the peaks.

let day = startofday(ago(1d));
AzureMetrics
| where TimeGenerated >= day and TimeGenerated < day + 1d
| where ResourceProvider =~ "MICROSOFT.SQL"
| where MetricName in ("cpu_percent", "log_write_percent", "physical_data_read_percent", "workers_percent")
| extend rid = tolower(_ResourceId)
| where rid has "/databases/"
| extend Server = extract(@"servers/([^/]+)", 1, rid), Db = extract(@"databases/([^/]+)$", 1, rid)
| where Db != "master"
| summarize HourAvg = avg(Average), HourMax = max(Maximum) by bin(TimeGenerated, 1h), Server, Db, MetricName

With geo-replication, the same database name appears on two servers. I summarise per server first, then keep the busier copy per hour: that is the primary, and it is the one that costs capacity.

The history table

CREATE TABLE dbo.DbMetricHistory (
    MetricDate  date          NOT NULL,
    HourUtc     tinyint       NOT NULL,
    DbName      sysname       NOT NULL,
    PoolName    sysname       NULL,
    MetricName  varchar(40)   NOT NULL,
    HourAvg     decimal(9,3)  NULL,
    HourMax     decimal(9,3)  NULL,
    CONSTRAINT PK_DbMetricHistory PRIMARY KEY (MetricDate, HourUtc, DbName, MetricName)
);

Two design choices matter. The job is idempotent: it deletes the day it is about to write, so a re-run never duplicates. And it purges old rows (about two months is enough), because the table grows by thousands of rows per day.

Scoring a database

A score reduces many metrics to one comparable number. Mine is a weighted sum of normalised signals, each one bucketed from the daily percentile:

SELECT DbName, MetricDate,
       0.50 * cpu_p95 + 0.20 * io_p95 + 0.15 * log_p95 + 0.15 * workers_p95 AS Score
FROM (
    SELECT DbName, MetricDate,
           MAX(CASE WHEN MetricName='cpu_percent' THEN HourMax END)                 AS cpu_p95,
           MAX(CASE WHEN MetricName='physical_data_read_percent' THEN HourMax END)  AS io_p95,
           MAX(CASE WHEN MetricName='log_write_percent' THEN HourMax END)           AS log_p95,
           MAX(CASE WHEN MetricName='workers_percent' THEN HourMax END)             AS workers_p95
    FROM dbo.DbMetricHistory
    GROUP BY DbName, MetricDate
) t;

The weights are the part I revisit. Our pools saturated on CPU, yet CPU was under-weighted: the composite score was mostly IO and ranked pools poorly against reality. I checked it by comparing the rank of pools by score with their rank by real pool CPU (a Spearman correlation), changed the weights, recomputed all history and saw the correlation improve. A scoring grid is a hypothesis: test it against the pool-level truth.

Seasonality

One week of data is a trap. Month-end processing, nightly batches and weekly jobs create peaks that a short window misses. What helped:

Pitfalls

Mastodon LLIMastodon SocialFosstodon