APM Tips

KQL · dependencies

SQL dependency duration by name

Median and p95 duration and call counts for SQL dependencies, grouped by target and name rather than raw SQL text, with one example command.

Updated

dependencies dependencies 

let lookback = 24h;
dependencies
| where timestamp > ago(lookback)
| where type =~ "SQL"
| summarize
    calls = sum(itemCount),
    p50_ms = percentilew(duration, itemCount, 50),
    p95_ms = percentilew(duration, itemCount, 95),
    failed = sumif(itemCount, tostring(success) =~ "false"),
    example_command = take_any(substring(data, 0, 200))
    by target, name
| order by p95_ms desc

Returns: One row per SQL target and dependency name with the call count, p50 and p95 duration in milliseconds, failed calls, and the first 200 characters of one command.

Pitfalls

  • What name holds depends on the SDK. The classic .NET SDK stores the server and database name there for SQL calls, so each row is a database rather than a statement; the data model describes name as a low-cardinality command name such as a stored procedure.
  • data can hold the full SQL text, including literal values, and it is empty unless SQL text collection is enabled. Don't group by it: inline literals make every statement unique.
  • Other database clients record their own type names. Run dependencies | summarize sum(itemCount) by type to see what yours use, and adjust the filter.
  • Percentiles are weighted by itemCount so sampling doesn't skew them.

How it works

Slow pages are very often slow queries. This query lists your SQL dependencies by how slow they are for the worst 5% of calls, without grouping on the SQL text itself, which would split one statement into thousands of rows the moment it carries a literal value.

Line by line

  • where type =~ "SQL" keeps SQL calls; =~ ignores case. The data model lists SQL among the example dependency types.
  • calls = sum(itemCount) and the two percentilew() columns count and weight calls the sampling-aware way. duration is in milliseconds.
  • failed counts failed calls; a high p95 together with failures often means timeouts.
  • take_any(substring(data, 0, 200)) keeps the first 200 characters of one command. The data model describes data as the command initiated by the call, such as a SQL statement. Showing one example is useful; grouping by it is not.
  • by target, name groups by destination and dependency name. For the classic .NET SDK, Microsoft documents that the server and database name is always stored as the dependency name for SQL calls, while the full query text goes in data only when you enable it. With that SDK this query is effectively per database. Instrumentations that put a statement or procedure name in name give you per-command rows.

Getting per-command detail

When name only tells you the database, find which routes run the slow SQL instead. Group by operation_Name:

let lookback = 24h;
dependencies
| where timestamp > ago(lookback)
| where type =~ "SQL"
| summarize calls = sum(itemCount), p95_ms = percentilew(duration, itemCount, 95) by operation_Name, target
| order by p95_ms desc

A route that makes many SQL calls with a modest p95 each is a different problem from one slow statement: check dependency calls per request. The agent-based dependency tracking guide explains how to get the SQL text into data for the classic .NET stack and what the agent records.

Variations

  • Over time: group by bin(timestamp, 15m), target, keep p95_ms, sort by timestamp and add | render timechart.
  • Workspace tables: use AppDependencies, TimeGenerated, DependencyType, Target, Name, Data, DurationMs, Success and ItemCount.

Sources, checked

  1. Application Insights telemetry data model (dependency name and data)learn.microsoft.com · checked
  2. Application Insights Classic API 2.x (SQL dependency tracking)learn.microsoft.com · checked
  3. percentilew(), percentilesw() (Kusto)learn.microsoft.com · checked
  4. substring() (Kusto)learn.microsoft.com · checked
  5. Migrate an Application Insights classic resource to a workspace-based resource (table schemas)learn.microsoft.com · checked

  • Slowest dependencies by target

    dependencies dependencies

    Returns: Up to 20 rows, one per dependency type and target, with the call count, the p95 duration in milliseconds and the number of failed calls, slowest first.

  • Dependency p95 duration over time, by type

    dependencies dependencies

    Returns: One line per dependency type showing the p95 call duration in milliseconds for each 15-minute interval over the last 24 hours.

  • Dependency calls per request, by operation

    requestsdependencies dependencies

    Returns: One row per request operation with its request count, the dependency calls made under that operation name and the average calls per request, chattiest first.