Introduction

Microsoft Fabric provides a unified analytics platform, but performance and cost issues often arise from configuration, data layout, and workload patterns. This guide consolidates the most common optimisation challenges across Fabric workloads—Lakehouse, Data Factory, Warehouse, Eventstreams, Eventhouses, and Spark—and outlines practical remediation approaches.

1. Lakehouse Performance Issues

Common Problem: Small File Problem

Small files degrade query performance due to:

• Increased metadata overhead
• Poor scan efficiency
• Higher latency in Spark and SQL reads

Causes

• Frequent small batch writes
• Streaming ingestion without compaction
• Incremental updates

Optimisation Techniques

• Use OPTIMIZE + ZORDER (Delta tables) to compact files
• Batch writes instead of micro-batching where possible
• Enable auto-compaction in pipelines or notebooks
• Partition data appropriately (avoid over-partitioning)

Schema Optimisation

• Use correct data types (e.g., INT vs STRING)
• Avoid unnecessary high precision (e.g., DECIMAL(38,10) unless required)

Recommended Best Practices

• Design tables for query patterns, not ingestion convenience
• Use Delta Lake features (versioning, compaction)
• Keep file sizes in the ~100MB–1GB range

2. Data Factory Pipeline Bottlenecks

Common Problems

• Slow pipeline execution
• Underutilised compute
• Inefficient parallelism

Root Causes

• Default throughput settings
• Sequential activity execution
• Poor partitioning in copy activities

Optimisation Strategies

1. Throughput Settings

• Increase Data Integration Units (DIUs) where applicable
• Adjust batch size for copy operations

2. Parallelism

• Enable parallel copy
• Split large datasets into partitions
• Use ForEach activities with controlled concurrency

3. Bandwidth Management

• Avoid network throttling by: Using region-aligned resources Leveraging staging storage for large transfers

3. Warehouse Performance (SQL Endpoint)

Common Problems

• Slow queries
• High compute consumption
• Fragmented storage

Key Optimisation Techniques

1. Efficient Table Design

• Use the smallest data types possible
• Avoid over-precision (e.g., BIGINT when INT suffices)

2. Use CTAS (Create Table As Select)

• Rebuild tables for: Better distribution Compression improvements
• Example:

SQL
CREATE TABLE new_table
AS SELECT * FROM old_table;

3. Use OPENROWSET for External Reads

• Avoid unnecessary ingestion when querying external data:

SQL
SELECT * 
FROM OPENROWSET
( BULK 'https://path/file.parquet',
FORMAT = 'PARQUET'
) AS data;

4. Avoid Small Updates

• Frequent small updates create fragmentation and small files
• Prefer: Batch updates Rebuild via CTAS

5. Clone Instead of Copy

• Use zero-copy cloning where possible to reduce duplication and cost

4. Eventstreams Optimisation

Common Problems

• High costs
• Lagging ingestion
• Storage inefficiency

Root Causes

• Excessive retention settings
• Inefficient ingestion configuration

Optimisation Strategies

• Reduce retention period to business-required minimum
• Use direct ingestion where possible instead of staging
• Monitor throughput vs event volume

5. Eventhouse (KQL Database) Optimisation

Common Problem: Poor Query Performance

Key Fix: Hot Cache Utilisation

• Ensure frequently queried data resides in hot cache
• Adjust caching policies for: Time-series data Frequently accessed datasets

Additional Practices

• Optimise ingestion batching
• Use appropriate table policies for retention and caching

6. Spark Performance in Fabric

Common Problems

• Slow notebook execution
• High resource consumption

Optimisation Techniques

1. Enable Adaptive Query Execution (AQE)

• Allows Spark to dynamically optimise joins and partitions

Python
spark.conf.set("spark.sql.adaptive.enabled", "true")

2. Resource Profiles

• The write-heavy profileis intended for workloads that involve frequent data ingestion and updates. It is optimised for high-throughput writes, making it particularly suitable for scenarios like streaming ingestion, batch loading, and Bronze-layer pipelines in a medallion architecture. In this profile, certain optimisations such as VOrder are disabled by default, prioritising write performance and reducing ingestion latency.
• The read-heavy profile for Sparkis tailored for workloads where Spark performs frequent reads and transformations. This includes data preparation, joins, aggregations, and transformation-heavy pipelines often found in Silver-layer processing. The configuration enables optimisations that improve read efficiency and data processing performance within Spark itself.
• The read-heavy profile for Power BIis specifically optimised for serving analytical queries, particularly when Power BI or SQL endpoints are querying Delta tables. This profile enhances read performance and enables features such as VOrder and optimised write settings to improve query latency and overall report responsiveness. It is particularly suited to Gold-layer workloads where data is consumed for reporting and analytics.
• In addition to the predefined profiles, Fabric also supports a custom profile option. This allows you to define your own Spark configuration settings to meet specialised workload requirements. For example, you can tune parameters such as shuffle partitions, adaptive query execution, or serialization to optimise for advanced use cases like machine learning or highly parallel transformations

3. Use Native Execution Engine (NEE)

Especially beneficial for CPU-heavy ML workloads

The Native Execution Engine is based on two key OSS components: Velox, a C++ database acceleration library introduced by Meta, and Apache Gluten (incubating), a middle layer responsible for offloading JVM-based SQL engines’ execution to native engines introduced by Intel. – so careful as it can cause issues.

4. Partitioning Strategy

• Avoid too many small partitions
• Avoid skew in joins

5. Caching and Reuse

• Cache intermediate datasets when reused:

Python
df.cache()

7. General Query Optimisation

Across Fabric services:

• Use predicate pushdown
• Avoid SELECT *
• Filter early
• Use proper indexing/distribution
• Leverage query optimizer hints where appropriate

8. Automating Optimisation with Logic Apps / Power Automate

You can operationalise optimisation using scheduled workflows calling the Fabric REST API.

Example Use Cases

• Trigger Delta table compaction
• Run Spark optimisation notebooks
• Monitor and adjust pipelines
• Refresh hot cache policies

Architecture Overview

1. Scheduler (Logic Apps / Power Automate)
2. Calls Fabric REST API
3. Executes: Notebook jobs SQL scripts Pipeline runs

Example Flow

Step 1: Trigger

• Recurrence (e.g., nightly)

Step 2: HTTP Action

Call Fabric API:

HTTP POST 
URL:
https://api.fabric.microsoft.com/v1/workspaces/{workspaceId}/items/{itemId}/jobs

Header:
Authorization: Bearer <token>
Content-Type: application/json

Body:
{"executionData": 
 {"parameters": 
  {"operation": "optimize"
  }
 }
}

Practical Automation Tasks

• Run: OPTIMIZE on Lakehouse tables Spark notebooks for compaction Warehouse CTAS refresh jobs
• Adjust: Eventstream retention
• Monitor: Pipeline performance trends

Final Thoughts

Optimising Microsoft Fabric requires a holistic approach across storage, compute, and orchestration layers:

• Prevent small files early
• Right-size compute and parallelism
• Design schemas thoughtfully
• Use automation to enforce consistency

By aligning with good practice principles—especially around performance tuning, data layout, and workload optimisation—you can significantly improve both cost efficiency and execution speed.

About the author

N, Radoi (23/06/2026) Optimising Microsoft Fabric: Common Problems and Practical Fixes. (1) Optimising Microsoft Fabric: Common Problems and Practical Fixes | LinkedIn