Monitor and optimize an analytics solution
Where to look
- Monitoring hub - one view of pipeline runs, notebook and Spark job activity, dataflow refreshes, and their status/duration.
- Spark UI and Spark Advisor - stage/task skew, shuffle, executor utilization; Advisor surfaces concrete recommendations (partitioning, caching, broadcast joins).
- Capacity Metrics app - capacity units (CU) consumption by item, and throttling stages: interactive delay, then interactive rejection, then background rejection. Watch the 24-hour smoothing effect before concluding a spike is a capacity problem.
- Query insights / DMVs (warehouse) - slow T-SQL, repeated queries, missing statistics.
Storage and table tuning
Fabric writes Parquet with V-Order enabled by default, which speeds Direct Lake and Spark reads. Avoid small files: compact with OPTIMIZE (bin compaction, optional Z-ORDER on filter columns) and reclaim space with VACUUM (default 7-day retention). Partition only on high-cardinality, commonly filtered columns - over-partitioning creates the small-file problem you were trying to avoid. Target file sizes around 1 GB.
Compute tuning
Enable autoscaling and dynamic allocation for Spark, use high concurrency mode to share a session across notebooks, and set optimize write for partitioned sinks. Size capacity to the workload, then reduce waste: cancel idle sessions, schedule heavy jobs outside peak, and avoid full refreshes when incremental refresh suffices.
Direct Lake
Direct Lake reads Delta/Parquet directly from OneLake. If a query uses something it cannot translate (e.g., a SQL view, certain expressions) or exceeds guardrails, the model can fall back to DirectQuery - a classic exam trap. Check for views, unsupported data types, or overly large tables.
Rule of thumb
"Slow reads → check file sizes and run OPTIMIZE. Slow Direct Lake → check for fallback to DirectQuery. Slow everything → check the Capacity Metrics app for throttling."