I have a number of packages that are running Data Quality check for some of our customer data. At times, the DQS task will be stuck in one of the packages randomly. There is no pattern how it gets stuck. Sometimes restarting the SQL Server works, sometimes not.
When a task is stuck, the CPU usage in windows task manager is at 0%.
In the above, you can see that one task is stuck but the other is not.
Recently, i realized another interesting behavior. When a task is stuck, an another package is triggered to run concurrently, the stucked task gets unstucked.
This does not make logical sense to me so I am wondering if anyone faced issues with random hanging DQS Cleansing task in SSIS before.
Thank you!
For anyone who meets the same issue, when your SSIS task or DQS task gets stuck. Try reducing the memory allocated to SSMS or SQL server instead of increasing it. I am guilty of increasing the memory allocated to SQL server as I thought it will be allocating memory to the tasks triggered from there. However, the triggered SSIS tasks run in their own thread and own memory usage.
Reducing SQL server memory allocation cap now allowed my other tasks to run smoothly.