KQL: Azure SQL wait statistics in Log Analytics
KQL queries to analyse wait stats of an Azure SQL database or elastic pool in 5-minute bins.
These KQL queries analyse Azure SQL Database wait statistics from the Log Analytics AzureDiagnostics table. They aggregate wait duration and wait count by wait type in 5-minute bins over a given time range.
Prerequisite: the database diagnostics (wait statistics category) must be sent to a Log Analytics workspace.
For one database
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL" and DatabaseName_s == 'AppDb' and wait_type_s != '' //and ElasticPoolName_s == 'sqlelp-01-sqlsrv-prd-01'
| where TimeGenerated >= datetime("2023-03-20 10:30") and TimeGenerated <= datetime("2023-03-20 12:10")
| parse _ResourceId with * "/microsoft.sql/servers/" LogicalServerName "/databases/" DatabaseName
| summarize total_duration_ms_in_timespan = sum(delta_wait_time_ms_d), Total_count_in_timespan = sum(delta_waiting_tasks_count_d) by wait_type_s, bin(TimeGenerated, 5m)
| order by TimeGenerated asc, wait_type_s
For an elastic pool
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL" and ElasticPoolName_s == 'sqlelp-01-sqlsrv-prd-01' and wait_type_s != ''
| where TimeGenerated >= datetime("2023-03-20 10:30") and TimeGenerated <= datetime("2023-03-20 12:10")
| parse _ResourceId with * "/microsoft.sql/servers/" LogicalServerName "/databases/" DatabaseName
| summarize total_duration_ms_in_timespan = sum(delta_wait_time_ms_d), Total_count_in_timespan = sum(delta_waiting_tasks_count_d) by wait_type_s, bin(TimeGenerated, 5m)
| order by TimeGenerated asc, wait_type_s
Notes
- Adapt the database name, pool name and time window (
TimeGenerated). - The
wait_type_s != ''filter excludes rows that are not wait statistics. - Database, server and pool names are generic examples.