LLI Wiki — My Digital Knowledge Base

KQL queries to monitor Azure SQL (workers, storage, errors, I/O)

Log Analytics queries on AzureMetrics and AzureDiagnostics to track workers, storage, errors and PAGEIOLATCH waits.

When Azure SQL database and elastic pool metrics and diagnostics are sent to a Log Analytics workspace, the AzureMetrics and AzureDiagnostics tables let you track worker saturation, storage, errors and I/O waits. This is a collection of KQL queries for that purpose.

Discover the available data

// Table columns
AzureMetrics
| getschema

// Available metrics
AzureMetrics
| summarize count() by MetricName

// Available diagnostic categories
AzureDiagnostics
| summarize count() by Category

Workers percentage (thread limit)

Average per 30-minute bin over the last 30 days, per resource. Works for an elastic pool as well as for a logical server.

AzureMetrics
| where MetricName == "workers_percent"
| where TimeGenerated >= ago(30d) and TimeGenerated <= ago(1d)
| summarize AvgWorkersPercent = avg(Average) by Resource, bin(TimeGenerated, 30m)
| project TimeGenerated, Resource, AvgWorkersPercent
| render timechart

Worker peaks, sorted by maximum:

AzureMetrics
| where MetricName == "workers_percent"
| project TimeGenerated, ResourceId, Resource, Average, Maximum
| order by Maximum desc

Azure portal: workers percentage of the databases in an elastic pool

Elastic pool metrics

AzureMetrics
| where ResourceId contains "elasticPools"
| where MetricName in ("cpu_percent", "storage_percent", "io_percent", "log_write_percent")
| project TimeGenerated, Resource, ResourceGroup, MetricName, Average, Maximum
| order by TimeGenerated desc

Storage percentage

AzureMetrics
| where MetricName == "storage_percent"
| project TimeGenerated, ResourceId, Resource, Average, Maximum
| order by TimeGenerated desc

Allocated data storage for a database (in GB)

AzureMetrics
| where Resource contains 'sqldb-example-01'
| where MetricName contains 'allocated_data_storage'
| summarize avg(Total) by bin(TimeGenerated, 1h)
| extend AvgAllocatedDataStorage_GB = avg_Total / (1024 * 1024 * 1024)
| project TimeGenerated, AvgAllocatedDataStorage_GB
| render timechart

Errors

Error count per resource group:

AzureDiagnostics
| where Category == 'Errors'
| summarize count() by ResourceGroup
| order by count_ desc

Error details for one resource group, excluding "read-only" messages:

AzureDiagnostics
| where Category == 'Errors'
| where ResourceGroup == 'rg-example'
| where Message !contains 'read-only'

PAGEIOLATCH waits per hour

Total pageiolatch wait time per database and per hour for a given day:

AzureDiagnostics
| where TimeGenerated between (datetime(2025-01-15 00:00:00) .. endofday(datetime(2025-01-15 00:00:00)))
| where ResourceProvider == 'MICROSOFT.SQL' and wait_type_s has 'pageiolatch'
| where Resource matches regex 'SQLDB-EXAMPLE-DB[0-9][0-9][0-9][0-9][0-9]$'
| summarize total_duration_s = sum(delta_wait_time_ms_d) / 1000 by Resource, bin(TimeGenerated, 1h)
| where total_duration_s >= 0.1
| extend MetricDate = format_datetime(TimeGenerated, 'yyyy-MM-dd'),
         MetricTime = format_datetime(TimeGenerated, 'HH:mm'),
         MetricName = 'pageiolatch_waits'

Notes

Mastodon LLIMastodon SocialFosstodon