Monitoring Azure SQL elastic pools: metrics, alerts and a daily report
CPU, workers, sessions and storage per pool and per database, alert thresholds and a daily check script that mails an HTML report.
An Azure SQL elastic pool shares CPU, workers, sessions and storage between all its databases. While all is well nobody thinks about it; the day one database eats the pool, all the others suffer. Here is how I monitor production pools: the signals that matter, the alerts, and a daily check that sends an HTML report.
Architecture
flowchart LR
A["Azure SQL pools and databases"] --> B["Azure Monitor (metrics)"]
B --> C["Log Analytics (AzureMetrics)"]
C --> D["Grafana dashboard"]
B --> E["Alert rules"]
B --> F["Daily check script"]
F --> G["HTML report by mail"]
E --> H["DBA team"]
Metrics flow to a Log Analytics workspace through a diagnostic setting. Three consumers: a dashboard to understand, alerts to be notified, a daily report so nothing is forgotten.
What to watch
| Signal | Metric | Why |
|---|---|---|
| CPU | cpu_percent |
Saturation of the shared compute |
| Workers | workers_percent |
Concurrent active requests; at 100 %, new requests fail |
| Sessions | sessions_percent |
Open connections (badly tuned application pools) |
| Storage | storage_percent |
The pool has a maximum size and you cannot exceed it |
| I/O | log_write_percent, physical_data_read_percent |
Bottlenecks often hidden behind a normal CPU |
Each metric exists at pool level (the shared limit) and at database level (its share, and its own cap if you set a per-database maximum). You need both: a pool at 60 % CPU can hide a database at 100 % of its cap.
The workers/sessions ratio is a useful indicator: many sessions but few workers is a connection problem; the opposite is real load.
What to alert on
- Pool CPU: warning around 80 % sustained, critical around 90 %, over a window of several minutes (never on a single data point).
- Workers: stricter, because saturation makes requests fail.
- Storage: 80 % and 90 % thresholds, with a trend, not only the level.
- Connection failures and high-severity errors.
I split alerts into groups (availability, compute, concurrency, capacity), one file per rule, deployed by pipeline.
The daily check
Alerts say "now". The morning report says "this week". The idea, simplified:
$pools = Get-AzResource -ResourceType 'Microsoft.Sql/servers/elasticPools'
$rows = foreach ($p in $pools) {
$m = Get-AzMetric -ResourceId $p.ResourceId `
-MetricName 'cpu_percent','workers_percent','sessions_percent','storage_percent' `
-AggregationType Maximum -TimeGrain 00:05:00 `
-StartTime (Get-Date).AddDays(-1) -EndTime (Get-Date) -WarningAction SilentlyContinue
$o = [ordered]@{ Pool = $p.Name }
foreach ($metric in $m) {
$o[$metric.Name.Value] = ($metric.Data.Maximum | Measure-Object -Maximum).Maximum
}
[pscustomobject]$o
}
$html = $rows | Sort-Object cpu_percent -Descending |
ConvertTo-Html -Fragment | Out-String
Send-MailMessage -From 'dba@contoso.com' -To 'dba@contoso.com' `
-Subject "Pools check $(Get-Date -Format yyyy-MM-dd)" -Body $html -BodyAsHtml `
-SmtpServer 'smtp.contoso.com'
For real use I colour cells against thresholds (green, orange, red), add the top databases of the busiest pool and the remaining storage in GB. The script is read-only, runs daily in a scheduled pipeline, and the report is also published as an artifact. Send-MailMessage is marked obsolete: use the mail module or service of your choice.
Lessons learned
- Maximum, not average. A 5-minute average hides worker spikes.
- Limits depend on the service tier (vCores, max size): read them per pool rather than assuming they are equal.
- A database drowned in a pool needs database-level rules, otherwise you only see the group average.
- Storage is the forgotten limit: you notice when a write fails. Watch growth, not just the percentage.
- A report nobody reads is useless: keep it short, sorted by severity, with a clear "all green" state.