ADF Performance Tuning: DIUs and Parallelism

A Copy activity is taking two hours, and the Settings tab has a box labelled Data Integration Units. Doubling it is tempting. Sometimes that halves the run time. Often it changes nothing except the bill. ADF performance tuning starts with knowing which of those you are about to get.

The ceiling comes first

Microsoft's performance guide states the principle plainly: overall throughput is bounded by the minimum of three things, the source data store, the destination data store, and the network bandwidth between them (Microsoft Learn: performance and scalability guide). No setting in Data Factory raises that ceiling.

The guide includes a table of copy duration by data size and bandwidth. It is simple arithmetic, and the first part of the script below recomputes sixteen of its cells and compares them with the published figures.

# Part 1: recompute Microsoft's table of copy duration by data size and bandwidth
SIZES_GB = {"10 GB": 10, "100 GB": 100, "1 TB": 1024, "10 TB": 10 * 1024}
BANDS_MBPS = {"50 Mbps": 50, "100 Mbps": 100, "500 Mbps": 500, "1 Gbps": 1024}
PUBLISHED = {                      # the same cells as printed in Microsoft's performance guide
    "10 GB":  ["27.3 min", "13.7 min", "2.7 min", "1.3 min"],
    "100 GB": ["4.6 hrs", "2.3 hrs", "0.5 hrs", "0.2 hrs"],
    "1 TB":   ["46.6 hrs", "23.3 hrs", "4.7 hrs", "2.3 hrs"],
    "10 TB":  ["19.4 days", "9.7 days", "1.9 days", "0.9 days"],
}
UNITS = {"min": 60, "hrs": 3600, "days": 86400}


def duration(size_gb, mbps, unit):
    megabits = size_gb * 1024 * 8
    return f"{megabits / mbps / UNITS[unit]:.1f} {unit}"


print(f"{'':8}" + "".join(f"{b:>11}" for b in BANDS_MBPS))
agree = True
for label, gb in SIZES_GB.items():
    unit = PUBLISHED[label][0].split()[1]
    cells = [duration(gb, mbps, unit) for mbps in BANDS_MBPS.values()]
    agree = agree and cells == PUBLISHED[label]
    print(f"{label:8}" + "".join(f"{c:>11}" for c in cells))
print("every cell equals the published figure:", agree)

# Part 2: DIU-hours when throughput scales with DIUs, and when something else caps it
SIZE_GB = 100
CAP_MBPS = 500          # the slowest of source, sink and network
PER_DIU_MBPS = 40       # ASSUMED throughput added by each DIU, for illustration only
print(f"\n{SIZE_GB} GB, cap {CAP_MBPS} Mbps, assumed {PER_DIU_MBPS} Mbps per DIU")
print(f"{'DIUs':>5} {'throughput':>11} {'duration':>10} {'DIU-hours':>10}")
for diu in (4, 8, 16, 32, 64):
    mbps = min(diu * PER_DIU_MBPS, CAP_MBPS)
    hours = SIZE_GB * 1024 * 8 / mbps / 3600
    print(f"{diu:5} {mbps:6} Mbps {hours * 60:6.1f} min {diu * hours:10.2f}")

            50 Mbps   100 Mbps   500 Mbps     1 Gbps
10 GB      27.3 min   13.7 min    2.7 min    1.3 min
100 GB      4.6 hrs    2.3 hrs    0.5 hrs    0.2 hrs
1 TB       46.6 hrs   23.3 hrs    4.7 hrs    2.3 hrs
10 TB     19.4 days   9.7 days   1.9 days   0.9 days
every cell equals the published figure: True

100 GB, cap 500 Mbps, assumed 40 Mbps per DIU
 DIUs  throughput   duration  DIU-hours
    4    160 Mbps   85.3 min       5.69
    8    320 Mbps   42.7 min       5.69
   16    500 Mbps   27.3 min       7.28
   32    500 Mbps   27.3 min      14.56
   64    500 Mbps   27.3 min      29.13

The table is the first thing to consult. If the link from an on-premises server is 100 Mbps, a terabyte takes about a day whatever you configure. The question tuning can answer is narrower: how close to the ceiling are you?

Three levels of parallelism

Copy scales at three levels, and the settings are easier to keep apart once they are named:

1. Several copy activities at once, typically from a ForEach. A ForEach that is not sequential runs up to 50 iterations at a time (Microsoft Learn: ForEach activity).
2. Compute for one copy activity. On the Azure integration runtime this is DIUs. On a self-hosted integration runtime it is the size and number of your machines.
3. Threads inside one copy activity, the parallel copies setting.

Data Integration Units

A DIU is a measure of the power of a single unit, a combination of CPU, memory and network allocation. It applies only to the Azure integration runtime (Microsoft Learn: performance features).

• Range. A copy run is allowed between 4 and 256 DIUs.
• Default. If you leave it on Auto, the service chooses based on the source, the sink and the data pattern.
• Billing. The charge is the number of DIUs used, times the copy duration, times the unit price per DIU-hour. The number used at run time is equal to or less than the number configured.

What the service will use depends heavily on the shape of the copy:

Read that table before touching the setting. One large file gets 4 DIUs however many you ask for. A load into Synapse through the COPY statement uses 2, because the warehouse engine does the work. A database source without a partition option gets 4. In all of these, a bigger number in the box does nothing.

Parallel copies

The parallelCopies property is the maximum number of threads inside the copy activity that read from the source or write to the sink in parallel. It is counted across all the DIUs or self-hosted nodes, and by default the service chooses it.

Its meaning changes with the scenario:

• Between file stores, it is parallelism at the file level. The number in use never exceeds the number of files, and merging into a single file removes file-level parallelism.
• From a database with a partition option, each thread runs one partition query. The default is 4.
• From a database with no partition option, to files, it does not take effect. Microsoft's wording is that even if parallelism is specified, it is not applied.
• Into Azure SQL Database or Azure Cosmos DB, the default also depends on the sink's tier.

For partitioned database sources Microsoft gives a rule of thumb: set the degree of parallelism to the number of DIUs, or self-hosted nodes, multiplied by 2 to 4, and note that too large a number can hurt (Microsoft Learn: SQL Server connector). More threads mean more load on the source and the sink. Data partitioning strategies covers the partition options that make this setting matter.

The two settings together, in a Copy activity definition:

{
  "name": "CopyOrdersToLake",
  "type": "Copy",
  "typeProperties": {
    "source": {
      "type": "SqlSource",
      "partitionOption": "PhysicalPartitionsOfTable"
    },
    "sink": { "type": "ParquetSink" },
    "dataIntegrationUnits": 16,
    "parallelCopies": 8
  }
}

Why more DIUs can cost more and gain nothing

The second part of the script is a model. It assumes each DIU adds 40 Mbps of throughput, an invented figure, and that something outside Data Factory caps the copy at 500 Mbps. Then it applies the billing formula.

From 4 to 8 DIUs the copy takes half the time and the DIU-hours are identical: 5.69. Speed was free. From 16 upward the cap is reached. The duration stays at 27.3 minutes and the DIU-hours double with each step, to 29.13 at 64 DIUs, five times the cost of the 8-DIU run for a run that is no faster than the 16-DIU one.

The numbers are illustrative; the shape is the point. While throughput scales with DIUs, raising them costs nothing extra. Past the bottleneck, every additional DIU that the service uses is paid for and idle.

Staged copy

With staging enabled, the copy first lands the data in Blob storage or Data Lake Storage Gen2 that you provide, then loads from there to the sink, and removes the temporary data afterwards. Microsoft lists the cases where it is useful:

• Loading Azure Synapse Analytics from sources that the bulk loaders cannot read directly. Loading data into Synapse covers this.
• Firewalls that allow only ports 80 and 443. A self-hosted runtime can stage over HTTPS, so port 1433 need not be opened to reach Azure SQL.
• Slow hybrid links. Data can be compressed before it leaves the premises.

You are billed for both stages. The same monitoring view will tell you when staging is not helping, and suggest removing it.

Self-hosted integration runtime

DIUs do not apply here. There are two ways to add capacity:

• Scale up, by raising the number of concurrent jobs a node may run, when its CPU and memory are not fully used.
• Scale out, by adding nodes, up to four, when CPU is high or memory is low. A single copy can spread across nodes when it reads many files or a partitioned database.

Microsoft also recommends hosting the runtime on a dedicated machine, separate from the database server. Integration runtimes explains the set-up.

Finding the real bottleneck

Every copy run has a monitoring view that splits the duration into stages. The longest stage is the bottleneck (Microsoft Learn: troubleshoot copy performance).

The view sometimes adds performance tuning tips, naming the bottleneck it has identified, for example a database at its capacity limit or throttling by a data store. Take them seriously; they are computed from the run.

One finding is easy to miss. A wildcard or last-modified filter makes the copy list every file in the folder and filter on the client. With a few new files in a large folder, listing can take longer than copying.

A method

Microsoft's tuning steps, condensed:

1. Take a baseline with a representative dataset large enough that the copy runs for at least 10 minutes, on default settings.
2. Maximise one copy activity. Read the stages, change one thing, run again.
3. Then run copies concurrently, for example with a ForEach over tables or folders, if the ceiling has not been reached. Control flow in ADF covers ForEach.
4. Then extend to the whole dataset, and use the measured numbers for capacity and cost planning.

For the activity itself, see the Copy activity guide.

Azure Data Factory and pipeline performance tuning are covered in the Data Engineering course.

Sources

• Copy activity performance and scalability guide (Microsoft Learn) — Microsoft Learn
• Copy activity performance optimization features (Microsoft Learn) — Microsoft Learn
• Troubleshoot copy activity performance (Microsoft Learn) — Microsoft Learn
• Copy and transform data to and from SQL Server (Microsoft Learn) — Microsoft Learn
• ForEach activity in Azure Data Factory and Azure Synapse Analytics (Microsoft Learn) — Microsoft Learn