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

fabric-pyspark-perf-remediate

Skill17 starsChanged 9 months agoMITView on GitHub

Diagnose and resolve Apache Spark performance issues in Microsoft Fabric notebooks and Spark Job Definitions. Use when PySpark jobs are slow, notebooks take too long, Spark stages are skewed, shuffles are excessive, out-of-memory errors occur, Delta Lake writes are slow, or Fabric capacity is throttled. Covers data skew, shuffle optimization, broadcast joins, partition tuning, VOrder, Optimized Write, resource profiles, autotune, native execution engine, small file compaction, and Spark UI interpretation. Keywords include slow notebook, OOM, spill, shuffle, skew, broadcast, repartition, coalesce, OPTIMIZE, VACUUM, Z-ORDER, checkpoint, cache, persist, executor memory, driver memory, spark.sql.shuffle.partitions, autoBroadcastJoinThreshold, maxPartitionBytes, Fabric capacity throttling, CU utilization.

The file

We split this skill into the rules it gives an agent

9 rules. About 0 are common, 8 are distinctive, and 1 is very specific to this project.

  • ≈0

    Common · estimate

    Many files like this have a rule of this kind.

  • ≈8

    Distinctive · estimate

    This author's own choices. Worth a look.

  • 1

    Very specific

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

  • Common
  • Distinctive
  • Very specific
  1. Distinctive:

    Follow this triage sequence to identify the root cause:

    line 35
  2. Distinctive:

    Classify the bottleneck: - Use the decision matrix below to categorize the issue.

    line 39
  3. Distinctive:

    Apply targeted fix: - Follow the relevant reference guide for your bottleneck type.

    line 40
  4. Distinctive:

    Use these indicators to classify your performance issue:

    line 45
  5. Distinctive:

    Small Files / Delta Compaction: Write operations produce thousands of small Parquet files, reads are slow due to file listing overhead, …

    line 53
  6. Distinctive:

    Run these in a Fabric notebook to gather performance data:

    line 108
  7. Distinctive:

    Fix: Check if the Spark session is waiting for cluster allocation.

    line 135
  8. Distinctive:

    Problem: Write operation produces 10,000+ small files.

    line 143
  9. Very specific:

    Fix: Check Delta table history for data volume growth.

    line 147

≈ “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

Microsoft Fabric PySpark Performance remediate

Systematic guide for diagnosing and resolving Apache Spark performance problems in Microsoft Fabric Data Engineering workloads, including notebooks, Spark Job Definitions, and pipeline activities.

When to Use This Skill

Activate when encountering any of these scenarios:

  • PySpark notebook cells take unexpectedly long to execute
  • Spark Job Definitions exceed expected duration or fail with timeouts
  • Out-of-memory (OOM) errors on driver or executors
  • Excessive shuffle read/write in Spark UI stage details
  • Data skew causing individual tasks to run much longer than peers
  • Delta Lake table writes are slow or produce many small files
  • Fabric capacity utilization is high or jobs are queued/throttled
  • Need to choose between resource profiles (readHeavy vs writeHeavy)
  • Deciding whether to enable autotune, native execution engine, or Optimized Write
  • Interpreting Spark UI metrics (stages, tasks, storage, SQL plan)

Prerequisites

  • Access to a Microsoft Fabric workspace with Data Engineering/Science experience
  • Fabric capacity (F2 or higher) with Spark compute enabled
  • Familiarity with PySpark DataFrames and Spark SQL
  • Access to Spark UI via the Monitoring Hub or notebook session details

Quick Diagnostic Workflow

Follow this triage sequence to identify the root cause:

  1. Check capacity status - Is the Fabric capacity throttled or overloaded? See Monitoring Hub for queued jobs and CU utilization.
  2. Identify the slow stage - Open Spark UI, find the stage with the longest duration, and check task-level metrics.
  3. Classify the bottleneck - Use the decision matrix below to categorize the issue.
  4. Apply targeted fix - Follow the relevant reference guide for your bottleneck type.
  5. Validate improvement - Re-run the job and compare Spark UI metrics before and after.

Bottleneck Decision Matrix

Use these indicators to classify your performance issue:

Shuffle Bottleneck: Shuffle read/write bytes are large (>1 GB per stage), many tasks in the stage, high GC time. Fix with broadcast joins, reduced shuffle partitions, or pre-partitioned data. See shuffle-and-join-optimization.md.

Data Skew: One or few tasks take 10x longer than median, input size per task is highly uneven, some tasks process GBs while others process MBs. Fix with salting, repartitioning, or adaptive query execution. See data-skew-resolution.md.

Memory Pressure / OOM: Executor lost errors, "Container killed by YARN for exceeding memory limits", excessive GC time (>10% of task time), disk spill. Fix with memory configuration, caching strategy, or partition sizing. See memory-and-spill-tuning.md.

Small Files / Delta Compaction: Write operations produce thousands of small Parquet files, reads are slow due to file listing overhead, table maintenance never run. Fix with OPTIMIZE, Optimized Write, or streaming trigger intervals. See delta-table-optimization.md.

Capacity / Compute Sizing: Jobs queued for long periods, capacity utilization consistently above 80%, throttling messages in monitoring. Fix with pool sizing, resource profiles, or autoscale billing. See fabric-compute-tuning.md.

Key Spark Configuration Quick Reference

Session-Level Overrides (Notebook Cell 1)

# Shuffle partition count - default is 200, tune to data volume
spark.conf.set("spark.sql.shuffle.partitions", "auto")

# Broadcast join threshold - default 10MB, increase for medium tables
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", "50m")

# Max partition bytes - default 128MB, increase for fewer larger partitions
spark.conf.set("spark.sql.files.maxPartitionBytes", "256m")

# Enable Optimized Write for Delta
spark.conf.set("spark.microsoft.delta.optimizeWrite.enabled", "true")

# Enable autotune (preview, Runtime 1.1/1.2 only)
spark.conf.set("spark.ms.autotune.enabled", "true")

# Enable native execution engine
spark.conf.set("spark.native.enabled", "true")

# VOrder for read-optimized scenarios (Power BI, SQL endpoint)
spark.conf.set("spark.sql.parquet.vorder.default", "true")

Fabric Resource Profiles

ProfileBest ForVOrderKey Behavior
writeHeavyETL, ingestion, streamingOffOptimized throughput for writes (new workspace default)
readHeavyForSparkInteractive queries, EDAOnOptimized for Spark read operations
readHeavyForPBIPower BI, SQL endpointOnOptimized for downstream BI consumption

Resource profiles are set at the environment level in Workspace Settings > Data Engineering/Science > Spark Settings.

Autotune Details

Autotune automatically adjusts three key configurations per query using ML:

SettingDefaultWhat Autotune Optimizes
spark.sql.shuffle.partitions200Partition count for shuffles
spark.sql.autoBroadcastJoinThreshold10 MBBroadcast join size threshold
spark.sql.files.maxPartitionBytes128 MBMax bytes per read partition

Requirements: Runtime 1.1 or 1.2, not compatible with high concurrency mode or private endpoints. Needs 20-25 iterations to learn optimal config. Targets repetitive queries running >15 seconds.

Diagnostic Scripts

Run these in a Fabric notebook to gather performance data:

  • spark_health_check.py - Comprehensive session diagnostics including configuration, pool info, and Delta table stats
  • identify_skew.py - Detect data skew in a DataFrame by analyzing partition sizes
  • delta_table_health.py - Analyze Delta table file sizes, history, and maintenance status

Notebook Templates

  • perf_baseline_template.py - Template for establishing performance baselines with timing instrumentation

Common Anti-Patterns

Collecting large datasets to the driver: df.collect() or df.toPandas() on large DataFrames causes OOM. Use .limit(), .take(), or write to Delta instead.

Using repartition() before write when Optimized Write is available: Optimized Write handles partition optimization automatically, making explicit repartition() calls redundant and expensive.

Never running OPTIMIZE on Delta tables: Over time, append-heavy tables accumulate small files. Schedule OPTIMIZE (and optionally VACUUM) as maintenance.

Caching DataFrames that are used only once: .cache() or .persist() consume memory/storage. Only cache DataFrames referenced multiple times in the same session.

Using Python UDFs instead of native Spark functions: Python UDFs serialize data between JVM and Python, causing major overhead. Prefer pyspark.sql.functions or Pandas UDFs for vectorized operations.

Ignoring partition pruning: Reading Delta tables without filter predicates on partition columns forces full table scans. Always filter on partition columns when possible.

remediate Quick Fixes

Problem: Notebook hangs on first cell. Fix: Check if the Spark session is waiting for cluster allocation. Verify capacity is not fully utilized in Monitoring Hub. Consider starter pools for faster spin-up.

Problem: java.lang.OutOfMemoryError: Java heap space on driver. Fix: Reduce collect() size, increase driver memory in environment settings, or switch to writing results to a table instead of returning to the driver.

Problem: Stage with 200 tasks where 1 task takes 30 minutes and rest take 30 seconds. Fix: Classic data skew. See data-skew-resolution.md for salting and repartitioning techniques.

Problem: Write operation produces 10,000+ small files. Fix: Enable Optimized Write: spark.conf.set("spark.microsoft.delta.optimizeWrite.enabled", "true"). Run OPTIMIZE on the table afterward. See delta-table-optimization.md.

Problem: Job ran fine last week but is slow now. Fix: Check Delta table history for data volume growth. Run DESCRIBE HISTORY table_name and check OPTIMIZE history. Also check if workspace resource profile changed.

References

  • Shuffle and Join Optimization
  • Data Skew Resolution
  • Memory and Spill Tuning
  • Delta Table Optimization
  • Fabric Compute Tuning
  • Spark UI Interpretation Guide

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.