The Symptom Everyone Sees, The Cause Nobody Suspects
In most performance post-mortems I’ve been part of, the conversation starts the same way:
“The data is slow.”
Dashboards are lagging. Reports are timing out. Business users are frustrated. Service Level Targets (SLTs) are being breached, and fingers are pointing squarely at the data layer.
And to be fair — the data is where the pain surfaces.
But here’s the uncomfortable truth that took us time to fully unpack:
The data wasn’t broken. The data was overwhelmed — and it had a geography problem.
What looked like a data-platform problem turned out to be the interaction of three different layers:
- Concurrency — too many workloads competing for shared capacity.
- Geography — cross-region network latency adding to every request.
- Infrastructure thresholds — gateway and timeout behaviour amplifying the underlying delays.
The investigation became less about making individual queries faster and more about understanding the complete path from user to data and back again.
The Investigation: Peeling Back the Layers
Layer 1 — SLT Misses and the Data Blame Game
Our Service Level Targets were consistently missed during peak business hours.
Query response times that should have landed under 3 seconds were stretching to 30, 60, sometimes over 120 seconds. Dashboards were either stale or outright failing to load.
The immediate assumption?
Data quality issues. Bad indexes. Poorly written queries. An overloaded dataset.
We tuned queries.
We rebuilt indexes.
We optimised data models.
The problem persisted.
That was the first indication that we were probably looking in the wrong place.
Layer 2 — The Real Root Cause: Data Contention Under Concurrent Load
What our monitoring eventually revealed was not a quality problem but a concurrency problem.
During peak hours, hundreds of users were simultaneously firing queries against the same datasets.
The underlying engine — running on a shared Microsoft Fabric capacity — was not failing because the data was wrong.
It was failing because too many queries were competing for the same computational resources at the same time.
This is data contention: a condition where high volumes of concurrent queries queue up, block each other, and create a cascading slowdown that ripples all the way to the end-user experience.
Key symptoms we identified included:
- Query queue depth spiking dramatically during the 9–11 AM and 2–4 PM windows.
- Throttling events logged at the Fabric capacity level.
- Spill-to-disk operations increasing as memory pressure mounted.
- Individual query execution time remaining acceptable in isolation, but degrading by 10–40x under concurrent load.
That distinction was critical.
A query could be perfectly healthy when tested in isolation and still become unusable when hundreds of similar workloads arrived simultaneously.
The data was never the culprit.
Contention was.
Layer 3 — The Atlantic Problem: Latency Nobody Mapped
Here is where the investigation took an unexpected turn.
Our Microsoft Fabric capacity tenant was provisioned in a region across the Atlantic — physically and network-topologically distant from the majority of our user base.
What looked like slow query responses was, in many cases, not slow computation at all.
It was network round-trip time compounding every interaction.
The effects were insidious.
Gateway timeouts
Connections from local gateways to the remote Fabric tenant were breaching timeout thresholds before query results could be returned.
That did not necessarily mean the underlying query was too slow.
The combination of network latency, connection behaviour, data transfer time, and gateway thresholds could push the total wall-clock time beyond the point at which the client was willing to wait.
Connection failures
Under load, connection failures became more frequent.
From the user’s perspective, the result was simple:
“The report failed.”
But that error did not necessarily mean the capacity had stopped working.
The request could have been delayed, interrupted, or failed somewhere between the gateway and the remote capacity.
The compounding effect
The real problem was the interaction:
contention-induced delay + cross-region latency + gateway timeout thresholds
That combination made every SLT miss look worse than the underlying compute performance warranted.
A query that took 8 seconds to execute could appear as a 45-second user-facing failure.
The network and infrastructure path had amplified the original delay.
The Architecture of the Problem
The request path looked approximately like this:
[User / BI Client]
|
| Local network
|
[On-Premises / Regional Gateway]
|
| Cross-region network path
| ~80–150 ms RTT in observed conditions
|
[Microsoft Fabric Capacity Tenant - Remote Region]
|
| Contention point
| Concurrent queries competing for shared capacity
|
[Dataset / Lakehouse / Warehouse]
Every request had to traverse this path.
The important point was not simply that the tenant was geographically distant.
It was that latency and contention interacted.
A request delayed by compute contention had less tolerance for additional network and gateway overhead.
Likewise, a geographically distant workload had less tolerance for queueing and execution delays.
The layers amplified each other.
What We Did About It
Once the investigation moved beyond the data layer, the remediation strategy became much clearer.
There was no single magic fix.
We had to address the system as a complete path.
1. Addressed Contention at the Capacity Layer
We focused first on reducing unnecessary competition for shared resources.
Actions included:
- Scaling Fabric capacity during peak windows using autoscale policies.
- Implementing query concurrency limits and workload-management rules to prioritise critical service-level reports.
- Introducing incremental refresh and aggregation tables to reduce raw query workload.
- Shifting non-urgent batch workloads to off-peak hours to reduce simultaneous demand.
The goal was not simply to make every query faster.
It was to make the system more predictable under load.
That distinction matters.
A platform that produces a 2-second query most of the time and a 120-second query during peak concurrency is often more difficult to operate than one that consistently produces a predictable response.
2. Tackled the Geography Problem
The second intervention addressed the physical location of the workload.
We worked through the Fabric tenant configuration and migrated capacity to a region closer to the primary user base.
This required planning and coordination, but it delivered one of the most significant latency improvements.
As an interim measure, we also:
- Increased gateway timeout thresholds where appropriate so legitimate longer-running requests were not terminated prematurely.
- Reviewed connection-management behaviour at the gateway layer.
- Reduced unnecessary connection setup overhead where the architecture allowed it.
The important distinction was that increasing timeout values was not treated as the solution.
A larger timeout can prevent a premature failure.
It does not remove the latency or contention causing the delay.
That made it a mitigation, not a cure.
3. Improved Observability
The final intervention was perhaps the most important for preventing the next incident.
We needed to stop looking at “query duration” as a single number.
We instrumented telemetry to distinguish:
- network latency,
- queue time,
- compute time,
- gateway processing,
- and end-to-end user-perceived response time.
We also:
- Built a capacity-utilisation dashboard to provide early warning of contention events.
- Established a baseline for expected cross-region RTT.
- Monitored gateway behaviour alongside capacity and query telemetry.
- Correlated performance events across the request path rather than investigating each layer independently.
This changed the investigation model.
Instead of asking:
“Why is this query slow?”
we could ask:
“Where did the request spend its time?”
That is a much more useful question.
Before and After
| Area | Before | After |
|---|---|---|
| Peak concurrency | Uncontrolled contention | Workload management and prioritisation |
| Capacity | Shared demand exceeded predictable capacity | Capacity aligned more closely with peak workload |
| Geography | Cross-region deployment | Capacity moved closer to primary users |
| Gateway | Timeout behaviour amplified failures | Timeout and connection behaviour reviewed and tuned |
| Observability | Compute and network effects were difficult to separate | Latency and capacity telemetry separated |
| SLT compliance | ~54% | >91% |
The Results
Once the contention-management and regional-migration work was completed, the results were measurable.
- Average query response time dropped by ~68% during peak windows.
- SLT compliance improved from ~54% to over 91% within six weeks.
- Gateway timeouts effectively dropped to near-zero.
The important result was not simply that queries became faster.
The platform became more predictable.
The data, as it turned out, had been perfectly fine the whole time.
Key Takeaways for Data & Platform Engineers
-
Don’t confuse the symptom with the cause. Slow data experiences are often infrastructure and concurrency problems wearing a data costume.
-
Concurrent query volume is a first-class concern. Design capacity with peak concurrency in mind, not just peak data volume.
-
Geography matters more than people think. Cross-region tenancy is often an afterthought in platform provisioning. It shouldn’t be. Measure RTT early and establish a baseline.
-
Gateway timeouts are not necessarily a query story. If timeouts correlate with specific time windows and user geographies, investigate latency and gateway behaviour before assuming the query itself is the primary problem.
-
Observability must separate layers. If you cannot distinguish network time from queue time and compute time in your telemetry, you will repeatedly misdiagnose performance problems.
-
Optimisation has to consider the whole request path. A query can be perfectly tuned and still produce a poor user experience if the surrounding architecture is the bottleneck.
-
Predictability matters. Enterprise platforms are not judged only by their fastest response. They are judged by how reliably they behave under real workload conditions.
Final Thought
The most dangerous performance problems are the ones that look obvious but aren’t.
SLT misses that appear to be data problems can spend months in the wrong queue — being “fixed” by data engineers while the real causes sit elsewhere in the architecture.
The lesson from this investigation wasn’t simply to tune queries better.
It was to stop treating performance as a property of the data layer alone.
Performance is a property of the path the request takes through the entire system.
That path includes the workload, the capacity, the network, the gateway, the data platform, and the consumer.
If you only measure the layer where the pain appears, you can spend a very long time optimising the wrong thing.
Instrument the full path. Follow the evidence. Fix the layer that is actually limiting the system.
Related Articles
The DISTKEY That Was Right, and Still Wrong
A Redshift investigation into how a locally correct physical-design decision created problems for downstream consumers.
The Post-Acquisition Assumption Gap
How inherited assumptions around CDC, SCD, provenance, and governance can survive long after systems have been technically integrated.
About the Author
Aniruddha Banerjee is a Project Manager and Data Architect working across enterprise data engineering, cloud and analytics platforms, performance engineering, and technology architecture.
Through Aniruddha Writes, he explores the engineering decisions behind complex data platforms — particularly the assumptions, trade-offs, and failure patterns that become visible only at enterprise scale.
```