Lookup Activity in ForEach in ADF using batchcount shows most activities with the same duration while they should all be different.

Aart Hopman 0 Reputation points
2026-10-08T09:37:36.0166667+00:00

For one of my customers I have a pipeline that executes lots of stored procedures in an Azure SQL database using a ForEach with a batch count of 50. The only activity is a LookUp with a variable stored procedure (and parameters). I used to be able to check the duration of each of the lookup activities to determine which of them took the most time. But that is not possible anymore due to the fact that most of them have the same duration:image

I am used to the fact that a foreach with a batch count of 50 does not wait till all 50 have completed before the next activities are started. It used to be if 1 finishes that the next one in the queue will be executed. Now it is hard to analyze which of the Stored Procedures needs attention regarding performance.

Has anything changed in the UI or the behavior of the foreach?

regards Aart

Azure Data Factory
Azure Data Factory

An Azure service for ingesting, preparing, and transforming data at scale.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Salamat Shah 830 Reputation points MVP
    2026-10-08T10:19:37.6766667+00:00

    The screenshot showing many Lookup activities at approximately 52m 54s and another group around 10m is consistent with orchestration/parallel scheduling rather than proof that all stored procedures took exactly the same time. For performance troubleshooting, use SQL-side execution metrics as the source of truth, not the ADF activity duration alone.

    Recommendations:

    • Do not use the Lookup activity's displayed ADF duration as the stored-procedure execution time. The activity duration can include scheduling/waiting/orchestration overhead.
    • Measure SQL execution directly for performance analysis, for example with Azure SQL Query Store or other database-side monitoring. This gives the actual stored-procedure/query execution duration.
    • If accurate workload distribution is important, consider smaller batches, multiple pipeline executions, or explicitly partitioning the procedures rather than relying on a single ForEach with batchCount = 50.
    • Check the Integration Runtime and Azure SQL concurrency/resource limits, because these can further restrict effective parallelism.

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.