In Short: Boring Maintenance, Real Money
When a Fabric estate feels slow - notebooks crawling, Direct Lake visuals lagging, capacity mysteriously saturated - the cause is rarely exotic. It is almost always one of four boring things: thousands of tiny files, tables never compacted, partitioning misapplied, or Spark sessions sized on optimism. The fixes are equally boring, which is the good news: V-Order, OPTIMIZE, VACUUM, sensible partitioning and right-sized sessions are maintenance, not magic.
This is the checklist we run first on every performance engagement, because it usually ends the engagement.
V-Order: The Write-Time Favour to Every Reader
V-Order is Fabric's write-time optimisation for Parquet: data is sorted and encoded so Microsoft's readers - the SQL endpoint and, above all, Power BI's Direct Lake - scan it far faster. Writes pay a modest premium; every read thereafter collects.
The judgement call is straightforward: keep V-Order on for tables that serve BI and interactive SQL (your gold layer, especially), and consider relaxing it only for write-heavy intermediate tables nobody queries directly, where ingestion throughput is the scarce resource.
The Small-Files Problem, and OPTIMIZE
Delta tables accumulate files with every append, and streaming or frequent small loads accumulate thousands of them. Query engines pay per file - open, read footer, plan - so a table of 10,000 tiny files can be an order of magnitude slower than the same data in well-sized files.
OPTIMIZE compacts. Run it on a schedule proportional to write frequency: nightly for continuously-fed tables, weekly for calmer ones, immediately after any large backfill. Fabric also offers automatic compaction behaviours on managed tables, but do not assume: inspect your busiest tables' file counts, because the small-files tax is the single most common finding in slow estates.
VACUUM is the companion chore: it deletes unreferenced files left behind by updates and compactions, reclaiming storage. The cost is time travel - vacuumed history is gone - so set retention deliberately rather than accepting whichever default nobody read.
Partitioning: Less Than You Think
Partitioning splits a table into directories by column value, so filtered queries skip whole directories. It pays on large tables filtered by a low-cardinality column - date is the classic. It hurts everywhere else: partitioning a modest table by a high-cardinality column shatters it into exactly the small-files problem OPTIMIZE exists to fix.
Rule of thumb we apply: no partitioning below tens of gigabytes; when in doubt, rely on file-level statistics and V-Order instead; and never partition to mirror an org chart.
Sessions, Concurrency and the Bill
Spark consumes capacity while sessions run, not while work completes. The habits that waste it: notebooks on maximum node counts "to be safe", sessions left alive between meetings, and a dozen scheduled jobs each spinning a private cluster. The remedies: default to small sessions and scale on evidence, let idle timeouts do their job, and consolidate schedules so related jobs share warm compute. High-concurrency session sharing in notebooks exists precisely for the many-small-jobs pattern.
The satisfying part of this whole discipline: performance work and cost work are the same work. Every skipped file scan and idle minute reclaimed shows up twice - in user patience and in the capacity metrics your finance team reads.
Sources and Further Reading
Frequently asked
A write-time optimisation that sorts and compresses Parquet data so Microsoft's engines - especially Power BI's Direct Lake and the SQL endpoint - read it dramatically faster. It costs a little at write time and pays on every read; for BI-serving tables it should stay on.
Compacts many small files into fewer, well-sized ones. Streaming and frequent small appends create thousands of tiny files, and query engines pay a tax opening each; OPTIMIZE consolidates them and restores scan performance.
It permanently removes data files no longer referenced by the table's recent history, reclaiming storage. The trade-off is time travel: after a vacuum, you can no longer restore the table to versions whose files were removed, so retention settings deserve a deliberate decision.
Only large ones, and only on low-cardinality columns that queries genuinely filter by (a date grain is typical). Over-partitioning small tables recreates the small-files problem and is one of the most common self-inflicted slowdowns we find.
Usually, yes. Badly maintained tables make every query scan more data, and oversized Spark sessions burn capacity units idling. Table maintenance and right-sized sessions routinely show up as capacity savings, not just faster queries.
