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
- Dynamic views:
sys.dm_db_resource_stats(inside each database, 15-second samples, roughly the last hour) andsys.elastic_pool_resource_stats(inmaster, pool level, short retention). Perfect for a spot check during an incident, useless for history and impractical to loop over thousands of databases. - Azure Monitor metrics sent to Log Analytics: one query returns every database of the estate, with a long retention. This is my main source.
- Resource Graph: the current membership (which database is in which pool), because a metric row only carries the resource id.
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:
- keep hourly maxima, so a recurring night window stays visible;
- aggregate over at least a full cycle (a week, and ideally a month-end);
- look at the hour-of-day profile of a pool before declaring it idle.
Pitfalls
- Metrics are not available instantly: Log Analytics ingestion can lag, so collect yesterday, never today.
- Retention is limited: a re-fetch of a day that has already left the workspace deletes it for good. Check before reprocessing.
- A database that changed resource group during the period is split across two groups: query all of them.
- Without tie-breakers, rankings reorder between refreshes: always sort on a second key.