Requêtes KQL pour superviser Azure SQL Database
Requêtes Log Analytics (AzureMetrics, AzureDiagnostics, AzureActivity) pour suivre les métriques, deadlocks, connexions échouées et opérations longues d'Azure SQL.
Collection de requêtes KQL pour Log Analytics afin de superviser Azure SQL Database : métriques de ressources, deadlocks, connexions échouées, authentifications échouées et opérations du plan de contrôle longues ou limitées. Elles supposent que les paramètres de diagnostic Azure SQL (métriques, journaux d'audit) et le journal d'activité sont envoyés vers un espace de travail Log Analytics. Voir Monitor Azure SQL Database with Azure Monitor.
Découvrir les données disponibles
Fournisseurs de ressources qui envoient des métriques :
AzureMetrics
| summarize count() by ResourceProvider
Ressources SQL et noms de métriques présents :
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
CPU, écriture du journal et lecture des données par heure et par base
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
Le filtre has sélectionne le serveur logique et l'expression régulière sélectionne les bases selon un modèle de nommage ; à adapter à votre convention.
Deadlocks
Événements bruts :
AzureMetrics
| where ResourceProvider == 'MICROSOFT.SQL' and ResourceGroup == 'my-resource-group' and MetricName == 'deadlock'
Par jour :
AzureMetrics
| where ResourceProvider == 'MICROSOFT.SQL' and ResourceGroup == 'my-resource-group' and MetricName == 'deadlock'
| summarize EventCount = count() by bin(TimeGenerated, 1d)
| render timechart
Par jour et par base :
AzureMetrics
| where ResourceProvider == 'MICROSOFT.SQL' and ResourceGroup == 'my-resource-group' and MetricName == 'deadlock'
| summarize EventCount = count() by bin(TimeGenerated, 1d), Resource
| render timechart
Connexions échouées (erreurs utilisateur)
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
Uniquement les jours et bases au-dessus d'un seuil :
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
Authentifications échouées par IP cliente
Lister les ressources qui produisent des données d'audit :
AzureDiagnostics
| where ResourceProvider == "MICROSOFT.SQL"
| summarize count() by Resource
Compter les authentifications échouées par IP cliente pour une base donnée (et 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
Journal d'activité : opérations du plan de contrôle
Opérations sur une base :
AzureActivity
| where ResourceProvider == "Microsoft SQL"
| where tostring(Properties) contains "sqldb-prd-app-db00001"
| project TimeGenerated, OperationNameValue, ActivityStatusValue, Properties
| order by TimeGenerated desc
Idem, limité à une journée :
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
Opérations de plus d'une heure (regroupées par identifiant de corrélation) :
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
Tous les événements de ces opérations longues :
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
Limitations (throttling) et quotas
AzureActivity
| where tostring(Properties) has_any ("throttle","limit","quota","TooManyRequests")
| project TimeGenerated, OperationNameValue, ActivityStatusValue, Properties
| order by TimeGenerated desc
Opérations en échec uniquement :
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