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
- Replace
sqlsrv-example/sqlsrv-example-01with your logical server name. - Uncomment the
TimeGeneratedline to limit the analysed period.