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
- The numeric prefixes added to the buckets (
01.to11.) only serve to sort them by increasing duration. - The
_ResourceIdcolumn is reduced to the Application Insights resource name.