locked
13 GB data in a single file in data lake store transfer to SQL server table using azure data factory performance issue RRS feed

  • Question

  • hi all,

    i have single file 13 gb in a single file in azure data lake gen2.

    we are using azure data factory to copy this file from data lake to azure sql table.

    issue - performance is not good, throughput is too slow in azure data factory

    how much dtu is required to transfer data.

    Thursday, April 9, 2020 9:26 AM

Answers

  • Thank you for this additional detail, Manish. Yes, I would say that the IR instance is CPU bound. Please increase the service tier to a point where this VM is not throttled. 

    Regards,

    Mike

    • Marked as answer by manish0000 Wednesday, May 6, 2020 2:57 PM
    Monday, April 13, 2020 4:02 PM
  • hi all,

    this will help if we increase VM size where you install IR- IR nothing but compute.

    so if you have heavy lifting- increase VM size.

    • Marked as answer by manish0000 Wednesday, May 6, 2020 2:57 PM
    Wednesday, May 6, 2020 2:57 PM

All replies

  • hi all,

    this is urgent case we need to solve, if any body from microsoft will help us.

    current dtu s4- 800.

    Thursday, April 9, 2020 9:59 AM
  • hi all,

    i think this is limitation of pass based SQL, if i took same in azure vm it will work fine.

    i need expert suggestion move from pass to vm, we can't run all time premium tier.

    Friday, April 10, 2020 6:40 AM
  • Hi Manish,

    Can you please detail the specific service tier you have provisioned for your database. You mention S4 but also reference 800. S4 is 200 DTU and S7 is 800 DTU. So, it would be helpful to understand specifically what you have setup. It is highly suggested that you increase your service tier to a more performant instance to accomplish the data loading activities followed by scaling back down to a service tier that supports your daily workloads.

    Can you please use the following reporting tools to monitor and diagnose where the bottleneck is at, so that you have better insight to your loading activities:

    To monitor Azure SQL Database: Query Performance Insight for Azure SQL Database

    To monitor Azure Data Factory: Troubleshoot copy activity performance

    I realize this is an urgent issue and if you are really in a bind, I suggest you step-up to S9 (1600 DTU) while also leveraging the Azure Data Factory copy activity performance to identify any copy activity issues that are not necessarily performance related.

    Look forward to your additional detail and questions to help you succeed. 

    Regards,

    Mike

    Friday, April 10, 2020 6:25 PM
  • thanks for quick help,

    S9 (1600 DTU)- we already complete job  3 days back.

    question is why 13 gb data not copy from adls to to a single table, on 800 DTU.

    if i copy from adls to adls it working fine.

    is 800 DTU not sufficient to transfer data from adls gen2 in same network same location, both component.

    can Microsoft test this copy 13 gb data from adls to azure sql in saingle db, and share this result.

    or Microsoft   say   800 DTU is not sufficient to transfer 13 gb data from adls to Azure SQL single table.

    Saturday, April 11, 2020 6:59 AM
  • hi,

    i have found one thing, IR is install in VM (3.5 gb ram)-Standard DS1 v2 (1 vcpus, 3.5 GiB memory)

    cpu utilization 100 %, is this is one of reason performance is dowen

    Monday, April 13, 2020 12:11 PM
  • Thank you for this additional detail, Manish. Yes, I would say that the IR instance is CPU bound. Please increase the service tier to a point where this VM is not throttled. 

    Regards,

    Mike

    • Marked as answer by manish0000 Wednesday, May 6, 2020 2:57 PM
    Monday, April 13, 2020 4:02 PM
  • Hi Manish,

    Were you able to get this issue resolved? It would be super helpful if the root cause to this issue was made available and how it was resolved. It helps others who are potentially experiencing the same complications. 

    Regards,

    Mike

    Monday, April 27, 2020 4:08 PM
  • hi all,

    this will help if we increase VM size where you install IR- IR nothing but compute.

    so if you have heavy lifting- increase VM size.

    • Marked as answer by manish0000 Wednesday, May 6, 2020 2:57 PM
    Wednesday, May 6, 2020 2:57 PM