I have an ADF pipeline, that logs start/end/failures via a stored procedure activity. It calls 4 different stored procedures, passing parameters, to then either insert or update a logging table so we can store the StartDateTime, EndDateTime and a Status (failed/passed).

We have been seeing instances of this activity, taking in the region of 2-3minutes, yet 'normally' it executes within < 10 seconds.

It is using the Azure Integration Runtime, and I have re-created a dummy pipeline to test, as you can see from the screenshot, one of the activities took 1 minute 45 seconds.

Is there anyway that I can workaround this issue?

I have an ADF pipeline, that logs start/end/failures via a stored procedure activity. It calls 4 different stored procedures, passing parameters, to then either insert or update a logging table so we can store the StartDateTime, EndDateTime and a Status (failed/passed).

We have been seeing instances of this activity, taking in the region of 2-3minutes, yet 'normally' it executes within < 10 seconds.

It is using the Azure Integration Runtime, and I have re-created a dummy pipeline to test, as you can see from the screenshot, one of the activities took 1 minute 45 seconds.

Is there anyway that I can workaround this issue?

• sql-server
• azure-data-factory

Share Improve this question Follow edited Mar 6 at 17:56 marc_s 757k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Mar 6 at 15:01 nowYouSeeMenowYouSeeMe 1,01766 gold badges1414 silver badges2222 bronze badges 4

• Is IR running in the same region as the targeted SQL Server? (i.e.: could there be communication problems in the network layer?) What does the stored procedure actually do? Have you tried using examining the stored procedure's cached query plan to see if the performance may be due to poor join performance? (i.e.: if index statistics are way off query planner may choose the wrong join types for some operations.) – AlwaysLearning Commented Mar 6 at 21:52
• So both are running in the same region, which is UK South. I did check the stored procedures query plan, but the stored proc is literally just doing either an INSERT or an UPDATE to the table. – nowYouSeeMe Commented Mar 7 at 15:44
• As you said the stored procedure is literally just doing either an INSERT or an UPDATE to the table so you need to Run the stored procedure directly in your SQL Server to determine its execution time without ADF and compare the execution time. – Pratik Lad Commented Mar 11 at 12:13
• Did the activity execute in <10 seconds but it just queued nearly 1m 35 secs? – Geezer Commented Mar 11 at 13:37

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

As the Stored Procedure is just doing INSERT and UPDATE, I have created similar procedure and used two types of Integration Runtime to compare the execution time.

In Case of Azure Integration Runtime ,Tuning the number of cores or choosing a correct memory-optimized configuration can affect performance.
Below setup can be done while creating Azure Integration Runtime:

Now after running the pipelines with two different IRs we are getting significance difference in execution time.

1. AutoResolveIntegrationRuntime
2. Azure Integration Runtime

Also Inside Pipeline Setting, Concurrency can be increased to improve the pipeline performance.

Kindly Go through the attached Microsoft Document for more reference:

Resource Allocation and Performance Tuning