The bottom line
A slow lakehouse query is usually caused by physical file layout, not query logic. In order of frequency: too many small Parquet files, over-partitioning that created them, missing or stale statistics, V-Order not applied on read-heavy tables, files never removed by VACUUM, layouts that block file skipping, the query itself, the wrong engine, and capacity contention. Diagnose in order — confirm it is running not queued, count files and partitions, check statistics and maintenance history, then read the plan last. In most estates, counting files ends the investigation: run OPTIMIZE and VACUUM on a schedule. A lakehouse without a maintenance routine has a countdown on it.
In This Article
It degrades on a schedule, not at random
The pattern is always the same. The lakehouse went live, the first dashboards were quick, everybody was pleased. Months later the same query takes minutes, and nobody changed it.
That is the most useful thing to know about lakehouse performance: it degrades on a schedule, not at random. The cause is almost never the query, and almost never the capacity SKU — even though those are the first two things everyone reaches for. This is about the lakehouse and query-engine layer: files, layout, statistics, maintenance, and the two very different engines Fabric puts on top of the same Delta tables.
Why is my data lakehouse query slow?
Ranked by how often each is the actual cause in a live mid-market estate:
| Cause | Share of real cases | Fix effort |
|---|---|---|
| Small-file fragmentation | Most common by a wide margin | Low — run OPTIMIZE |
| Over-partitioning (which produces small files) | Very common | High — table rewrite |
| Missing or stale statistics | Common on the SQL endpoint | Low |
| V-Order not applied on read-heavy tables | Common since the default changed | Low |
| VACUUM never run — log and file bloat | Common in estates over a year old | Low |
| Layout blocking pruning and pushdown | Moderate | Medium |
| The query itself — wide selects, mistyped joins | Moderate | Low |
| Wrong engine for the workload | Moderate | Medium |
| Capacity contention — queued, not slow | Moderate, spiky | Medium |
| Shortcuts reading across regions or clouds | Less common, severe when present | High |
What should I check first? A diagnostic sequence
Diagnose in this order. Is it running or waiting — check the Capacity Metrics app for throttling and the Monitoring hub for queued Spark jobs. Count the files — sys.sp_get_table_health_metrics gives file counts and sizes per table from T-SQL. Count the partitions — anything under 1 GB per partition is a warning sign. Check statistics — query sys.stats with STATS_DATE(). Check maintenance history — when did OPTIMIZE last run, has VACUUM ever run? Compare cold and warm runs — never judge on the first execution. Read the plan last.
If counting files and checking statistics come back clean and the table is still slow, you have an unusual problem. In most estates, counting files ends the investigation.
Storage layout: small files and over-partitioning
The small-file problem occurs when a Delta table accumulates thousands of small Parquet files instead of a few large ones, forcing the engine to open, read metadata for and schedule work against every file. Microsoft's guidance for the SQL analytics endpoint is roughly 2 million rows and about 400 MB per file; OPTIMIZE defaults target 1 GB. Three causes, all consequences of sensible engineering: frequent small writes (an hourly incremental writes a few files each run), streaming ingestion (a file per micro-batch per partition), and over-partitioning. MERGE makes it worse — it rewrites the files containing affected rows, so on a wide table changing a few thousand rows can rewrite many files.
Detect it by looking at the distribution, not the average — one 900 MB file and 4,000 files of 200 KB averages to something that looks fine and performs terribly. Fix it with OPTIMIZE (bin-compaction toward 1 GB). Over-partitioning is where good intentions go wrong: partitioning a fact table by date because that is what you did in Hive gives 1,095 tiny daily directories. Microsoft's position is to partition primarily to let concurrent writers avoid conflicts, keep each partition ≥1 GB, avoid high-cardinality partition columns, and use liquid clustering for read performance from Runtime 2.0. One caution: on Runtime 1.3, every OPTIMIZE on a liquid-clustered table rewrites all files in Z-Cubes under 100 GB — check your runtime before adopting it.
Metadata and maintenance: statistics, V-Order and VACUUM
Statistics: the SQL analytics endpoint and Warehouse create histogram, average-column-length and cardinality statistics automatically at query time (multi-column statistics are not supported). At the Delta layer, data skipping depends on per-file min/max/null statistics, which Delta collects for the first 32 columns of the schema by default — so if the column you filter on is column 40 in a wide table, it has no file statistics and no file skipping happens. Set delta.dataSkippingStatsColumns to fix that.
V-Order reorganises Parquet row-groups for read efficiency, and as of August 2026 it is disabled by default for new workspaces to favour write-heavy engineering. Apply it selectively: on if your gold layer is read many times a day and written once a night (OPTIMIZE … VORDER), off for bronze. And VACUUM: Delta never deletes at write time — rewritten and deleted files stay until VACUUM removes them. The default retention is 7 days, and shorter requests fail by default, because a long-running job may be writing files not yet committed. In estates over a year old, VACUUM never having run is a common cause of listing overhead.
The query and the engine
Three query patterns cause most avoidable work: SELECT * on a wide table (columnar formats only pay off when you ask for fewer columns); joins on mismatched types (int to varchar forces conversion and blocks pushdown); and functions wrapped around filter or partition columns (the engine can no longer match the predicate against file statistics, so it reads everything).
And two engines over the same OneLake files behave differently — a distinction missed constantly.
| Fabric Spark | SQL analytics endpoint | |
|---|---|---|
| Typical slow symptom | Job waits before starting | First run slow, later runs fast |
| Sizing lever | Node size, max nodes | None — serverless |
| Failure signature | HTTP 430 TooManyRequests | "capacity has exceeded its limits" |
| Freshness | Reads Delta directly | Metadata sync, normally under a minute |
| Diagnostic | Monitoring hub, Spark UI | queryinsights.exec_requests_history |
On F64, Spark has 128 base VCores (up to 384 with 3× bursting) and a 64-job queue — FIFO for pipeline and scheduler jobs, but interactive notebook jobs fail rather than queue. Capacity throttling escalates in stages: overage under 10 minutes runs freely, 10–60 adds a 20-second interactive delay, 60 minutes to 24 hours rejects interactive operations while background jobs continue. Throttling only affects new operations — anything in flight completes. Locality is the last and most expensive cause: a shortcut to an S3 bucket or a storage account in another region means every uncached read crosses a network you do not control.
Symptom to cause to fix
| Symptom | Most likely cause | Fix |
|---|---|---|
| Got gradually slower over months | Small-file fragmentation | OPTIMIZE; enable auto compaction |
| Slow immediately after go-live, never fast | Over-partitioning | Rewrite without partitions; adopt liquid clustering |
| Fast in Spark, slow at the SQL endpoint | Small files + metadata sync | OPTIMIZE; split lakehouses across workspaces |
| First run slow, later runs fast | Cold cache | Expected; compare warm runs |
| Slow only at 08:00 and 17:00 | Capacity contention | Reschedule loads; review SKU |
| Fails with HTTP 430 | Spark VCore limit reached | Reduce concurrency; resize pool |
| Filtered query still scans everything | Filter column outside first 32 stats columns, or a function on it | Set dataSkippingStatsColumns; rewrite predicate |
| Storage growing, queries slowing | VACUUM never run | Schedule VACUUM at 7-day retention |
| Read-heavy gold tables slow | V-Order off by default | OPTIMIZE … VORDER on gold only |
| Joins slow despite small tables | Type mismatch forcing conversion | Align join column types |
The maintenance routine that prevents most of this
Prevention is the real answer — a lakehouse without a maintenance schedule is a lakehouse with a countdown on it.
- Weekly — OPTIMIZE on all silver and gold tables with meaningful write volume, scheduled via the Lakehouse Maintenance activity in a Data Factory pipeline, not by hand
- Weekly — VACUUM at the default 7-day retention
- Monthly — review sys.sp_get_table_health_metrics across the estate and act on the outliers
- Monthly — review partition counts and sizes; anything under 1 GB per partition is a candidate for removal in favour of liquid clustering
- On schema change — revisit delta.dataSkippingStatsColumns and V-Order table properties
- Quarterly — review capacity metrics for throttling patterns
None of this is difficult. It is simply nobody's job, which is why it does not happen.
A lakehouse without a maintenance schedule has a countdown on it. OPTIMIZE and VACUUM aren't optional — they're just usually nobody's job.
Where this breaks, and what it does not fix
OPTIMIZE costs capacity units — compaction is a full rewrite of the files it touches, so schedule it off-peak. Liquid clustering is not free on older runtimes — on Runtime 1.3 the full-rewrite behaviour makes frequent OPTIMIZE actively harmful. V-Order is not universally right — roughly 15% slower writes is a poor trade on a bronze layer written constantly and read rarely.
None of this fixes a bad model — if the query joins six fact tables at row grain to answer a question that should have been aggregated upstream, file layout will not save it. None of it fixes source latency — if the ERP extract lands at 06:45 and the report opens at 07:00, the problem is the pipeline schedule. And undersized capacity is undersized capacity — tuning buys headroom, not a tier.
What to do first
Four questions you can answer this week:
- How many Parquet files does your largest fact table contain, and what is the median file size?
- When did OPTIMIZE last run on that table — and has VACUUM ever run against it?
- Is the table partitioned, and if so, how many partitions hold less than 1 GB?
- Over the last 30 days, has your capacity entered interactive delay or rejection at any point?
If you cannot answer the second question, that is your answer. We run this diagnosis as a fixed-scope piece of work on live estates, and where the finding is structural we rebuild the affected layer rather than tuning around it.
The single question that usually ends the investigation: when did OPTIMIZE last run on your biggest fact table, and has VACUUM ever run? If you cannot answer, the fix is a maintenance schedule, not a bigger SKU. Book a diagnostic with Amit — no slides, no pitch deck, no obligation to proceed. Where the finding is structural, we rebuild the layer rather than tuning around it.
Free Assessment
Where does your operation sit on the data maturity curve?
8 questions. 3 minutes. You get a scored breakdown across data infrastructure, analytics readiness, and automation potential — with a specific next step for your industry.