LLI Wiki — My Digital Knowledge Base

KQL: LCK_M_S lock waits per Azure SQL database

KQL query on AzureDiagnostics to track shared lock waits per database, per minute.

This KQL query on the AzureDiagnostics table of a Log Analytics workspace shows LCK_M_S (shared lock) waits per database for Azure SQL logical servers, aggregated per minute.

Query

// Scope: Azure SQL logical server
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL" and LogicalServerName_s contains "sqlsrv-prd-app" and LogicalServerName_s !has '-neu' and wait_type_s == 'LCK_M_S'
//| where TimeGenerated >= datetime("2023-02-08 00:00") and TimeGenerated <= datetime("2023-02-08 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 DatabaseName_s, wait_type_s, bin(TimeGenerated, 1m)
| order by wait_type_s, TimeGenerated

Notes

Mastodon LLIMastodon SocialFosstodon