ADX performance issues

Hi HimanshuSinha ,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

Below step will help you to quick troubleshoot a ADX performance issues

1. Cluster health • .show diagnostics | project IsHealthy, NotHealthyReason • If unhealthy, check Portal → Resource health and address the noted reason.
2. Capacity pressure • .show capacity → review CPU %, memory %, CacheUtilizationFactor. • Sustained pressure → scale out (more engine nodes) or up (higher SKU) and/or use workload groups.
3. Find slow/failed queries

.show queries
| where StartedOn > ago(6h) and Duration > 5s
| project StartedOn, Duration, Text, ClientActivityId, ClientRequestId, User, State
| order by Duration desc

• Also check .show queries failures for recurring errors.

4. Explain one problematic query

set query_results_cache_max_age = 0m;
explain with (details="all")
<YOUR QUERY>

• Watch for early full scans, heavy mv-expand, unselective joins, wide text scans, high-cardinality summarize.

5. Fast query fixes • Filter and project early; avoid *. • Prefer has/startswith over broad contains where possible. • Time-bin aggregates: summarize ... by bin(Timestamp, 5m). • Pre-shrink and broadcast the small join side; materialize reused subqueries.
6. Ingestion contention • .show ingestion failures and .show operations | where StartedOn > ago(1h) and Operation startswith "DataIngest" • Tune batching: .show table T policy ingestionbatching. Stagger backfills/materialized-view rebuilds.
7. Data layout • Caching window: .show table T policy caching (align to common query range). • Partitioning: .show table T policy partitioning (ensure queries filter on the partition key). • Extent health/skew:

.show table T extents
| summarize extents=count(), rows=sum(row_count), size_gb=sum(original_size)/1024.0

If you can share details about the output or describe where you’re noticing errors or slowness, we can provide a more concrete suggestion.