LLI Wiki — My Digital Knowledge Base

KQL : durées des appels SQL par tranche de performance

Requête KQL Application Insights regroupant les dépendances SQL par jour et tranche de durée.

Cette requête KQL analyse les durées des dépendances SQL d'une ressource Application Insights, regroupées par jour et par tranche de performance (PerformanceBucket), avec des durées moyenne et totale lisibles.

Requête

Adapter le filtre _ResourceId has '...' pour cibler l'environnement voulu ; le filtre optionnel DurationMs permet de ne garder que les appels lents.

AppDependencies
| where DependencyType == 'SQL' and _ResourceId has 'appi-prd-myapp' // and DurationMs > 999 // choisir l'environnement et, si besoin, une durée minimale
| summarize CountValue = count(), TotalDuration = sum(DurationMs), AvgDuration = avg(DurationMs) by bin(TimeGenerated, 1d), PerformanceBucket, _ResourceId
| extend PerformanceBucket = case (
PerformanceBucket == '>=5min', strcat('11. ' , PerformanceBucket),
PerformanceBucket == '2min-5min', strcat('10. ' , PerformanceBucket),
PerformanceBucket == '1min-2min', strcat('09. ' , PerformanceBucket),
PerformanceBucket == '30sec-1min', strcat('08. ' , PerformanceBucket),
PerformanceBucket == '15sec-30sec', strcat('07. ' , PerformanceBucket),
PerformanceBucket == '7sec-15sec', strcat('06. ' , PerformanceBucket),
PerformanceBucket == '3sec-7sec', strcat('05. ' , PerformanceBucket),
PerformanceBucket == '1sec-3sec', strcat('04. ' , PerformanceBucket),
PerformanceBucket == '500ms-1sec', strcat('03. ' , PerformanceBucket),
PerformanceBucket == '250ms-500ms', strcat('02. ' , PerformanceBucket),
PerformanceBucket == '<250ms', strcat('01. ' , PerformanceBucket),
PerformanceBucket)
//readable average duration
| extend AvgDurationDescr = case (
    AvgDuration < 1000, strcat( round(AvgDuration,1) ,'ms'),
    AvgDuration >= 1000 and AvgDuration < 60000, strcat(round(AvgDuration / 1000 ,1) , 'sec'),
    AvgDuration >= 60000, strcat(round(AvgDuration / 1000 / 60 ,1) , 'min'),
    strcat(AvgDuration, ''))
//readable total duration
| extend TotalDurationDescr = case (
    TotalDuration < 1000, strcat( round(TotalDuration,1) ,'ms'),
    TotalDuration >= 1000 and TotalDuration < 60000, strcat(round(TotalDuration / 1000 ,1) , 'sec'),
    TotalDuration >= 60000 and TotalDuration < 3600000, strcat(round(TotalDuration / 1000 / 60 ,1) , 'min'),
    TotalDuration >= 3600000 and TotalDuration < 86400000, strcat(round(TotalDuration / 1000 / 60 / 60 ,1) , 'hr'),
    TotalDuration >= 86400000, strcat(round(TotalDuration / 1000 / 60 / 60 / 24 ,1) , 'day'),
    strcat(TotalDuration, ''))
//readable source (application insight resource name)
| extend _ResourceId = tostring(split(_ResourceId, "/")[-1])
| order by _ResourceId, TimeGenerated, _ResourceId

Remarques

Mastodon LLIMastodon SocialFosstodon