Long Queue Times on Data Factory SHIR for Stored Procedure Activity

Question

I have an Azure Data Factory that uses a self-hosted integration runtime to import and update data in Azure SQL Managed Instance. The SHIR and MI are on the same VNet. The SHIR is on a VM with 8vcores and 16 GB RAM. The ADF is using the Managed VNet offering for the Azure IR (not sure that is relevant since I'm using SHIR, but noting for completeness).

I have a parent pipeline with a for each loop that calls a child pipeline. In my child pipeline, I have the following: lookup -> copy activity -> stored proc. The sink on the copy activity is the same MI but different DB from the stored proc. But I'm consistently seeing long queue times on the stored proc. The stored proc itself is very simple.

I can see that this stored proc activity frequently takes 43 seconds and 40 of that is queueing. This proc runs about 250 times every 30 minutes. I have monitored the SHIR, and while CPU does occasionally max out at the very beginning of a run, it is not at maximum capacity while this stored proc activity is occurring (CPU gets high at the very beginning of the process and then drops down). There's plenty of memory, so I know that isn't an issue. Usually when I check monitoring while the activity is executing, it shows 5 - 10% CPU, > 10 GB available memory, plenty of job slots available.

The only relevant advice I see for tuning an SHIR is to make sure it has enough CPU and memory and that there are concurrent job workers available. And there are. All other advice I have found is related to copy activities or data flows.

What can I do to troubleshoot the long queue times before the stored proc activity?

Answer 1

We had a similar issue, seeing every task in the foreach had very long queue times, ~40s. Changing the number of concurrent jobs in the SHIR settings fixed the problem: https://learn.microsoft.com/en-us/azure/data-factory/create-self-hosted-integration-runtime?tabs=data-factory#scale-up

The default is 6, which we changed to the max of 24. Still plenty of CPU and memory left on the server and run times at the task level went from ~1:30 to 7s.

Hope this is helpful for anyone looking at the same issue.

Answer 2

Hello @MeaganL ,
Thanks for the ask and using Microsoft Q&A platform .
Let me also thanks you for providing all the details which really helps .
I am assuming that you are setting the "isSequential" = False in the for each activity , let me know if its not corrrect . To me at this time it is very difficult to predicte if the bottlenect is at the SHIR side or SQL side , I am more inclined to believe that its on the SHIR side , but there is not easy way to find that out . I suggest to

• Set "isSequential" = True and see how it behaves , i am confident there should not be any queue now .
• Set "isSequential" = False and play with the batch count setting and see which if the queue time is reduced .

ideally if you can process the data in batches it will be better rather then doing it individually . yes that may require to update the SP logic .

Please do let me know how it goes .
Thanks
Himanshu

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

• Please don't forget to click on or upvote 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

Answer 3

Hi

MeaganL-8918,
DId you find the possible solution for this queued state problem ?

Answer 4

No, we ended up just working arround it.

Answer 5

Worked around it how ?

Can you please guide me how ?