--- name: fabric-pyspark-perf-remediate-e6d9d1a9df0b description: 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. license: Complete terms in LICENSE.txt --- # 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](./references/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](./references/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](./references/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](./references/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](./references/fabric-compute-tuning.md). ## Key Spark Configuration Quick Reference ### Session-Level Overrides (Notebook Cell 1) ```python # 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 | Profile | Best For | VOrder | Key Behavior | |---------|----------|--------|--------------| | `writeHeavy` | ETL, ingestion, streaming | Off | Optimized throughput for writes (new workspace default) | | `readHeavyForSpark` | Interactive queries, EDA | On | Optimized for Spark read operations | | `readHeavyForPBI` | Power BI, SQL endpoint | On | Optimized 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: | Setting | Default | What Autotune Optimizes | |---------|---------|------------------------| | `spark.sql.shuffle.partitions` | 200 | Partition count for shuffles | | `spark.sql.autoBroadcastJoinThreshold` | 10 MB | Broadcast join size threshold | | `spark.sql.files.maxPartitionBytes` | 128 MB | Max 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](./scripts/spark_health_check.py) - Comprehensive session diagnostics including configuration, pool info, and Delta table stats - [identify_skew.py](./scripts/identify_skew.py) - Detect data skew in a DataFrame by analyzing partition sizes - [delta_table_health.py](./scripts/delta_table_health.py) - Analyze Delta table file sizes, history, and maintenance status ## Notebook Templates - [perf_baseline_template.py](./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](./references/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](./references/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](./references/shuffle-and-join-optimization.md) - [Data Skew Resolution](./references/data-skew-resolution.md) - [Memory and Spill Tuning](./references/memory-and-spill-tuning.md) - [Delta Table Optimization](./references/delta-table-optimization.md) - [Fabric Compute Tuning](./references/fabric-compute-tuning.md) - [Spark UI Interpretation Guide](./references/spark-ui-guide.md) --- Source attribution: PatrickGallucci Original project: https://github.com/PatrickGallucci/fabric-skills License: Apache-2.0 License information: https://www.apache.org/licenses/LICENSE-2.0 Original upstream instructions; downloaded from Mega AI Hub. ## Upstream license notice Apache License Version 2.0, January 2004 http://www.apache.org/licenses/ TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION 1. Definitions. "License" shall mean the terms and conditions for use, reproduction, and distribution as defined by Sections 1 through 9 of this document. "Licensor" shall mean the copyright owner or entity authorized by the copyright owner that is granting the License. "Legal Entity" shall mean the union of the acting entity and all other entities that control, are controlled by, or are under common control with that entity. For the purposes of this definition, "control" means (i) the power, direct or indirect, to cause the direction or management of such entity, whether by contract or otherwise, or (ii) ownership of fifty percent (50%) or more of the outstanding shares, or (iii) beneficial ownership of such entity. "You" (or "Your") shall mean an individual or Legal Entity exercising permissions granted by this License. "Source" form shall mean the preferred form for making modifications, including but not limited to software source code, documentation source, and configuration files. "Object" form shall mean any form resulting from mechanical transformation or translation of a Source form, including but not limited to compiled object code, generated documentation, and conversions to other media types. "Work" shall mean the work of authorship, whether in Source or Object form, made available under the License, as indicated by a copyright notice that is included in or attached to the work (an example is provided in the Appendix below). "Derivative Works" shall mean any work, whether in Source or Object form, that is based on (or derived from) the Work and for which the editorial revisions, annotations, elaborations, or other modifications represent, as a whole, an original work of authorship. For the purposes of this License, Derivative Works shall not include works that remain separable from, or merely link (or bind by name) to the interfaces of, the Work and Derivative Works thereof. "Contribution" shall mean any work of authorship, including the original version of the Work and any modifications or additions to that Work or Derivative Works thereof, that is intentionally submitted to Licensor for inclusion in the Work by the copyright owner or by an individual or Legal Entity authorized to submit on behalf of the copyright owner. For the purposes of this definition, "submitted" means any form of electronic, verbal, or written communication sent to the Licensor or its representatives, including but not limited to communication on electronic mailing lists, source code control systems, and issue tracking systems that are managed by, or on behalf of, the Licensor for the purpose of discussing and improving the Work, but excluding communication that is conspicuously marked or otherwise designated in writing by the copyright owner as "Not a Contribution." "Contributor" shall mean Licensor and any individual or Legal Entity on behalf of whom a Contribution has been received by Licensor and subsequently incorporated within the Work. 2. Grant of Copyright License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable copyright license to reproduce, prepare Derivative Works of, publicly display, publicly perform, sublicense, and distribute the Work and such Derivative Works in Source or Object form. 3. Grant of Patent License. Subject to the terms and conditions of this License, each Contributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-free, irrevocable (except as stated in this section) patent license to make, have made, use, offer to sell, sell, import, and otherwise transfer the Work, where such license applies only to those patent claims licensable by such Contributor that are necessarily infringed by their Contribution(s) alone or by combination of their Contribution(s) with the Work to which such Contribution(s) was submitted. If You institute patent litigation against any entity (including a cross-claim or counterclaim in a lawsuit) alleging that the Work or a Contribution incorporated within the Work constitutes direct or contributory patent infringement, then any patent licenses granted to You under this License for that Work shall terminate as of the date such litigation is filed. 4. Redistribution. You may reproduce and distribute copies of the Work or Derivative Works thereof in any medium, with or without modifications, and in Source or Object form, provided that You meet the following conditions: (a) You must give any other recipients of the Work or Derivative Works a copy of this License; and (b) You must cause any modified files to carry prominent notices stating that You changed the files; and (c) You must retain, in the Source form of any Derivative Works that You distribute, all copyright, patent, trademark, and attribution notices from the Source form of the Work, excluding those notices that do not pertain to any part of the Derivative Works; and (d) If the Work includes a "NOTICE" text file as part of its distribution, then any Derivative Works that You distribute must include a readable copy of the attribution notices contained within such NOTICE file, excluding those notices that do not pertain to any part of the Derivative Works, in at least one of the following places: within a NOTICE text file distributed as part of the Derivative Works; within the Source form or documentation, if provided along with the Derivative Works; or, within a display generated by the Derivative Works, if and wherever such third-party notices normally appear. The contents of the NOTICE file are for informational purposes only and do not modify the License. You may add Your own attribution notices within Derivative Works that You distribute, alongside or as an addendum to the NOTICE text from the Work, provided that such additional attribution notices cannot be construed as modifying the License. You may add Your own copyright statement to Your modifications and may provide additional or different license terms and conditions for use, reproduction, or distribution of Your modifications, or for any such Derivative Works as a whole, provided Your use, reproduction, and distribution of the Work otherwise complies with the conditions stated in this License. 5. Submission of Contributions. Unless You explicitly state otherwise, any Contribution intentionally submitted for inclusion in the Work by You to the Licensor shall be under the terms and conditions of this License, without any additional terms or conditions. Notwithstanding the above, nothing herein shall supersede or modify the terms of any separate license agreement you may have executed with Licensor regarding such Contributions. 6. Trademarks. This License does not grant permission to use the trade names, trademarks, service marks, or product names of the Licensor, except as required for reasonable and customary use in describing the origin of the Work and reproducing the content of the NOTICE file. 7. Disclaimer of Warranty. Unless required by applicable law or agreed to in writing, Licensor provides the Work (and each Contributor provides its Contributions) on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied, including, without limitation, any warranties or conditions of TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or FITNESS FOR A PARTICULAR PURPOSE. You are solely responsible for determining the appropriateness of using or redistributing the Work and assume any risks associated with Your exercise of permissions under this License. 8. Limitation of Liability. In no event and under no legal theory, whether in tort (including negligence), contract, or otherwise, unless required by applicable law (such as deliberate and grossly negligent acts) or agreed to in writing, shall any Contributor be liable to You for damages, including any direct, indirect, special, incidental, or consequential damages of any character arising as a result of this License or out of the use or inability to use the Work (including but not limited to damages for loss of goodwill, work stoppage, computer failure or malfunction, or any and all other commercial damages or losses), even if such Contributor has been advised of the possibility of such damages. 9. Accepting Warranty or Additional Liability. While redistributing the Work or Derivative Works thereof, You may choose to offer, and charge a fee for, acceptance of support, warranty, indemnity, or other liability obligations and/or rights consistent with this License. However, in accepting such obligations, You may act only on Your own behalf and on Your sole responsibility, not on behalf of any other Contributor, and only if You agree to indemnify, defend, and hold each Contributor harmless for any liability incurred by, or claims asserted against, such Contributor by reason of your accepting any such warranty or additional liability. END OF TERMS AND CONDITIONS APPENDIX: How to apply the Apache License to your work. To apply the Apache License to your work, attach the following boilerplate notice, with the fields enclosed by brackets "[]" replaced with your own identifying information. (Don't include the brackets!) The text should be enclosed in the appropriate comment syntax for the file format. We also recommend that a file or class name and description of purpose be included on the same "printed page" as the copyright notice for easier identification within third-party archives. Copyright [yyyy] [name of copyright owner] Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License. # Mega AI Hub resource metadata Creator / publisher: PatrickGallucci Original project: https://github.com/PatrickGallucci/fabric-skills Attribution basis: Upstream repository explicitly recorded in package mirror path Fields of work: Data & Analytics, Development & Engineering, Operations, Product Management Roles: Data Analyst, Data Engineer, ML / AI Engineer, Operations Manager, Product Manager Specialties: API, Backend Tools: Apache Spark, Microsoft Fabric Country applicability: Country not specified Classification basis: Source catalog labels Instruction SHA-256: 01b79390e64280f64b880f3e884a4fe2d244cd0cd49e38a9db6f355ea3de5bf2