agentleFS
PatrickGallucciPatrickGallucci/fabric-skills/skills/fabric-lakehouse-perf-remediate/SKILL.md

fabric-lakehouse-perf-remediate

Skill17 starsChanged 9 months agoMITView on GitHub

Diagnose and resolve Microsoft Fabric Lakehouse performance issues including slow Spark queries, small file problems, Delta table fragmentation, V-Order configuration, table maintenance (OPTIMIZE, VACUUM, Z-Order), SQL analytics endpoint tuning, Direct Lake performance, resource profile selection, autotune configuration, capacity throttling, and streaming ingestion optimization. Use when asked to troubleshoot Fabric Lakehouse slowness, optimize Delta tables, fix small file problems, configure Spark settings, run table maintenance, or improve query performance in notebooks or pipelines.

The file

We split this skill into the rules it gives an agent

3 rules. About 0 are common, 3 are distinctive, and 0 are very specific to this project.

  • ≈0

    Common · estimate

    Many files like this have a rule of this kind.

  • ≈3

    Distinctive · estimate

    This author's own choices. Worth a look.

  • 0

    Very specific

    Tied to their project's names, files or tools. Probably skip.

  • Common
  • Distinctive
  • Very specific
  1. Distinctive:

    When a user reports Lakehouse performance issues, work through these areas in order:

    line 33
  2. Distinctive:

    Run in a Fabric notebook (Spark SQL):

    line 59
  3. Distinctive:

    When streaming data into a Lakehouse, prevent small file proliferation:

    line 177

≈ “Common” is an estimate: at least 1 in 5 of the 364,980 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.

Keep what fits and drop the rest. Your version, ready for your agents.
Files
SKILL.md

Fabric Lakehouse Performance remediate

Systematic toolkit for diagnosing and resolving performance issues in Microsoft Fabric Lakehouse environments. Covers Delta table health, Spark compute tuning, query optimization, and automated maintenance workflows.

When to Use This Skill

  • Lakehouse queries are running slowly or timing out
  • Delta tables have accumulated many small files (small file problem)
  • Spark notebooks or jobs are underperforming
  • Direct Lake semantic models have cold-start or transcoding delays
  • SQL analytics endpoint queries are slow
  • Table maintenance (OPTIMIZE, VACUUM) needs to be scheduled or automated
  • V-Order, Z-Order, or resource profile configuration is needed
  • Capacity throttling or concurrency issues are suspected
  • Streaming ingestion is creating fragmented Delta tables

Prerequisites

  • Microsoft Fabric workspace with Lakehouse items
  • Contributor or higher workspace role
  • Fabric capacity (F2 or above) or Trial capacity
  • For REST API automation: Microsoft Entra token for Fabric service
  • For Spark commands: Access to Fabric notebooks or Spark Job Definitions

Quick Diagnosis Checklist

When a user reports Lakehouse performance issues, work through these areas in order:

  1. Identify the symptom — Slow reads, slow writes, capacity throttling, or query timeouts
  2. Check Delta table health — File count, file sizes, V-Order status, partition layout
  3. Review Spark configuration — Resource profile, autotune, shuffle partitions
  4. Inspect capacity utilization — Concurrency limits, burst capacity, throttling
  5. Evaluate maintenance history — When was OPTIMIZE/VACUUM last run?
  6. Assess data patterns — Streaming vs batch, read-heavy vs write-heavy

Symptom-to-Action Map

SymptomRoot CauseAction
Slow reads across all enginesSmall files, no V-OrderRun OPTIMIZE VORDER, switch to readHeavy profile
Slow Spark queries onlyWrong shuffle partitionsEnable autotune or tune manually
Slow Power BI Direct LakeToo many Parquet files/row groupsRun OPTIMIZE, check guardrail limits
Slow SQL analytics endpointFiles under 400 MB, too many small filesOPTIMIZE with maxRecordsPerFile=2M
Write performance degradedV-Order enabled on write-heavy workloadSwitch to writeHeavy resource profile
Capacity throttledToo many concurrent Spark jobsReview concurrency limits, enable optimistic admission
Storage growing unexpectedlyVACUUM not runningSchedule VACUUM with 7-day retention
Streaming creates tiny filesNo batching or trigger intervalAdd processingTime trigger, run periodic OPTIMIZE

Core Optimization Operations

1. Table Maintenance Commands

Run in a Fabric notebook (Spark SQL):

-- Basic OPTIMIZE (bin-compaction)
OPTIMIZE lakehouse_name.schema_name.table_name;

-- OPTIMIZE with V-Order
OPTIMIZE lakehouse_name.schema_name.table_name VORDER;

-- OPTIMIZE with Z-Order on frequently filtered columns
OPTIMIZE lakehouse_name.schema_name.table_name ZORDER BY (column_name);

-- OPTIMIZE with both Z-Order and V-Order
OPTIMIZE lakehouse_name.schema_name.table_name ZORDER BY (column_name) VORDER;

-- OPTIMIZE specific partitions only
OPTIMIZE lakehouse_name.schema_name.table_name WHERE date_key >= '2025-01-01' VORDER;

-- VACUUM with default 7-day retention
VACUUM lakehouse_name.schema_name.table_name;

-- VACUUM with custom retention (requires safety check disabled)
VACUUM lakehouse_name.schema_name.table_name RETAIN 168 HOURS;

2. Resource Profile Configuration

Set at environment level or runtime. See resource-profiles.md for details.

# Check current profile
spark.conf.get('spark.fabric.resourceProfile')

# Switch to read-heavy for Spark queries
spark.conf.set("spark.fabric.resourceProfile", "readHeavyForSpark")

# Switch to read-heavy for Power BI Direct Lake
spark.conf.set("spark.fabric.resourceProfile", "readHeavyForPBI")

# Switch to write-heavy for ETL/ingestion
spark.conf.set("spark.fabric.resourceProfile", "writeHeavy")

3. V-Order Control

# Check current V-Order setting
spark.conf.get('spark.sql.parquet.vorder.default')

# Enable V-Order for read-heavy workloads
spark.conf.set('spark.sql.parquet.vorder.default', 'true')

# Disable V-Order for write-heavy ingestion
spark.conf.set('spark.sql.parquet.vorder.default', 'false')

4. Autotune Configuration

-- Enable autotune for automatic Spark SQL tuning
SET spark.ms.autotune.enabled=TRUE;

-- Disable autotune
SET spark.ms.autotune.enabled=FALSE;

Autotune adjusts three key settings per query: spark.sql.shuffle.partitions, spark.sql.autoBroadcastJoinThreshold, and spark.sql.files.maxPartitionBytes. Requires 20-25 iterations to learn optimal settings. Only works on Runtime 1.1 and 1.2. Not compatible with high concurrency mode or private endpoints.

5. SQL Analytics Endpoint Optimization

For best SQL analytics endpoint performance, target ~2 million rows and ~400 MB per Parquet file:

# Before data changes
spark.conf.set("spark.sql.files.maxRecordsPerFile", 2000000)

# Perform data operations (inserts, updates, deletes)
# ...

# After data changes, set max file size and optimize
spark.conf.set("spark.databricks.delta.optimize.maxFileSize", 4294967296)

Then run OPTIMIZE on the affected tables.

REST API Automation

Automate table maintenance via the Fabric REST API. See rest-api-maintenance.md for full details.

Endpoint:

POST https://api.fabric.microsoft.com/v1/workspaces/{workspaceId}/items/{lakehouseId}/jobs/instances?jobType=TableMaintenance

Request body:

{
  "executionData": {
    "tableName": "my_table",
    "schemaName": "dbo",
    "optimizeSettings": {
      "vOrder": "true",
      "zOrderBy": ["frequently_filtered_column"]
    },
    "vacuumSettings": {
      "retentionPeriod": "7.01:00:00"
    }
  }
}

Monitor status:

GET https://api.fabric.microsoft.com/v1/workspaces/{workspaceId}/items/{lakehouseId}/jobs/instances/{operationId}

Streaming Ingestion Best Practices

When streaming data into a Lakehouse, prevent small file proliferation:

  1. Set processing time triggers to batch events into larger writes
  2. Use Optimized Write (spark.microsoft.delta.optimizeWrite.enabled = true)
  3. Partition wisely — low-cardinality columns only (< 100-200 distinct values)
  4. Schedule periodic OPTIMIZE — daily or more often for high-frequency streams
  5. Combine repartition() with partitionBy() for optimal in-memory and on-disk layout
# Example: Streaming with batching and partitioning
rawData = df \
  .writeStream \
  .format("delta") \
  .option("checkpointLocation", "Files/checkpoint") \
  .outputMode("append") \
  .partitionBy("date_key") \
  .trigger(processingTime="1 minute") \
  .toTable("my_streaming_table")

Available Scripts

  • Invoke-FabricTableMaintenance.ps1 — PowerShell script to automate table maintenance via REST API across multiple tables
  • Get-DeltaTableHealth.ps1 — PowerShell script to assess Delta table health metrics via Fabric REST API

Available Templates

  • maintenance-notebook.py — PySpark notebook template for comprehensive table maintenance
  • perf-diagnostic-notebook.py — PySpark notebook template for diagnosing performance issues

Detailed References

  • Resource Profiles — Complete guide to Fabric Spark resource profile selection and configuration
  • REST API Maintenance — Automating table maintenance with Fabric REST API and PowerShell
  • Delta Table Health — Assessing and monitoring Delta table file layout, V-Order status, and partition health
  • Concurrency and Capacity — Understanding Spark job admission, throttling, burst capacity, and autoscale billing

remediate

IssueCauseFix
OPTIMIZE command not recognizedRunning in SQL analytics endpointUse Fabric notebook with Spark runtime
VACUUM fails with retention errorRetention < 7 days without safety overrideSet spark.databricks.delta.retentionDurationCheck.enabled=false
Autotune not activatingQuery too short (< 15 seconds) or wrong runtimeUse Runtime 1.1/1.2, ensure queries exceed 15s
Table maintenance API returns 409Another maintenance job running on same tableWait for completion, jobs on different tables run in parallel
V-Order not applied after OPTIMIZESession/table property mismatchUse OPTIMIZE table VORDER explicitly
Capacity throttled during maintenanceMaintenance consuming too many coresSchedule during off-peak, reduce concurrent jobs

Key Decision Framework

Is the workload primarily reads or writes?
├── Read-heavy (dashboards, queries, analytics)
│   ├── Power BI Direct Lake → readHeavyForPBI profile + OPTIMIZE VORDER
│   └── Spark analytics → readHeavyForSpark profile + enable autotune
├── Write-heavy (ETL, ingestion, streaming)
│   └── writeHeavy profile + periodic OPTIMIZE + VACUUM on schedule
└── Mixed workload
    ├── Separate environments for read vs write paths
    └── Use runtime spark.conf.set() to switch profiles per notebook

Download now (ZIP)

Does it work?

Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.

No reviews yet

People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.

  • It worked
  • Partly
  • It didn't
What did you use it for? e.g. Reviewing pull requests in a Rails app

Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.

Your trail

Files you open will collect here, so you can find them again.

Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.

Now do this for your own.

Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.