Application Insights: long SQL dependencies executed repeatedly
KQL query listing SQL calls longer than one second that ran at least 10 times in the last hour.
This query reads the AppDependencies table and lists the SQL calls that took longer than 999 ms during the last hour. The calls are grouped by statement and target, and only the groups that ran at least 10 times are kept, so one-off slow calls are ignored.
//long queries in the last hour on production that were executed at least 10 times
AppDependencies
| where TimeGenerated >= now(-1h)
| where DependencyType == 'SQL' and DurationMs > 999 and _ResourceId has 'appi-example'// to define for which environment you run the query and on query duration
| summarize CountValue = count(), TotalDuration = sum(DurationMs), AvgDuration = avg(DurationMs) by Data, _ResourceId, Target
| where CountValue >= 10
| extend _ResourceId = tostring(split(_ResourceId, "/")[-1])
| extend Target = tostring(split(Target, ":")[-1])
| order by _ResourceId, Data
Notes
- Replace
appi-examplewith a part of the name of your Application Insights resource, which selects the environment.