LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon