Share via

Copy activity output entities and Query activity of ADW

Priya Jha 871 Reputation points
2020-11-06T14:15:41.207+00:00

HI All,

We have a pipeline that contains copy activity which runs query on Azure SQL DB and generates a blob file. PFB screenshot of copy activity output

38083-copydata.png

1) Wanted to know how Copy duration mentioned in the output is generated. Is it combination of Queue and Transfer details?

2) Under transfer details there are 3 entities named Time to first byte, Reading from source and Writing to sink. What does each entity indicates?

3) Out of these 3 entities which entity can define the exact time of query run on Azure SQL DB when copy activity starts running?

We have another pipeline, PFB screenshot of copy activity output:

38084-othercopy.png

1) If Copy duration is combination of Queue and Transfer details, then in this pipeline why there is discrepancy of 1 sec as Copy duration is 6 sec and Queue and Transfer details sums up to 5 sec

2) In the output screenshot, all the 3 entities under Transfer details is 0 but the output is shown as 4 sec. How this data was generated?

Also, whenever a copy activity is transferring data to or from Azure DW, no logs is present for this copy activity transfer in query activity even when the time for data transfer is more than half and hour. From where we can check if any copy activity is running on DW.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,692 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,182 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. HarithaMaddi-MSFT 10,136 Reputation points
    2020-11-09T13:34:34.523+00:00

    Hi @Priya Jha ,

    Welcome to Microsoft Q&A Platform. Thanks for posting the queries.

    Wanted to know how Copy duration mentioned in the output is generated. Is it combination of Queue and Transfer details?

    No, its not a combination of these two alone. There are lot of other factors that will be added to the total duration in addition to Queue and Transfer details Ex: wait time in raising the request to hit the source.

    Under transfer details there are 3 entities named Time to first byte, Reading from source and Writing to sink. What does each entity indicates?

    Please go through this document that explains in detail on these three entities.

    Out of these 3 entities which entity can define the exact time of query run on Azure SQL DB when copy activity starts running?

    As mentioned in the above document, "Transfer - Time to first byte" experienced long working duration means your source query takes long to return any data. Check and optimize the query or server.

    In the output screenshot, all the 3 entities under Transfer details is 0 but the output is shown as 4 sec. How this data was generated?

    Total duration means the overall duration for each steps and working duration doesn't include all components now which may lead to confusion. E.g. the serialization time is not included in working duration.

    From where we can check if any copy activity is running on DW.

    Please refer this document that calls out all DMVs that can be used to track queries. As shown below, I can see ADF query in Synapse DWH.

    38353-image.png

    Hope this helps! Please suggest for specific queries for any run by sharing pipeline run id details and we will be glad to assist.

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

    • Please accept an answer if correct. 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.