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

fabric-onelake-perf-remediate

Skill17 starsChanged 9 months agoMITView on GitHub

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/write performance, lakehouse query slowness, Direct Lake fallback, table maintenance failures, Spark concurrency limits, warehouse cold starts, or optimizing Delta parquet file layouts. Supports PowerShell, T-SQL, and Spark SQL diagnostic workflows.

The file

Show its rules and text

We split this skill into the rules it gives an agent

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

  • ≈0

    Common · estimate

    Many files like this have a rule of this kind.

  • ≈17

    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 sequence to isolate the root cause:

    line 32
  2. Distinctive:

    When Spark jobs fail with HTTP 430 (TooManyRequestsForCapacity):

    line 46
  3. Distinctive:

    Check active Spark sessions against your SKU's VCore limit (1 CU = 2 Spark VCores)

    line 49
  4. Distinctive:

    When first query execution is significantly slower than subsequent runs:

    line 56
  5. Very specific:

    Check the data_scanned_remote_storage_mb column — non-zero indicates cold start

    line 59
  6. Distinctive:

    Do NOT judge performance on first execution.

    line 60
  7. Distinctive:

    When Delta tables have hundreds or thousands of small Parquet files:

    line 65
  8. Distinctive:

    Run the table-health-check.ps1 script to assess file counts and sizes

    line 67
  9. Distinctive:

    When choosing between read-heavy and write-heavy resource profiles:

    line 74
  10. Distinctive:

    When data in OneLake or external storage is in a different region than Fabric capacity:

    line 84
  11. Distinctive:

    Verify the Fabric capacity region in the Admin portal

    line 86
  12. Distinctive:

    Check shortcut destinations — are they in the same region?

    line 87
  13. Distinctive:

    Keep large fact tables co-located.

    line 89
  14. Distinctive:

    Use the region-latency-test.ps1 script to measure impact

    line 90
  15. Distinctive:

    When Direct Lake models fall back to DirectQuery instead of reading from OneLake:

    line 94
  16. Distinctive:

    Check if the semantic model has been framed (refreshed) recently

    line 96
  17. Distinctive:

    Verify Delta tables are V-Ordered for optimal transcoding

    line 97
  18. Distinctive:

    Check table row counts against the SKU guardrails

    line 98

≈ “Common” is an estimate: at least 1 in 5 of the 350,495 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

OneLake Performance remediate

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.

When to Use This Skill

  • OneLake read or write operations are slow or timing out
  • Lakehouse or warehouse queries have unexpectedly high latency
  • Spark jobs are being throttled with HTTP 430 errors
  • Delta tables have accumulated many small files (small file problem)
  • Direct Lake semantic models are falling back to DirectQuery
  • Cold cache performance is significantly slower than warm cache
  • Cross-region data access is adding network latency
  • V-Order is not applied or needs to be enabled/disabled
  • Table maintenance (OPTIMIZE, VACUUM) is failing or not improving performance
  • Capacity utilization is high and jobs are queuing

Prerequisites

  • Microsoft Fabric workspace with Contributor or higher role
  • Access to the Monitoring Hub in the Fabric portal
  • PowerShell 7+ with Az.Fabric module (for automation scripts)
  • Familiarity with Spark SQL or T-SQL for diagnostic queries

Diagnostic Decision Tree

Follow this sequence to isolate the root cause:

1. Is the issue capacity-level? → Check Spark VCore utilization and queue depth
2. Is the issue cold cache? → Check data_scanned_remote_storage_mb
3. Is the issue file layout? → Check small file count and V-Order status
4. Is the issue cross-region? → Verify data and capacity are co-located
5. Is the issue query design? → Check string column widths, partition pruning

Step-by-Step Workflows

Workflow 1: Diagnose Capacity Throttling

When Spark jobs fail with HTTP 430 (TooManyRequestsForCapacity):

  1. Open the Monitoring Hub in the Fabric portal
  2. Check active Spark sessions against your SKU's VCore limit (1 CU = 2 Spark VCores)
  3. Review the queue depth against your SKU's queue limit (see capacity-sku-reference.md)
  4. Cancel unnecessary jobs or scale up the capacity SKU
  5. For burst workloads, use the spark-capacity-check.ps1 script to monitor utilization

Workflow 2: Resolve Cold Cache Latency

When first query execution is significantly slower than subsequent runs:

  1. Query the queryinsights.exec_requests_history view
  2. Check the data_scanned_remote_storage_mb column — non-zero indicates cold start
  3. Do NOT judge performance on first execution; measure subsequent runs
  4. For pre-warming strategies and diagnostic queries, see cold-cache-diagnostics.md

Workflow 3: Fix Small File Problem

When Delta tables have hundreds or thousands of small Parquet files:

  1. Run the table-health-check.ps1 script to assess file counts and sizes
  2. Apply OPTIMIZE to consolidate files (target: 128 MB–1 GB per file)
  3. Apply V-Order for read-optimized workloads
  4. Schedule recurring maintenance — see table-maintenance-workflow.md

Workflow 4: Optimize V-Order Configuration

When choosing between read-heavy and write-heavy resource profiles:

  1. Identify your dominant workload pattern (ingestion vs. analytics)
  2. New Fabric workspaces default to writeHeavy profile (V-Order disabled)
  3. For Power BI / interactive queries, switch to readHeavyForSpark or readHeavyForPBI
  4. Apply V-Order at session, table, or OPTIMIZE command level
  5. See v-order-decision-guide.md for detailed configuration

Workflow 5: Diagnose Cross-Region Latency

When data in OneLake or external storage is in a different region than Fabric capacity:

  1. Verify the Fabric capacity region in the Admin portal
  2. Check shortcut destinations — are they in the same region?
  3. For ADLS Gen2 or S3 shortcuts, confirm storage account region
  4. Keep large fact tables co-located; small dimension tables tolerate cross-region
  5. Use the region-latency-test.ps1 script to measure impact

Workflow 6: Direct Lake Fallback Investigation

When Direct Lake models fall back to DirectQuery instead of reading from OneLake:

  1. Check if the semantic model has been framed (refreshed) recently
  2. Verify Delta tables are V-Ordered for optimal transcoding
  3. Check table row counts against the SKU guardrails
  4. Review column data types — large string columns degrade performance
  5. See direct-lake-remediate.md

remediate Quick Reference

SymptomLikely CauseFirst Action
HTTP 430 errorsCapacity VCores exhaustedCheck Monitoring Hub, cancel idle sessions
First query very slowCold cache / node resumeCheck data_scanned_remote_storage_mb
All queries slowSmall files / no V-OrderRun table health check script
Queries slow after migrationWrong resource profileSwitch to appropriate read/write profile
Shortcuts slowCross-region data accessVerify region co-location
Direct Lake fallbackTable not framed / too largeCheck framing status and SKU guardrails
VACUUM failsRetention period too shortSet retention >= 7 days
Streaming ingestion slowSchema enforcement overheadConsider Eventhouse with OneLake availability

References

  • Capacity SKU Reference — VCore limits, queue limits, node configurations
  • Cold Cache Diagnostics — T-SQL diagnostic queries and pre-warming
  • Table Maintenance Workflow — OPTIMIZE, VACUUM, and scheduling
  • V-Order Decision Guide — When to enable/disable, resource profiles
  • Direct Lake remediate — Fallback investigation, framing, transcoding

Available Scripts

  • spark-capacity-check.ps1 — Monitor Spark VCore utilization and queue depth
  • table-health-check.ps1 — Assess Delta table file counts, sizes, and V-Order status
  • region-latency-test.ps1 — Measure cross-region OneLake access latency
  • run-table-maintenance.ps1 — Execute table maintenance via Fabric REST API

Templates

  • diagnostic-report.md — Template for documenting performance investigation findings

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.