Does Microsoft Fabric Apply Automatic Backend Batching When Copying Data from SQL Server?

Hi pallaravikiran9,

Hope you're doing well!

This is my recommandationfor your use case :

Source: SQL Server (billions of rows)
Target: Fabric Lakehouse

- Use Dynamic Range Partitioning on a numeric ID column
- Set explicit partition count (start at 8, tune from there)
- Set DIUs to 16–32 for large tables
- Consider staging to Parquet in Lakehouse, then processing with Spark
- Don't rely on default single-stream for billion-row tables
- Add retry policies on the Copy Activity for resilience

Here's the docs : 

1. https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-performance

2. https://learn.microsoft.com/en-us/azure/data-factory/connector-sql-server

3. https://learn.microsoft.com/en-us/fabric/data-factory/copy-job-overview

Hope this helps. Feel free to ask me questions if needed, and don’t forget to Accept as Solution if this guidance worked for you. That's motivate me to keep helping.

Best regards,

Oussama (Data Consultant & Fabric's Expert)

Hey pallaravikiran9,

Appreciate the question structure; very helpful.

By default, Fabric doesn't auto-batch large queries, intead a single thread is used. For 1.5 billion records, default settings take around 2 hours to run. So, for larger datasets we should explicitly enable parallelism.

To enable parallel reads, in the Copy Activity, go to Source > Advanced > Partition option, choose:

• Physical partitions of table. If your SQL Server table already has partitions defined. Zero config, Fabric auto-detects them. Best choice when available.
• Dynamic range. If no physical partitions exist. Fabric splits by a numeric/date column. Fabric auto-detects them, if you don't specify.
• Then set Degree of Copy Parallelism in the Settings tab. Start with 4–8 and tune from there.

Physical partitions give the most balanced result across duration, capacity units, and source compute overhead.

Give this Microsoft doc a read, it answers all of your queries in brief along with real test results by developers: https://learn.microsoft.com/en-us/fabric/data-factory/copy-performance-sql-databases

There is one more option "Enable Staging", but it is relevant for Fabric Warehouses. For your SQL Server to Lakehouse setup, this option is not required.

Hope this helps!

Best,

Harshit

hi pallaravikiran9 great question, let me try to answer it in "chunks":

1. When copying data from SQL Server using a Copy Job activity, does Fabric automatically split large result sets into internal batches (for example, by primary key or row ranges) to improve performance?
   RA: Fabric has the capability (for SQL Server connector) to chunk a large SQL Server query into multiple SQL statements. This is supported by "Partition option" in the built-in configuration of SQL Server connector. This is not done automatically.
   https://learn.microsoft.com/en-us/fabric/data-factory/connector-sql-server-copy-activity
   

    

2. If a SQL query returns a very large number of rows (millions or more), does Fabric execute the query once and stream all rows, or internally generate multiple SQL executions to optimise throughput?
   RA: Depends if partitioned copy is enabled or not, assuming it is enabled, you can set "Degree of copy parallelism" as well which allows for multiple SQL executions.
   https://learn.microsoft.com/en-us/fabric/data-factory/connector-sql-server-copy-activity
   
   

3. From a performance and scalability perspective, Is it recommended / required to explicitly design batching at the pipeline level (for example using parameterised queries with ForEach loops)? Or does Fabric already handle this optimisation internally for SQL Server sources?
   RA: If your goal is high throughput for one large table (e.g. table size > ~50GB or rows > ~100M), I would not consider batching at pipeline level (just enable partitioning + parallel copy). 
   Pipeline-level batching (ForEach, parameterized WHERE clauses) is an additional layer if you need more control or want to orchestrate multiple copy operations concurrently. 
   https://learn.microsoft.com/en-us/fabric/data-factory/copy-activity-performance-and-scalability-guide
   
   

4. Are there any official best‑practice recommendations from the Fabric team regarding Batch size, ID‑based vs Date‑based batching and Concurrency settings when using Copy Job with SQL Server?
   RA: For batch size, I don't think there is a “read batch size” concept for SQL Server source. Batch size is only relevant for writes (destination) and this is managed automatically by Fabric.
   Regarding ID-based vs Date-based batching, prioritise dynamic partitioning with integer columns (int, bigint, etc.), then date / datetime columns (if integer columns don't exist). Please use a well-distributed column (avoid skew).
   For concurrency settings, there are 3 levers to play with:
   - Degree of copy parallelism = number of parallel threads / SQL queries
   - Partitioning = multiple parallel reads from SQL Server

   - Pipeline-level batching = multiple Copy activities in parallel (ForEach)
   https://learn.microsoft.com/en-us/fabric/data-factory/connector-sql-database-copy-activity

Appreciate if you can 'Kudos' and/or 'Accept as Solution' if this answered your query.