Applies toAzure Data Factory, Synapse pipelines, Fabric Data Factory copy activity
1. Read the duration breakdown
In Monitor → pipeline run → Copy activity → details (glasses icon):
| Stage | Large value means |
|---|---|
| Queue | Waiting for an integration runtime: a busy self-hosted IR or concurrency limits |
| Pre-copy script | Sink pre-copy SQL (for example a TRUNCATE) is slow or blocked |
| Time to first byte | Source query is slow to start (no index, complex view) |
| Reading from source | Source throughput or single-threaded reading |
| Writing to sink | Sink throughput: indexes, triggers, small batches, throttling |
The panel also shows used DIUs, parallel copies and throughput. Often there's a tip in the Performance tuning tips section.
2. Source-side fixes
- Enable partitioned reads for databases:
- SQL sources: Physical partitions of table or Dynamic range on an indexed numeric or date column.
- Oracle, SAP and others have similar partition options.
- Push filters into the source query instead of copying everything and filtering later.
- For files, copy many medium files (100 MB–1 GB) rather than a single huge file or millions of tiny ones.
3. Sink-side fixes
- Azure SQL / SQL Server: use a staging table (heap, no indexes) plus a stored procedure
MERGE, rather than row-by-row upsert. RaisewriteBatchSizefor small rows. - Synapse dedicated pool: use COPY statement or PolyBase with staging enabled. Bulk insert is much slower.
- Storage / Data Lake: write Parquet and avoid one file per row group.
- Watch for throttling on the sink (DTU/vCore limits, Cosmos DB RU/s).
4. Scale settings
| Setting | Applies to | Notes |
|---|---|---|
| DIUs | Azure IR (cloud-to-cloud) | Default is Auto. Raise it for large file copies. |
| Degree of copy parallelism | Both | Higher helps partitioned sources and many files |
| Self-hosted IR nodes | Self-hosted | Scale out (up to 4 nodes) and give nodes CPU and network |
| Concurrent jobs per SHIR node | Self-hosted | Raise only if the node's CPU and memory aren't saturated |
5. Self-hosted IR bottlenecks
On the IR machine during a copy:
Get-Counter '\Processor(_Total)\% Processor Time','\Memory\Available MBytes','\Network Interface(*)\Bytes Total/sec' -SampleInterval 5 -MaxSamples 6
CPU pinned → scale out, or move to a bigger VM. Network saturated → check the VPN/ExpressRoute bandwidth, or compress (Parquet/gzip) before moving data.
6. Staged copy
For on-premises → Synapse/Snowflake, or across regions, turn on staging (an interim Blob/ADLS account). It enables the fast bulk-load path and compresses transfer over slow links.
Tip
Change one setting at a time and rerun with the same data volume. Otherwise you won't know which change helped, and DIUs you don't need cost money on every run.