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
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
- Database names, resource group names and the regular expression are generic examples to adapt.
- The tables and columns available depend on the diagnostic settings enabled on the resources.