**From Trace Spans to Actionable Insights: A Workflow for Slow Query Analysis**
Slow SQL queries degrade user experience, cause cascading failures, and turn simple operations into production incidents. The traditional fix? Collect more telemetry. But more telemetry means more things to look at, not necessarily more understanding. Instead of treating traces as a data stream we might analyze someday, we should be opinionated about what matters at the moment of decision. As we argued in *The Signal in the Storm*, raw telemetry only becomes useful when we extract meaningful patterns.
In this guide, you’ll build a repeatable workflow that turns OpenTelemetry database spans into span-derived metrics you can dashboard and alert on—so you can identify what’s slow, what matters most, and what just regressed. We’ll make this concrete with slow SQL queries, serving two use cases:
* **Optimization**: Which queries yield the most value if made faster, weighted by traffic?
* **Incident response**: Which queries are behaving abnormally right now?
We’ll build a lab where your app emits OpenTelemetry traces, and we distill those into actionable metrics, starting with simple slow query detection, then adding traffic-weighted impact, and finally anomaly detection.
—
### Understanding the Problem: Why Raw Traces Aren’t Enough
“Slow” isn’t a single problem. It’s a symptom with fundamentally different causes. A 50ms query might be fine for a reporting dashboard but catastrophic for checkout. As *High Performance MySQL* emphasizes, understanding why a query is slow determines how to fix it. Here are the most common problems that may cause slow queries:
**Excessive Work**
The database does more than necessary—typically full table scans due to missing or unusable indexes. Without an index on `customer_id`, a simple `SELECT * FROM orders WHERE customer_id = $1` grows from 20ms at 10K rows to minutes at 10M rows. Aggregations and joins compound this. Even indexed queries can explode when the planner misjudges cardinality and chooses the wrong join strategy.
**Resource Contention**
Perfectly optimized queries can be slow when waiting for resources. Lock contention blocks queries until other transactions release rows. Connection pool exhaustion adds latency before the query even starts. A query spending 95% of its time waiting for locks won’t be fixed by query optimization—it needs transaction redesign.
**Environmental Pressure**
CPU saturation, I/O bottlenecks, and memory pressure can slow any query. The same SQL with the same plan performs completely differently under resource contention.
**Plan Regressions**
Performance degrades when execution plans change—even with identical queries and data. Parameter-sensitive plans optimize for one set of values but fail for others. Stale statistics after bulk loads cause the planner to choose terrible strategies.
**Pathological Patterns**
Some slowness doesn’t appear in slow query logs. The N+1 problem executes 100 fast queries sequentially, adding latency plus network overhead. No individual query is “slow,” but the pattern is catastrophic.
Databases ship with excellent diagnostic tools: slow query logs, query stores, and `EXPLAIN`. These tell you what’s expensive inside the database. What they don’t provide is context—*which service* triggered the slow query, *is it user-facing or background work*, and *does it correlate with the latency spike you’re investigating*? You’re left with a list of slow queries and no signal about which ones matter most.
Typically, someone bridges this gap manually: a developer notices a slow endpoint, brings the query to a DBA, and they optimize it together. This works, but that manual linking is exactly what we can automate.
—
### The Building Blocks: Observability Stack and Application
For our lab, we use the OpenTelemetry Collector paired with docker-otel-lgtm—a pre-packaged stack from Grafana that bundles Loki, Grafana, Tempo, and Mimir. This gives us a complete observability environment with minimal setup.
Our sample application is a simple Go-based “Album API” that serves music album data from PostgreSQL. It’s intentionally designed to produce the kind of intermittent slow queries that are common in production. The services use `otelsql` to instrument database calls, emitting spans with the stable OpenTelemetry database semantic conventions.
We’ll build three dashboards, each adding a layer of insight:
1. A simple view of the queries by duration.
2. Queries weighted by traffic to surface optimization opportunities.
3. Anomaly detection to identify queries deviating from their normal behavior.
—
### Lab Setup: Clone and Run
“`bash
git clone
cd slow-query-lab
docker-compose up -d
“`
Once running, open Grafana at `localhost:3000` (default credentials: admin/admin) where we’ll explore our dashboards.
—
### Dashboard 1: Slow SQL – By Duration (Simple View)
The first dashboard takes the most direct approach: query Tempo for database spans and aggregate them to find queries that take the longest time. This is what you’d naturally build when you first start exploring traces for slow query analysis.
**What It Shows**
The Slow SQL – By Duration dashboard queries traces directly using TraceQL:
“`
{ span.db.system != “” } | select(span.db.query.text, span.db.statement)
“`
Then it groups by root operation (API endpoint) and SQL statement, aggregates duration into mean, max, and count, and sorts by average duration (slowest first).
**What’s Good About This**
This approach gives you immediate visibility into queries with full application context:
* You can see exactly which SQL statements are taking the most time.
* You know which API endpoints trigger them.
* You have the count to understand frequency.
* You can click through to individual traces for debugging.
It’s a first improvement over raw database logs because you’re already seeing the application context that makes slow queries actionable.
**The Limitation**
Here’s the problem: sorting by average duration doesn’t tell you which queries matter most. Consider:
| Query | Avg Duration | Count |
|——————-|————–|——–|
| Complex report | 2.3s | 5 |
| Search | 150ms | 10,000 |
The complex report is “slower” by average duration, so it appears first. But the search query, despite being faster on average, runs 2,000 times more often. Its aggregate impact is far greater. This dashboard tells you what’s slow, but not what’s impactful.
—
### Dashboard 2: Slow SQL – Traffic Weighted (Optimization Focus)
The second dashboard addresses this limitation by introducing an impact score: **Average Duration × Call Count**.
**What It Shows**
Using the same TraceQL query with a calculated field:
“`
Impact = Avg Duration × Count
“`
This simple formula captures a key insight: a moderately slow query that runs thousands of times has more total impact than a very slow query that runs rarely. The dashboard sorts by impact score, surfacing the queries that matter most to your users.
It also adds:
* **Service breakdown**: See which service triggered each query.
* **Latency distribution**: Visualize duration over time, not just averages.
* **Top queries by impact**: A quick view of where to focus optimization efforts.
**What’s Good About This**
Traffic-weighted impact gives you a much better prioritization signal for optimization work:
* High-volume, moderately-slow queries surface above rare-but-slow ones.
* You can justify optimization work with concrete impact numbers.
* The service and endpoint context helps you route issues to the right team.
When someone asks “which slow queries should we optimize first?”, this dashboard gives you a defensible answer. It’s exactly what you need for planning performance improvements.
**The Limitation**
But this dashboard is for optimization, not incident response. Even with traffic-weighted impact, it can’t answer a critical question: **“What has changed?”**
Suppose your search query has an impact score of 150,000. Is that normal? Is it higher than yesterday? Higher than last week? The dashboard shows you a snapshot of current state, but it has no concept of baseline.
This matters enormously during incidents. When latency spikes, you don’t just want to know “search queries are slow”—you want to know “search queries are slower than normal”. You need to distinguish between:
* A query that’s always been slow (known behavior, maybe acceptable)
* A query that just became slow (new problem, needs investigation)
Without a baseline, every slow query looks the same.
—
### Dashboard 3: Slow SQL – Anomaly Detection (Incident Response)
Because of these limitations, the third dashboard changes our approach: instead of just querying traces, we distill metrics from spans and then apply anomaly detection to identify deviations from normal behavior.
**The Setup**
For this dashboard, we add the **spanmetrics connector** to the OpenTelemetry Collector. Here’s the relevant part of the collector configuration:
“`yaml
connectors:
spanmetrics:
dimensions:
– name: db.system
default: “unknown”
– name: db.query.text
– name: db.statement
– name: db.name
default: “unknown”
exemplars:
enabled: true
service:
pipelines:
traces:
receivers: [otlp]
processors: [transform, batch]
exporters: [spanmetrics, otlphttp/lgtm]
metrics:
receivers: [spanmetrics]
processors: [batch]
exporters: [otlphttp/lgtm]
“`
The **spanmetrics connector** examines every database span and generates histogram metrics for query latency, labeled by:
* `service_name`: Which service made the query
* `db_system`: Database type (postgresql)
* `db_query_text` or `db_statement`: The SQL query
* `db_name`: Database name
These metrics are stored in Mimir (the Prometheus-compatible backend in docker-otel-lgtm), where we can apply PromQL-based anomaly detection.
**Anomaly Detection with Adaptive Baselines**
The sample app includes Prometheus recording rules from Grafana’s PromQL Anomaly Detection framework. These rules calculate:
* **Baseline**: A smoothed average of historical values (what’s “normal”)
* **Upper band**: Baseline + N standard deviations (upper threshold)
* **Lower band**: Baseline − N standard deviations (lower threshold)
When current values exceed the bands, we have an anomaly—a clear signal that something has changed.
**What It Shows**
The Slow SQL – Anomaly Detection dashboard displays:
* **Current latency** plotted against the adaptive baseline bands
* **Anomaly indicators** when latency exceeds normal bounds
* **Per-query breakdown** so you can see which specific queries are anomalous
The key insight is the visual comparison: instead of just showing “p95 latency is 450ms”, it shows “p95 latency is 450ms, which is above the expected range of 200–350ms.”
**Why This Is Better**
This dashboard answers the question the previous one couldn’t: **“What has changed?”**
* A query that’s always slow (450ms baseline) won’t trigger anomalies when it runs at 450ms
* A query that’s normally fast (50ms baseline) will trigger anomalies if it suddenly runs at 200ms
* You get automatic context for what’s “normal” without maintaining manual thresholds
The anomaly detection acts as a symptom detector. It tells you: “This query is behaving differently than it usually does.” That’s a high-signal insight you can act on immediately.
—
### From Metrics to Symptoms to Root Cause
Notice what we’ve achieved with this architecture:
1. **Raw telemetry** (traces) flows from the application
2. **Distillation** (spanmetrics connector) extracts metrics from those traces
3. **Anomaly detection** (Prometheus rules) identifies deviations from baseline
4. **Symptoms** (anomalous queries) surface for investigation
We went from thousands of trace spans to a handful of anomaly signals that tell you exactly where to look.
Even with anomaly detection, you’re still looking at symptoms. In real-world incident scenarios, slow queries are just one of many symptoms that pop up at once. You’re not only trying to understand this one; you’re triaging a flood of alerts and correlating many symptoms to find the real root cause.
Connecting the symptom (“search query is slow”) to the root cause (“index was dropped during last night’s migration”) requires causal reasoning—understanding the relationships between system components and tracing the chain of causation from effect back to cause.
You can absolutely do this reasoning yourself. Look at deployment timestamps, check for schema changes, investigate resource metrics, correlate with other symptoms. Good engineers do this every day.
But it’s manual, time-consuming, and doesn’t scale.
—
### FAQ
**Q: Why not just use traditional slow query logs?**
A: Slow query logs lack application context—they don’t tell you which service triggered the query, whether it’s user-facing or background work, or if it correlates with a latency spike you’re investigating. Traces provide this context.
**Q: What about the N+1 query problem?**
A: The same approach works. The spanmetrics connector will create separate metrics for each unique query statement, allowing you to detect when many small queries are adding up to significant latency.
**Q: How much historical data is needed for anomaly detection?**
A: Adaptive baselines typically need 24–48 hours of data to establish reliable norms. Start with wider bands and tighten as confidence grows.
**Q: Won’t raw SQL in metrics explode cardinality?**
A: Yes—SELECT * FROM orders WHERE customer_id = 12345 becomes a separate series per customer. Use prepared statements (so instrumentation captures templates, not literals), normalize query text, or configure `aggregation_cardinality_limit` in the spanmetrics connector.
**Q: How can I secure my SQL queries in metrics?**
A: The OpenTelemetry Collector is the ideal place to redact sensitive information. Drop or transform sensitive attributes before shipping downstream—this aligns with the distillation principle: sanitize at the edge, not centrally.
—
### Conclusion
We’ve transformed slow query analysis from reactive log scraping into a systematic, context-rich workflow. By distilling OpenTelemetry traces into metrics and applying anomaly detection, we move from “something is slow” to “this specific query is behaving abnormally, here’s why it matters, and here’s where the real problem likely lies.”
Even with these powerful tools, diagnosing root causes in complex systems remains challenging. That’s where causal reasoning platforms like **Causely** come in—building on this foundation of distilled symptoms to automatically trace issues back to their root causes. Try it yourself and turn your telemetry from noise into actionable insight.
