# Expected Diagnosis: O11Y-2328599

## Root Cause

High-frequency JOIN query on `dh_message_setting` + `dh_message_setting_ext` caused TiKV-19 hotspot due to missing index on `dh_message_setting_ext.tag_id`. The `IndexLookUp` on table `c` (`dh_message_setting_ext`) scanned 303,713 rows per execution (15 minutes), triggering `server_is_busy` errors and cascading expensive query accumulation (500+) across the cluster.

Concurrent ANALYZE operations added load but were a secondary contributor, not the primary root cause.

## Evidence Chain

1. **Metrics**: TiKV-19 gRPC P99 latency 56.12s (vs <0.05s other nodes); CPU 28.78 cores (vs ~15 average)
2. **TopSQL (component=tikv, instance=TiKV-19)**: `dh_message_setting` JOIN query consumed 67.80% of TiKV-19 CPU (25,547s out of 37,680s)
3. **TopSQL (component=tikv, grouped by instance)**: TiKV-19 CPU 3.66x cluster average, load imbalance coefficient 4.78
4. **SlowQuery**: digest `61b692432...` averaged 174.95s execution time, 590.07s coprocessor time, 8,662 executions
5. **SlowQuery plan**: `IndexLookUp_193` on `dh_message_setting_ext` took 15m0.4s (83% of total); `server_is_busy` 29 times; `backoff` 2m21.8s
6. **Loki logs**: 50 expensive_query warnings at 04:01:10-16 UTC, 38/50 were the same `dh_message_setting` JOIN query

## Causality

Missing index on `dh_message_setting_ext.tag_id` -> `IndexLookUp` scans 300K+ rows per query -> TiKV-19 becomes hotspot (3.66x CPU) -> `server_is_busy` backpressure -> query latency >60s -> 500+ expensive queries accumulated -> alert fired

## Severity

High

## Recommendations

1. **Immediate**: Add composite index on `dh_message_setting_ext(message_id, tag_id)` to eliminate the 300K-row scan
2. **Short-term**: Review Region distribution for `dh_message_setting_ext` to check for hotspot caused by sequential writes
3. **Medium-term**: Evaluate query pattern - if `tag_id` selectivity is low, consider restructuring the query to push `tag_id` filter earlier

## Scoring Rubric

To pass this case, the Agent must:
- [ ] Correctly identify the `dh_message_setting_ext` (table c) missing index as root cause (not ANALYZE)
- [ ] Query TopSQL with component=tikv (hotspot is on TiKV-19; TiDB component is optional)
- [ ] Include instance dimension in TopSQL to identify TiKV-19 as hotspot
- [ ] Cross-reference with SlowQuery execution plan
- [ ] Identify the correct bottleneck operator by execution time (not by index type)
- [ ] Provide specific index recommendation for table c