How to improve time to execute logging stored procedure in Azure Data Factory?

user_17666976 0
Viewed 317

I have an ADF solution which is metadata driven. It passes a connection string, and source and sink as parameters. My concern is that I also have SQL logging steps within pipelines and child pipelines and now for a simple Azure DB table copy into ADSL Parquet it is bottlenecked by the logging steps and child pipelines. I noticed that each step (mainly logging steps) take around 3-6 seconds.

I have tried the following:

  • upgrading the config database from basic to S1
  • changing the ADF's integration runtime to 32 core count
  • changing the TTL to 20 mins
  • checked the quick cache

Nothing seems to reduce the time to run these audit steps.

The audit step is a stored procedure which you pass in a load of parameters. This proc run in split seconds in SSMS so the proc isn't the issue.

Is there any way of reducing the time to execute logging steps?

azure-data-factory-pipeline azure-data-factory-2 azure-data-factory sql-server
2 Answers

As per the Microsoft SLA for ADF, they guarantee that at least 99.9% of the time, all activity runs will initiate within 4 minutes of their scheduled execution times.

As stated from the Product Team, any stored procedure activity that performs within 4 minutes has met SLA within ADF--this SLA covers the overhead of ADF communicating with SQL server. With that said, your current performance within ADF is normal.

Check the supported documents:

  1. https://azure.microsoft.com/en-gb/support/legal/sla/data-factory/v1_2/

  2. https://docs.microsoft.com/en-us/answers/questions/36323/adf-performance-troubleshooting.html

user_159690410

I tend to think adding excessive logging into pipelines is a bit of an anti-pattern as it leads to more complex pipelines with more components to maintain. You can also encounter this type of issue, particularly if you're running the logging inside a For Each activity for example. I tend to use this sparingly and concentrate more on the built-logging and making use of the custom user properties and annotations. Most of the information you need should be in the built-in logging which you can harvest via API calls either at the end of your pipelines or elsewhere, as ably described here and here.

The other thing you could do is move any non-critical tasks to run in parallel. For example a 'log start of activity' task does not necessarily need to run first sequentially. Consider making some of these tasks non-parallel, as explained in the diagram below. Obviously this does not apply when you need to capture information from the activity to log:

sequential v parallel tasks

user_15275040
Related