LLI Wiki — My Digital Knowledge Base

KQL for Azure SQL operations: CPU, waits, queries, errors, storage and geo-replication

A KQL toolbox for Azure SQL Database: CPU, waits, Query Store, errors, failed logins, storage limit wall and replication heatmap.

When you run Azure SQL Database at scale, the portal metrics are no longer enough: you want to compare dozens of databases, go back in time, and cross pool, server and region. Log Analytics and KQL do this very well. Here is the toolbox of queries I use, written generically.

Prerequisites

A diagnostic setting sends metrics and log categories to a Log Analytics workspace (Azure Diagnostics mode):

flowchart LR
    A["Servers, pools, databases"] --> B["Diagnostic settings"]
    B --> C["AzureMetrics table"]
    B --> D["AzureDiagnostics table"]
    C --> E["KQL queries"]
    D --> E
    E --> F["Grafana dashboards or Workbooks"]

Metrics land in AzureMetrics; logs (waits, Query Store, errors, audit) in AzureDiagnostics. In the latter, columns carry type suffixes (_s, _d) and only exist once an event has filled them: I use column_ifexists. To extract server, pool or database I parse a lower-cased _ResourceId.

CPU

AzureMetrics
| where TimeGenerated > ago(24h)
| where ResourceProvider =~ "MICROSOFT.SQL" and MetricName == "cpu_percent"
| extend rid = tolower(_ResourceId)
| where rid has "/elasticpools/"
| extend Pool = extract(@"elasticpools/([^/]+)", 1, rid)
| summarize CpuMax = max(Maximum), CpuAvg = avg(Average) by bin(TimeGenerated, 15m), Pool
| order by TimeGenerated asc

Replace /elasticpools/ with /databases/ for the per-database view. Always keep Maximum next to Average.

Wait statistics

AzureDiagnostics
| where TimeGenerated > ago(24h) and Category == "DatabaseWaitStatistics"
| extend wait = tostring(column_ifexists("wait_type_s", "")),
         ms = todouble(column_ifexists("delta_wait_time_ms_d", 0.0))
| summarize TotalMs = sum(ms) by wait
| top 10 by TotalMs desc

Waits tell you why it is slow: locks, page I/O, log. I look at them per pool and per database when CPU alone does not explain it.

Query performance

AzureDiagnostics
| where TimeGenerated > ago(24h) and Category == "QueryStoreRuntimeStatistics"
| extend db = tostring(column_ifexists("DatabaseName_s", "")),
         q = tolong(column_ifexists("query_id_d", 0)),
         dur = todouble(column_ifexists("duration_d", 0.0))
| summarize Runs = count(), AvgDur = avg(dur), MaxDur = max(dur) by db, q
| top 20 by MaxDur desc

The Query Store category must be enabled; the duration is in Query Store's unit. I start from this list to find a query_id, then analyse the plan in the database itself.

Errors: a dashboard

AzureDiagnostics
| where TimeGenerated > ago(7d) and Category =~ "Errors"
| extend pool = tostring(column_ifexists("ElasticPoolName_s", ""))
| summarize Errors = count() by bin(TimeGenerated, 1h), Severity, pool

Severity, pool and database as filters: you see at once whether an error spike belongs to one pool or one database.

Failed logins

AzureMetrics
| where TimeGenerated > ago(7d)
| where MetricName startswith "connection_failed" and Total > 0
| extend Server = extract(@"(?i)servers/([^/]+)", 1, _ResourceId),
         Db = extract(@"(?i)databases/([^/]+)", 1, _ResourceId)
| summarize Failed = sum(Total), First = min(TimeGenerated), Last = max(TimeGenerated) by Server, Db
| order by Failed desc

To know who fails, the SQLSecurityAuditEvents audit category with action_name_s == "DATABASE AUTHENTICATION FAILED" gives identity and source address when auditing is enabled.

Storage capacity and the limit wall

AzureMetrics
| where TimeGenerated > ago(1h) and MetricName == "storage_percent"
| extend rid = tolower(_ResourceId)
| summarize arg_max(TimeGenerated, Average) by rid
| extend Kind = iff(rid has "/elasticpools/", "pool", "database")
| where Average >= 80
| project Kind, Resource = extract(@"([^/]+)$", 1, rid), Pct = round(Average, 1)
| order by Pct desc

The "wall" is the list of everything above 80 % or 90 %, pools and databases together. To anticipate, add a slope: make-series over 14 days then series_fit_line gives growth per day and therefore the days left.

Geo-replication: health and heatmap

AzureMetrics
| where TimeGenerated > ago(24h) and MetricName == "replication_lag_seconds"
| extend Db = extract(@"(?i)databases/([^/]+)", 1, _ResourceId)
| summarize Lag = max(Maximum) by bin(TimeGenerated, 30m), Db

This "time × database × peak lag" result renders as a heatmap in Grafana: one row per database, one column per slice, colour shows the lag. Healthy databases stay cold; a warming one stands out at a glance.

Pitfalls

Mastodon LLIMastodon SocialFosstodon