Share via

Azure Data Factory Looging to Azure SQL DB

Martin Rendell 1 Reputation point
2021-12-14T13:36:01.067+00:00

We are developing an ADF solution which is metadata driven, it passes connection string and source and sink details as parameters.

Logging to an Azure SQL Database via a lookup task within child and parent pipelines has been implemented, however, for a simple Azure DB table copy into ADSL Parquet it is bottlenecked by the logging steps.

We noted that each step (mainly logging steps) takes around 6 seconds but this is frequently much longer.

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

Set up uses Azure Hosted Managed Vnet with private endpoints to all Azure services.

We have tried the following which have had no effect;
Upgrading the Config Database from basic to S3
Changing the ADF's integration runtime to 32 core count,
Changing the DIU, however, the copy activity will max at 4
Replacing lookup task for Stored Procedure task (this impacts the ability to use output)

The audit step calls a Stored Procedure with parameters and runs in split seconds in SSMS.

Any advice appreciated.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,196 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. HimanshuSinha-msft 19,386 Reputation points Microsoft Employee
    2021-12-15T21:22:42.85+00:00

    Hello @Martin Rendell ,
    Thanks for the ask and using Microsoft Q&A platform and big thanks for what you have tried till now .
    To me using Lookup for logging ( I am assuming you are inserting records in a SQL table ) is not the right activity for the job . Lookup activity designed to have a datset in return and to me thats looks to be the reason for this delay . I suggest you to use Store procedure activity for the task .
    More on Lookup : https://learn.microsoft.com/en-us/azure/data-factory/control-flow-lookup-activity#supported-capabilities

    157958-image.png

    Please do let me know how it goes .
    Thanks
    Himanshu

    -------------------------------------------------------------------------------------------------------------------------

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators