LLI Wiki — My Digital Knowledge Base

KQL queries to monitor Azure SQL Database

Log Analytics queries (AzureMetrics, AzureDiagnostics, AzureActivity) to track Azure SQL metrics, deadlocks, failed connections and long-running operations.

A collection of KQL queries for Log Analytics to monitor Azure SQL Database: resource metrics, deadlocks, failed connections, failed authentications and long-running or throttled control-plane operations. They require the Azure SQL diagnostic settings (metrics, audit logs) and the activity log to be sent to a Log Analytics workspace. See Monitor Azure SQL Database with Azure Monitor.

Discover what is available

Which resource providers send metrics:

AzureMetrics
| summarize count() by ResourceProvider

Which SQL resources and which metric names are present:

AzureMetrics
| where ResourceProvider == 'MICROSOFT.SQL' and ResourceGroup == 'my-resource-group'
| summarize count() by ResourceId
AzureMetrics
| where ResourceProvider == 'MICROSOFT.SQL' and ResourceGroup == 'my-resource-group'
| summarize count() by MetricName

Hourly CPU, log write and data read per database

AzureMetrics
//| where TimeGenerated between ( datetime('$MetricDate') .. endofday(datetime('$MetricDate')))
| where ResourceProvider == 'MICROSOFT.SQL' and ResourceGroup == 'my-resource-group' and MetricName in ('cpu_percent','log_write_percent','physical_data_read_percent')
| where ResourceId has 'SQLSRV-PRD-01' and Resource matches regex 'SQLDB-PRD-APP-DB[0-9][0-9][0-9][0-9][0-9]$'
| summarize MetricMax = max(Maximum), MetricAvg = avg(Average) by Resource, MetricName, bin(TimeGenerated, 1h)
| extend MetricDate = format_datetime(TimeGenerated, 'yyyy-MM-dd'), MetricTime = format_datetime(TimeGenerated, 'HH:mm')
| where MetricMax > 0 and MetricAvg > 0

The has filter selects the logical server and the regex selects databases by naming pattern; adapt both to your naming convention.

Deadlocks

Raw events:

AzureMetrics
| where ResourceProvider == 'MICROSOFT.SQL' and ResourceGroup == 'my-resource-group' and MetricName == 'deadlock'

Per day:

AzureMetrics
| where ResourceProvider == 'MICROSOFT.SQL' and ResourceGroup == 'my-resource-group' and MetricName == 'deadlock'
| summarize EventCount = count() by bin(TimeGenerated, 1d)
| render timechart

Per day and per database:

AzureMetrics
| where ResourceProvider == 'MICROSOFT.SQL' and ResourceGroup == 'my-resource-group' and MetricName == 'deadlock'
| summarize EventCount = count() by bin(TimeGenerated, 1d), Resource
| render timechart

Failed connections (user errors)

AzureMetrics
| where ResourceProvider == 'MICROSOFT.SQL' and ResourceGroup == 'my-resource-group' and MetricName == 'connection_failed_user_error'
| summarize EventCount = count() by bin(TimeGenerated, 1d), Resource
| render timechart

Only the days and databases above a threshold:

AzureMetrics
| where ResourceProvider == 'MICROSOFT.SQL' and ResourceGroup == 'my-resource-group' and MetricName == 'connection_failed_user_error'
| summarize EventCount = count() by bin(TimeGenerated, 1d), Resource
| where EventCount > 10
| order by EventCount desc
| render timechart

Failed authentications by client IP

List the resources that produce audit data:

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
| summarize count() by Resource

Count failed authentications by client IP for a given database (and master):

AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
| where Resource contains "MYDATABASE" or Resource contains "master"
| where action_name_s == "DATABASE AUTHENTICATION FAILED"
| summarize count() by client_ip_s
// | order by TimeGenerated desc

Activity log: control-plane operations

Operations on one database:

AzureActivity
| where ResourceProvider == "Microsoft SQL"
| where tostring(Properties) contains "sqldb-prd-app-db00001"
| project TimeGenerated, OperationNameValue, ActivityStatusValue, Properties
| order by TimeGenerated desc

Same, restricted to one day:

AzureActivity
| where ResourceProvider == "Microsoft SQL"
| where TimeGenerated >= datetime(2026-02-20)
| where TimeGenerated < datetime(2026-02-21)
| where tostring(Properties) contains "sqldb-prd-app-db00001"
| project TimeGenerated, OperationNameValue, ActivityStatusValue, Properties
| order by TimeGenerated desc

Operations that lasted more than one hour (grouped by correlation id):

AzureActivity
| where ResourceProvider == "Microsoft SQL"
| summarize min(TimeGenerated), max(TimeGenerated) by OperationNameValue, CorrelationId
| extend Duration = max_TimeGenerated - min_TimeGenerated
| where Duration > 1h
| order by Duration desc

All events belonging to those long operations:

let LongOps =
AzureActivity
| where ResourceProvider == "Microsoft SQL"
| summarize
    MinTime = min(TimeGenerated),
    MaxTime = max(TimeGenerated)
    by OperationNameValue, CorrelationId
| extend Duration = MaxTime - MinTime
| where Duration > 1h;

AzureActivity
| where ResourceProvider == "Microsoft SQL"
| join kind=inner LongOps on CorrelationId
| project TimeGenerated,
          OperationNameValue,
          ActivityStatusValue,
          CorrelationId,
          Duration,
          Properties
| order by Duration desc, TimeGenerated asc

Throttling and quota errors

AzureActivity
| where tostring(Properties) has_any ("throttle","limit","quota","TooManyRequests")
| project TimeGenerated, OperationNameValue, ActivityStatusValue, Properties
| order by TimeGenerated desc

Failed operations only:

AzureActivity
| where ResourceProvider == "Microsoft SQL"
| where ActivityStatusValue == "Failed"
| where tostring(Properties) has_any ("quota","limit","TooManyRequests","429")
| project TimeGenerated, OperationNameValue, Properties
| order by TimeGenerated desc
Mastodon LLIMastodon SocialFosstodon