Upsert in copy activity taking long time.

NAGA SANDEEP KUMAR KAPA 51

Hi Team,

I have a pipeline in which we are using copy activity with upsert functionality with sink as Azure SQL DB , it is running dead slow. It is working well in dev environment it took only 2.5 mins for 70k records whereas in acceptance it is about 191k records it took 2 hours for 30k records before i cancelled . Nothing has changed in the sink , same configuration.

How can I know where is the bottleneck. I can see in detail section writing to sink is taking long time. But how to reduce the timing.

I have tried removing index it is working but upsert functionality is not working and also I should not change the sink. How can I resolve this. Thanks in advance

Thanks,
Sandeep

BhargavaGunnam-MSFT 26,316 Reputation points Microsoft Employee

2022-09-05T20:11:29.65+00:00
Hello @NAGA SANDEEP KUMAR KAPA ,

Welcome to the MS Q&A platform.

Please run the below query on your Azure SQL DB when you are running the pipeline in your acceptance environment. This query will give you the list of processes that are in the suspended list along with other details like wait_type, wait_duration, Blocking session, query plan, and program that is executing the process.

This will give you a basic idea of where exactly your query is having issues by examining the wait types and you can take the necessary actions accordingly to overcome the issues.

Please note: you can start the troubleshooting using this method as running the above query will not have an impact on the system.

SELECT owt.session_id, owt.exec_context_id, owt.wait_duration_ms, owt.wait_type, owt.blocking_session_id, owt.resource_description, est.text, es.program_name, eqp.query_plan, es.cpu_time, es.memory_usage FROM sys.dm_os_waiting_tasks owt INNER JOIN sys.dm_exec_sessions es ON owt.session_id = es.session_id INNER JOIN sys.dm_exec_requests er ON es.session_id = er.session_id OUTER APPLY sys.dm_exec_sql_text (er.sql_handle) est OUTER APPLY sys.dm_exec_query_plan (er.plan_handle) eqp WHERE es.is_user_process = 1 ORDER BY owt.session_id, owt.exec_context_id

Please see this document explaining about how to troubleshoot using Wait stats.
I hope this helps. Please let me know if you have any further questions.
NAGA SANDEEP KUMAR KAPA 51 Reputation points

2022-09-06T13:50:17.27+00:00

Hi ,

Thanks for the reply. I have used that query but I am getting only one result with wait_type as XE_LIVE_TARGET_TVF, and program_name as TdService , cpu time and memory usage as 0 .

Suggested solution in the given document for wait type is safe to ignore.

How can I reduce the time now.

One more thing I have observed is surprisingly when I tried with Debug option instead of trigger now/scheduled trigger it took less time of approx around 3 mins.

I have tried again by trigger now option after truncating the data in target. Again it is very slow.

Could you please assist in this.
Note: I have tried with different databases as well.

Regards,
Sandeep
BhargavaGunnam-MSFT 26,316 Reputation points Microsoft Employee

2022-09-06T23:28:52.977+00:00

Hello @NAGA SANDEEP KUMAR KAPA ,

Thanks for the reply.

Did you run that query while running your pipeline with the trigger run? You should see some wait types on your Azure SQL end if your pipeline is running for a long time.

I am suspecting this could be due to blocking, locking, or long-running waits.

It's hard to tell what is causing the issue without looking at your resources. I would suggest you file a support request and a support engineer can take a deeper look to troubleshoot the issue further.

In case, if you don't have a support plan, I can enable one-time free support for you to work closely on this matter. Please let me know.
NAGA SANDEEP KUMAR KAPA 51 Reputation points

2022-09-07T03:16:54.147+00:00

Hi ,

Yes i have ran that query while pipeline is running with trigger. I can see only that wait type. Records are inserting slowly. Ran query multiple times.

Ok sure will raise a ticket and let you know about it.

Regards,
Sandeep.
BhargavaGunnam-MSFT 26,316 Reputation points Microsoft Employee

2022-09-07T19:29:00.83+00:00

Hello @NAGA SANDEEP KUMAR KAPA ,
Thank you so much for the update.
Once you get a solution from the support, could you please consider sharing the same here so that other community members can benefit from that?
BhargavaGunnam-MSFT 26,316 Reputation points Microsoft Employee

2022-09-15T21:12:15.777+00:00

Hello @NAGA SANDEEP KUMAR KAPA ,
I am just checking in to see if you get a resolution from the support.
if you have a resolution, request you to share the same here, so that other community members can benefit from that
NAGA SANDEEP KUMAR KAPA 51 Reputation points

2022-09-16T02:41:14.073+00:00

Hi Bhargav,

Issue is not yet resolved. Db team is checking the issue. Will update once they gave any solution.

Regards,
Sandeep
BhargavaGunnam-MSFT 26,316 Reputation points Microsoft Employee

2022-09-16T14:34:34.38+00:00

Thank you, @NAGA SANDEEP KUMAR KAPA

1 answer

Syed Ali 5 Reputation points

2023-06-05T21:33:21.4433333+00:00

Could someone please advise. I have same issue and it is taking too long.
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment