LLI Wiki — My Digital Knowledge Base

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

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

Mastodon LLIMastodon SocialFosstodon