fabric-lakehouse-perf-remediate

Name: fabric-lakehouse-perf-remediate
Author: PatrickGallucci

16stars1forks

How to install fabric-lakehouse-perf-remediate?

patrickgallucci/fabric-skills/fabric-lakehouse-perf-remediate

$npx -y skills add PatrickGallucci/fabric-skills --skill fabric-lakehouse-perf-remediate

Installs into the current project.

Summary

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

Files · 1

View on GitHub

SKILL.md

1	# Fabric Lakehouse Performance remediate
2
3	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.
4
5	## When to Use This Skill
6
7	- Lakehouse queries are running slowly or timing out
8	- Delta tables have accumulated many small files (small file problem)
9	- Spark notebooks or jobs are underperforming
10	- Direct Lake semantic models have cold-start or transcoding delays
11	- SQL analytics endpoint queries are slow
12	- Table maintenance (OPTIMIZE, VACUUM) needs to be scheduled or automated
13	- V-Order, Z-Order, or resource profile configuration is needed
14	- Capacity throttling or concurrency issues are suspected
15	- Streaming ingestion is creating fragmented Delta tables
16
17	## Prerequisites
18
19	- Microsoft Fabric workspace with Lakehouse items
20	- Contributor or higher workspace role
21	- Fabric capacity (F2 or above) or Trial capacity
22	- For REST API automation: Microsoft Entra token for Fabric service
23	- For Spark commands: Access to Fabric notebooks or Spark Job Definitions
24
25	## Quick Diagnosis Checklist
26
27	When a user reports Lakehouse performance issues, work through these areas in order:
28
29	1. Identify the symptom — Slow reads, slow writes, capacity throttling, or query timeouts
30	2. Check Delta table health — File count, file sizes, V-Order status, partition layout
31	3. Review Spark configuration — Resource profile, autotune, shuffle partitions
32	4. Inspect capacity utilization — Concurrency limits, burst capacity, throttling
33	5. Evaluate maintenance history — When was OPTIMIZE/VACUUM last run?
34	6. Assess data patterns — Streaming vs batch, read-heavy vs write-heavy
35
36	## Symptom-to-Action Map
37
38	\| Symptom \| Root Cause \| Action \|
39	\|---------\|-----------\|--------\|
40	\| Slow reads across all engines \| Small files, no V-Order \| Run OPTIMIZE VORDER, switch to readHeavy profile \|
41	\| Slow Spark queries only \| Wrong shuffle partitions \| Enable autotune or tune manually \|
42	\| Slow Power BI Direct Lake \| Too many Parquet files/row groups \| Run OPTIMIZE, check guardrail limits \|
43	\| Slow SQL analytics endpoint \| Files under 400 MB, too many small files \| OPTIMIZE with maxRecordsPerFile=2M \|
44	\| Write performance degraded \| V-Order enabled on write-heavy workload \| Switch to writeHeavy resource profile \|
45	\| Capacity throttled \| Too many concurrent Spark jobs \| Review concurrency limits, enable optimistic admission \|
46	\| Storage growing unexpectedly \| VACUUM not running \| Schedule VACUUM with 7-day retention \|
47	\| Streaming creates tiny files \| No batching or trigger interval \| Add processingTime trigger, run periodic OPTIMIZE \|
48
49	## Core Optimization Operations
50
51	### 1. Table Maintenance Commands
52
53	Run in a Fabric notebook (Spark SQL):
54
55	```sql
56	-- Basic OPTIMIZE (bin-compaction)
57	OPTIMIZE lakehouse_name.schema_name.table_name;
58
59	-- OPTIMIZE with V-Order
60	OPTIMIZE lakehouse_name.schema_name.table_name VORDER;
61
62	-- OPTIMIZE with Z-Order on frequently filtered columns
63	OPTIMIZE lakehouse_name.schema_name.table_name ZORDER BY (column_name);
64
65	-- OPTIMIZE with both Z-Order and V-Order
66	OPTIMIZE lakehouse_name.schema_name.table_name ZORDER BY (column_name) VORDER;
67
68	-- OPTIMIZE specific partitions only
69	OPTIMIZE lakehouse_name.schema_name.table_name WHERE date_key >= '2025-01-01' VORDER;
70
71	-- VACUUM with default 7-day retention
72	VACUUM lakehouse_name.schema_name.table_name;
73
74	-- VACUUM with custom retention (requires safety check disabled)
75	VACUUM lakehouse_name.schema_name.table_name RETAIN 168 HOURS;
76	```
77
78	### 2. Resource Profile Configuration
79
80	Set at environment level or runtime. See [resource-profiles.md](./references/resource-profiles.md) for details.
81
82	```python
83	# Check current profile
84	spark.conf.get('spark.fabric.resourceProfile')
85
86	# Switch to read-heavy for Spark queries
87	spark.conf.set("spark.fabric.resourceProfile", "readHeavyForSpark")
88
89	# Switch to read-heavy for Power BI Direct Lake
90	spark.conf.set("spark.fabric.resourceProfile", "readHeavyForPBI")
91
92	# Switch to write-heavy for ETL/ingestion
93	spark.conf.set("spark.fabric.resourceProfile", "writeHeavy")
94	```
95
96	### 3. V-Order Control
97
98	```python
99	# Check current V-Order setting
100	spark.conf.get('spark.sql.parquet.vorder.default')
101
102	# Enable V-Order for read-heavy workloads
103	spark.conf.set('sp

People who liked this also viewed

6 picks

Type

fabric-lakehouse-perf-remediate

16stars1forks

View on GitHub

How to install fabric-lakehouse-perf-remediate?

patrickgallucci/fabric-skills/fabric-lakehouse-perf-remediate

$npx -y skills add PatrickGallucci/fabric-skills --skill fabric-lakehouse-perf-remediate

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# Fabric Lakehouse Performance remediate
2
3	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.
4
5	## When to Use This Skill
6
7	- Lakehouse queries are running slowly or timing out
8	- Delta tables have accumulated many small files (small file problem)
9	- Spark notebooks or jobs are underperforming
10	- Direct Lake semantic models have cold-start or transcoding delays
11	- SQL analytics endpoint queries are slow
12	- Table maintenance (OPTIMIZE, VACUUM) needs to be scheduled or automated
13	- V-Order, Z-Order, or resource profile configuration is needed
14	- Capacity throttling or concurrency issues are suspected
15	- Streaming ingestion is creating fragmented Delta tables
16
17	## Prerequisites
18
19	- Microsoft Fabric workspace with Lakehouse items
20	- Contributor or higher workspace role
21	- Fabric capacity (F2 or above) or Trial capacity
22	- For REST API automation: Microsoft Entra token for Fabric service
23	- For Spark commands: Access to Fabric notebooks or Spark Job Definitions
24
25	## Quick Diagnosis Checklist
26
27	When a user reports Lakehouse performance issues, work through these areas in order:
28
29	1. Identify the symptom — Slow reads, slow writes, capacity throttling, or query timeouts
30	2. Check Delta table health — File count, file sizes, V-Order status, partition layout
31	3. Review Spark configuration — Resource profile, autotune, shuffle partitions
32	4. Inspect capacity utilization — Concurrency limits, burst capacity, throttling
33	5. Evaluate maintenance history — When was OPTIMIZE/VACUUM last run?
34	6. Assess data patterns — Streaming vs batch, read-heavy vs write-heavy
35
36	## Symptom-to-Action Map
37
38	\| Symptom \| Root Cause \| Action \|
39	\|---------\|-----------\|--------\|
40	\| Slow reads across all engines \| Small files, no V-Order \| Run OPTIMIZE VORDER, switch to readHeavy profile \|
41	\| Slow Spark queries only \| Wrong shuffle partitions \| Enable autotune or tune manually \|
42	\| Slow Power BI Direct Lake \| Too many Parquet files/row groups \| Run OPTIMIZE, check guardrail limits \|
43	\| Slow SQL analytics endpoint \| Files under 400 MB, too many small files \| OPTIMIZE with maxRecordsPerFile=2M \|
44	\| Write performance degraded \| V-Order enabled on write-heavy workload \| Switch to writeHeavy resource profile \|
45	\| Capacity throttled \| Too many concurrent Spark jobs \| Review concurrency limits, enable optimistic admission \|
46	\| Storage growing unexpectedly \| VACUUM not running \| Schedule VACUUM with 7-day retention \|
47	\| Streaming creates tiny files \| No batching or trigger interval \| Add processingTime trigger, run periodic OPTIMIZE \|
48
49	## Core Optimization Operations
50
51	### 1. Table Maintenance Commands
52
53	Run in a Fabric notebook (Spark SQL):
54
55	```sql
56	-- Basic OPTIMIZE (bin-compaction)
57	OPTIMIZE lakehouse_name.schema_name.table_name;
58
59	-- OPTIMIZE with V-Order
60	OPTIMIZE lakehouse_name.schema_name.table_name VORDER;
61
62	-- OPTIMIZE with Z-Order on frequently filtered columns
63	OPTIMIZE lakehouse_name.schema_name.table_name ZORDER BY (column_name);
64
65	-- OPTIMIZE with both Z-Order and V-Order
66	OPTIMIZE lakehouse_name.schema_name.table_name ZORDER BY (column_name) VORDER;
67
68	-- OPTIMIZE specific partitions only
69	OPTIMIZE lakehouse_name.schema_name.table_name WHERE date_key >= '2025-01-01' VORDER;
70
71	-- VACUUM with default 7-day retention
72	VACUUM lakehouse_name.schema_name.table_name;
73
74	-- VACUUM with custom retention (requires safety check disabled)
75	VACUUM lakehouse_name.schema_name.table_name RETAIN 168 HOURS;
76	```
77
78	### 2. Resource Profile Configuration
79
80	Set at environment level or runtime. See [resource-profiles.md](./references/resource-profiles.md) for details.
81
82	```python
83	# Check current profile
84	spark.conf.get('spark.fabric.resourceProfile')
85
86	# Switch to read-heavy for Spark queries
87	spark.conf.set("spark.fabric.resourceProfile", "readHeavyForSpark")
88
89	# Switch to read-heavy for Power BI Direct Lake
90	spark.conf.set("spark.fabric.resourceProfile", "readHeavyForPBI")
91
92	# Switch to write-heavy for ETL/ingestion
93	spark.conf.set("spark.fabric.resourceProfile", "writeHeavy")
94	```
95
96	### 3. V-Order Control
97
98	```python
99	# Check current V-Order setting
100	spark.conf.get('spark.sql.parquet.vorder.default')
101
102	# Enable V-Order for read-heavy workloads
103	spark.conf.set('sp

People who liked this also viewed

6 picks

Type