LLI Wiki — My Digital Knowledge Base

KQL: SQL dependency durations by performance bucket

Application Insights KQL query grouping SQL dependencies by day and duration bucket.

This KQL query analyses SQL dependency durations of an Application Insights resource, grouped by day and by performance bucket (PerformanceBucket), with readable average and total durations.

Query

Adjust the _ResourceId has '...' filter to target the desired environment; the optional DurationMs filter keeps only slow calls.

AppDependencies
| where DependencyType == 'SQL' and _ResourceId has 'appi-prd-myapp' // and DurationMs > 999 // choose the environment and, if needed, a minimum duration
| 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

Notes

Mastodon LLIMastodon SocialFosstodon