LLI Wiki — My Digital Knowledge Base

KQL: resource governor waits in Azure SQL Database

KQL queries on AzureDiagnostics to track governor and log rate waits per server and elastic pool.

In Azure SQL Database, "governor" waits show that the engine is throttling a resource (notably transaction log rate) to stay within the service tier limits. When diagnostics are sent to Log Analytics, the AzureDiagnostics table lets you track them with KQL.

The relevant wait types are: RESOURCE_GOVERNOR_IDLE, LOG_RATE_GOVERNOR, POOL_LOG_RATE_GOVERNOR and INSTANCE_LOG_RATE_GOVERNOR. See the Microsoft documentation on transaction log rate governance.

Governor waits per logical server

Wait count and cumulated duration per wait type and per day, for one or more logical servers:

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL" and wait_type_s has 'GOVERNOR' and LogicalServerName_s contains "sqlsrv-example"
//| where TimeGenerated >= datetime("2024-01-01 00:00") and TimeGenerated <= datetime("2024-01-01 23:59")
| parse _ResourceId with * "/microsoft.sql/servers/" LogicalServerName "/databases/" DatabaseName
| summarize Total_count_in_timespan = sum(delta_waiting_tasks_count_d), total_duration_ms_in_timespan =
sum(delta_wait_time_ms_d) by LogicalServerName, wait_type_s, bin(TimeGenerated, 1d)
| order by wait_type_s, TimeGenerated

Overview per elastic pool

Same principle, filtering on RESOURCE_GOVERNOR and grouping by elastic pool:

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL" and wait_type_s has 'RESOURCE_GOVERNOR' and LogicalServerName_s == "sqlsrv-example-01"
//| where TimeGenerated >= datetime("2024-01-01 00:00") and TimeGenerated <= datetime("2024-01-01 23:59")
| parse _ResourceId with * "/microsoft.sql/servers/" LogicalServerName "/databases/" DatabaseName
| summarize Total_count_in_timespan = sum(delta_waiting_tasks_count_d), total_duration_ms_in_timespan =
sum(delta_wait_time_ms_d), timef = max(TimeGenerated) by ElasticPoolName_s, wait_type_s, bin(TimeGenerated, 1d)
| order by ElasticPoolName_s asc, wait_type_s, TimeGenerated

Notes

Mastodon LLIMastodon SocialFosstodon