fabric-onelake-perf-remediate

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

16stars1forks

How to install fabric-onelake-perf-remediate?

patrickgallucci/fabric-skills/fabric-onelake-perf-remediate

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

Installs into the current project.

Summary

Diagnose and resolve Microsoft Fabric OneLake performance issues including slow queries, cold cache latency, small file problems, Delta table fragmentation, V-Order optimization, Spark throttling, capacity SKU sizing, and cross-region data access. Use when remediate OneLake read/

Files · 1

View on GitHub

SKILL.md

1	# OneLake Performance remediate
2
3	Systematic diagnostic and remediation toolkit for Microsoft Fabric OneLake performance issues. Covers the full stack from capacity-level throttling down to individual Delta table file layout problems.
4
5	## When to Use This Skill
6
7	- OneLake read or write operations are slow or timing out
8	- Lakehouse or warehouse queries have unexpectedly high latency
9	- Spark jobs are being throttled with HTTP 430 errors
10	- Delta tables have accumulated many small files (small file problem)
11	- Direct Lake semantic models are falling back to DirectQuery
12	- Cold cache performance is significantly slower than warm cache
13	- Cross-region data access is adding network latency
14	- V-Order is not applied or needs to be enabled/disabled
15	- Table maintenance (OPTIMIZE, VACUUM) is failing or not improving performance
16	- Capacity utilization is high and jobs are queuing
17
18	## Prerequisites
19
20	- Microsoft Fabric workspace with Contributor or higher role
21	- Access to the Monitoring Hub in the Fabric portal
22	- PowerShell 7+ with Az.Fabric module (for automation scripts)
23	- Familiarity with Spark SQL or T-SQL for diagnostic queries
24
25	## Diagnostic Decision Tree
26
27	Follow this sequence to isolate the root cause:
28
29	```
30	1. Is the issue capacity-level? → Check Spark VCore utilization and queue depth
31	2. Is the issue cold cache? → Check data_scanned_remote_storage_mb
32	3. Is the issue file layout? → Check small file count and V-Order status
33	4. Is the issue cross-region? → Verify data and capacity are co-located
34	5. Is the issue query design? → Check string column widths, partition pruning
35	```
36
37	## Step-by-Step Workflows
38
39	### Workflow 1: Diagnose Capacity Throttling
40
41	When Spark jobs fail with HTTP 430 (TooManyRequestsForCapacity):
42
43	1. Open the Monitoring Hub in the Fabric portal
44	2. Check active Spark sessions against your SKU's VCore limit (1 CU = 2 Spark VCores)
45	3. Review the queue depth against your SKU's queue limit (see [capacity-sku-reference.md](./references/capacity-sku-reference.md))
46	4. Cancel unnecessary jobs or scale up the capacity SKU
47	5. For burst workloads, use the [spark-capacity-check.ps1](./scripts/spark-capacity-check.ps1) script to monitor utilization
48
49	### Workflow 2: Resolve Cold Cache Latency
50
51	When first query execution is significantly slower than subsequent runs:
52
53	1. Query the `queryinsights.exec_requests_history` view
54	2. Check the `data_scanned_remote_storage_mb` column — non-zero indicates cold start
55	3. Do NOT judge performance on first execution; measure subsequent runs
56	4. For pre-warming strategies and diagnostic queries, see [cold-cache-diagnostics.md](./references/cold-cache-diagnostics.md)
57
58	### Workflow 3: Fix Small File Problem
59
60	When Delta tables have hundreds or thousands of small Parquet files:
61
62	1. Run the [table-health-check.ps1](./scripts/table-health-check.ps1) script to assess file counts and sizes
63	2. Apply OPTIMIZE to consolidate files (target: 128 MB–1 GB per file)
64	3. Apply V-Order for read-optimized workloads
65	4. Schedule recurring maintenance — see [table-maintenance-workflow.md](./references/table-maintenance-workflow.md)
66
67	### Workflow 4: Optimize V-Order Configuration
68
69	When choosing between read-heavy and write-heavy resource profiles:
70
71	1. Identify your dominant workload pattern (ingestion vs. analytics)
72	2. New Fabric workspaces default to `writeHeavy` profile (V-Order disabled)
73	3. For Power BI / interactive queries, switch to `readHeavyForSpark` or `readHeavyForPBI`
74	4. Apply V-Order at session, table, or OPTIMIZE command level
75	5. See [v-order-decision-guide.md](./references/v-order-decision-guide.md) for detailed configuration
76
77	### Workflow 5: Diagnose Cross-Region Latency
78
79	When data in OneLake or external storage is in a different region than Fabric capacity:
80
81	1. Verify the Fabric capacity region in the Admin portal
82	2. Check shortcut destinations — are they in the same region?
83	3. For ADLS Gen2 or S3 shortcuts, confirm storage account region
84	4. Keep large fact tables co-located; small dimension tables tolerate cross-region
85	5. Use the [region-latency-test.ps1](./scripts/region-latency-test.ps1) script to measure impact
86
87	### Workflow 6: Direct Lake Fallback Investigation
88
89	When Direct Lake models fall back to DirectQuery instead of reading from OneLake:
90
91	1. Check if the semantic model has been framed (refreshed) recently
92	2. Verify Delta tables are V-Ordered for optimal transcoding
93	3.

People who liked this also viewed

6 picks

Type

fabric-onelake-perf-remediate

16stars1forks

View on GitHub

How to install fabric-onelake-perf-remediate?

patrickgallucci/fabric-skills/fabric-onelake-perf-remediate

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

Installs into the current project.

Summary

Files · 1

View on GitHub

SKILL.md

1	# OneLake Performance remediate
2
3	Systematic diagnostic and remediation toolkit for Microsoft Fabric OneLake performance issues. Covers the full stack from capacity-level throttling down to individual Delta table file layout problems.
4
5	## When to Use This Skill
6
7	- OneLake read or write operations are slow or timing out
8	- Lakehouse or warehouse queries have unexpectedly high latency
9	- Spark jobs are being throttled with HTTP 430 errors
10	- Delta tables have accumulated many small files (small file problem)
11	- Direct Lake semantic models are falling back to DirectQuery
12	- Cold cache performance is significantly slower than warm cache
13	- Cross-region data access is adding network latency
14	- V-Order is not applied or needs to be enabled/disabled
15	- Table maintenance (OPTIMIZE, VACUUM) is failing or not improving performance
16	- Capacity utilization is high and jobs are queuing
17
18	## Prerequisites
19
20	- Microsoft Fabric workspace with Contributor or higher role
21	- Access to the Monitoring Hub in the Fabric portal
22	- PowerShell 7+ with Az.Fabric module (for automation scripts)
23	- Familiarity with Spark SQL or T-SQL for diagnostic queries
24
25	## Diagnostic Decision Tree
26
27	Follow this sequence to isolate the root cause:
28
29	```
30	1. Is the issue capacity-level? → Check Spark VCore utilization and queue depth
31	2. Is the issue cold cache? → Check data_scanned_remote_storage_mb
32	3. Is the issue file layout? → Check small file count and V-Order status
33	4. Is the issue cross-region? → Verify data and capacity are co-located
34	5. Is the issue query design? → Check string column widths, partition pruning
35	```
36
37	## Step-by-Step Workflows
38
39	### Workflow 1: Diagnose Capacity Throttling
40
41	When Spark jobs fail with HTTP 430 (TooManyRequestsForCapacity):
42
43	1. Open the Monitoring Hub in the Fabric portal
44	2. Check active Spark sessions against your SKU's VCore limit (1 CU = 2 Spark VCores)
45	3. Review the queue depth against your SKU's queue limit (see [capacity-sku-reference.md](./references/capacity-sku-reference.md))
46	4. Cancel unnecessary jobs or scale up the capacity SKU
47	5. For burst workloads, use the [spark-capacity-check.ps1](./scripts/spark-capacity-check.ps1) script to monitor utilization
48
49	### Workflow 2: Resolve Cold Cache Latency
50
51	When first query execution is significantly slower than subsequent runs:
52
53	1. Query the `queryinsights.exec_requests_history` view
54	2. Check the `data_scanned_remote_storage_mb` column — non-zero indicates cold start
55	3. Do NOT judge performance on first execution; measure subsequent runs
56	4. For pre-warming strategies and diagnostic queries, see [cold-cache-diagnostics.md](./references/cold-cache-diagnostics.md)
57
58	### Workflow 3: Fix Small File Problem
59
60	When Delta tables have hundreds or thousands of small Parquet files:
61
62	1. Run the [table-health-check.ps1](./scripts/table-health-check.ps1) script to assess file counts and sizes
63	2. Apply OPTIMIZE to consolidate files (target: 128 MB–1 GB per file)
64	3. Apply V-Order for read-optimized workloads
65	4. Schedule recurring maintenance — see [table-maintenance-workflow.md](./references/table-maintenance-workflow.md)
66
67	### Workflow 4: Optimize V-Order Configuration
68
69	When choosing between read-heavy and write-heavy resource profiles:
70
71	1. Identify your dominant workload pattern (ingestion vs. analytics)
72	2. New Fabric workspaces default to `writeHeavy` profile (V-Order disabled)
73	3. For Power BI / interactive queries, switch to `readHeavyForSpark` or `readHeavyForPBI`
74	4. Apply V-Order at session, table, or OPTIMIZE command level
75	5. See [v-order-decision-guide.md](./references/v-order-decision-guide.md) for detailed configuration
76
77	### Workflow 5: Diagnose Cross-Region Latency
78
79	When data in OneLake or external storage is in a different region than Fabric capacity:
80
81	1. Verify the Fabric capacity region in the Admin portal
82	2. Check shortcut destinations — are they in the same region?
83	3. For ADLS Gen2 or S3 shortcuts, confirm storage account region
84	4. Keep large fact tables co-located; small dimension tables tolerate cross-region
85	5. Use the [region-latency-test.ps1](./scripts/region-latency-test.ps1) script to measure impact
86
87	### Workflow 6: Direct Lake Fallback Investigation
88
89	When Direct Lake models fall back to DirectQuery instead of reading from OneLake:
90
91	1. Check if the semantic model has been framed (refreshed) recently
92	2. Verify Delta tables are V-Ordered for optimal transcoding
93	3.

People who liked this also viewed

6 picks

Type