Fabric Copy Activity Performance for SQL Databases - What the Numbers Tell You
Most Fabric projects we pick up in Australia start the same way. There is an Azure SQL database or an on-premises SQL Server holding years of transactional data, and the first job is getting it into a Lakehouse or Warehouse. Somebody drags a Copy activity onto a pipeline canvas, points it at the biggest table, presses run and goes to lunch. When they get back it is still going.
The defaults in the Copy activity are safe and slow. That is a reasonable design choice by Microsoft, but it means the gap between a default pipeline and a tuned one is enormous. How enormous? Microsoft published a set of test results in their Copy activity performance with SQL databases article, and the headline is a 1.5 billion row load dropping from over two hours to under seven minutes.
I like this article a lot because it shows actual numbers, including capacity consumption and source database CPU, and those two columns change the story. The fastest option is not always the one you want. Let me walk through what they tested and how we apply it.
The test setup
Microsoft loaded a 1.5 billion row table from Azure SQL Database into both a Fabric Lakehouse and a Fabric Warehouse on an F64 capacity. They built four versions of the source table with the same data: a heap, one with a clustered index, one with 8 physical partitions, and one with 85.
Two caveats before we go further. Microsoft says plainly these are test cases and not formal engineering benchmarks. And the source database was General Purpose serverless with up to 80 vCores, which is far bigger than what most of our clients run. Keep that second point in mind. It matters later.
The baseline
With everything on default (no partition option, parallelism on Auto), the service used a single parallel copy. One connection, one stream of rows.
- Fabric Warehouse: 2 hours 23 minutes
- Fabric Lakehouse: 2 hours 10 minutes
Source database CPU sat below 1% the whole time. The database was practically idle while the pipeline crawled. That is the tell that you are leaving performance on the table.
The partition options
The Copy activity source has a Partition option setting with three choices: None, Physical partitions of table, and Dynamic range. This one setting accounts for most of the difference.
Physical partitions
If the source table is already partitioned in SQL, the service reads each partition in parallel and works out the partition column for you. The number of partitions caps the parallelism.
With 8 partitions the Warehouse load took about 26 minutes. With 85 partitions it took 8 and a half minutes. Source CPU peaked at 3% and 15% respectively, which is very polite to the source system.
Microsoft calls this the most balanced approach, and I agree. The catch is obvious: your table has to be partitioned already, and most operational databases we see are not. Nobody is going to repartition a production ERP table to make a data load faster.
Dynamic range
This is the one you will use most. You pick a column, ideally an integer or date key with an even spread of values, and the service generates range queries against it. Something like:
SELECT * FROM [dbo].[orders]
WHERE [o_orderkey] > '4617187501' AND [o_orderkey] <= '4640625001'
One query per parallel copy. And here is where the results get interesting.
On the heap table with parallelism left on Auto, the service spun up around 250 parallel copies. The load took 39 minutes, the source database hit 100% CPU, and it burned 282,600 capacity units. Then Microsoft capped parallelism at 50. The same load finished in 13 minutes and used 92,159 capacity units.
So fewer parallel copies made it three times faster and three times cheaper. With 250 sessions hammering a heap, the database was so overloaded that queries were sitting suspended waiting for CPU. More is not better. This is the single most useful finding in the article, and it matches what we have seen on client work: Auto parallelism with Dynamic range is a trap when the source table has no supporting index.
Dynamic range with a clustered index
Same test, but the source table has a clustered index on the partition column. Now the range queries are index seeks instead of full scans.
The Lakehouse load took 6 minutes 34 seconds with parallelism at 50. Source CPU peaked at around 10%. Capacity units came in near 55,000, which is barely more than the two hour default run.
That is the best result in the whole article on almost every measure: fastest, close to cheapest, and gentle on the source. If your big tables have a clustered index on an identity or date column, and most do, you are in good shape.
One more tip on bounds
Dynamic range lets you supply upper and lower bounds for the partition column. If you leave them blank the service queries the source to work them out, which adds overhead. For a one-off historical load, run a quick MIN and MAX yourself and paste the values in.
Logical partitioning
There is a fourth pattern that is not a setting at all. You generate a list of date ranges, feed them into a ForEach activity, and run up to 50 separate Copy activities at once, each pulling its own slice.
Microsoft's results: about 12 minutes to Warehouse, 9 to Lakehouse. Quick, but it consumed 226,108 capacity units on the Warehouse test and pushed source CPU to 91%. It is also more to build and maintain.
We keep this pattern for cases where the built-in options are not available or not enough. On-premises Oracle sources are the classic example, and the article mentions the design came from exactly that situation, where a 180 GB load went from 65 hours to under 90 minutes. For Azure SQL with a decent index, Dynamic range gets you there with far less plumbing.
Speed is only one column
Here is a cut of the Warehouse results that I find more useful than duration alone:
| Approach | Duration | Capacity units | Source max CPU |
|---|---|---|---|
| Default | 2h 23m | 51,839 | under 1% |
| Physical, 8 partitions | 26m | 49,320 | 3% |
| Physical, 85 partitions | 8m 31s | 108,000 | 15% |
| Dynamic range, heap, Auto | 39m | 282,600 | 100% |
| Dynamic range, heap, 50 | 13m | 92,159 | 81% |
| Dynamic range, clustered index, 50 | 8m 38s | 55,440 | 10% |
| Logical design | 12m | 226,108 | 91% |
Fabric capacity is a shared pool. A pipeline that chews through 280,000 capacity units on an F64 is taking that away from your Power BI users and every other workload. On a smaller capacity it can tip you into throttling. So "how fast" is the wrong first question. The right one is "what am I optimising for?", and Microsoft says as much up front.
The Intelligent throughput optimisation setting (ITO) is your cost dial. It caps the CPU, memory and network the activity can use, with presets of Standard (64), Balanced (128) and Maximum (256), or a custom value from 4 to 256. In Microsoft's test, halving ITO made the load 34% slower and cut capacity usage by about 45%. For an overnight load where nobody cares whether it finishes at 1:15am or 1:20am, that is free money.
What I'd watch out for
Your source is probably smaller than theirs. Fifty parallel sessions against an 80 vCore database is fine. Fifty against a 4 vCore database that is also serving your line-of-business app at 10am is an outage. Start at 4 or 8, watch the source CPU and session counts, and work up. And talk to the DBA before you run it, not after.
Isolation level has a cost. With Dynamic range at 50 copies, setting Read Committed nearly doubled the duration compared with None or Read Uncommitted (about 25 minutes versus 14). Microsoft also warns against combining an explicit isolation level with Auto parallelism, as it risks overloading the source. Treat isolation level as a correctness requirement, and only set it if you have one.
Warehouse sinks need staging. When the destination is a Fabric Warehouse, parallelism and ITO only apply from source to staging. Factor that in when comparing against Lakehouse numbers.
Keep everything in one region. Microsoft's tests moved data from East US 2 to West US 2 and they note that same-region copies perform better. For Australian organisations that means keeping your Fabric capacity and your SQL source together, typically both in Australia East. We have seen pipelines pulling from Australia Southeast into a capacity elsewhere, and the fix was an architecture conversation, not a pipeline setting.
The article has a few rough edges. Some tables quote the Warehouse baseline against Lakehouse rows, and the parallel copy count for the heap test is 251 in one place and 242 in another. It doesn't change the conclusions, but don't quote the numbers to three significant figures in a design document.
The defaults are sometimes fine
One line in Microsoft's guidelines deserves more attention than it gets: running with the defaults might be the best option, especially if you don't know the source table's structure.
I agree. If a table has 2 million rows and loads in 90 seconds, leave it alone. Tuning has a maintenance cost, and a pipeline with forty hand-tuned Copy activities is harder to own than one with forty default ones. Our usual approach is to run everything on defaults first, sort by duration, and tune the top handful of tables. That is usually three to five tables carrying 90% of the runtime.
My short version
- Table already partitioned with plenty of partitions? Use Physical partitions.
- Clustered index on a well-distributed key? Dynamic range, parallelism set explicitly, bounds supplied.
- Heap or no useful index? Dynamic range still helps, but cap parallelism low and watch the source.
- Still not fast enough? Logical partitioning with ForEach, and budget the capacity for it.
- Optimising for cost? Turn ITO down and accept a slightly longer run.
If you are planning a migration into Fabric and want help sizing pipelines and capacity before the bills arrive, that is the work our Data Factory consultants and wider Microsoft Fabric consulting team do every week. You can reach us here if you'd like to talk through your setup.