I have this legacy Job that runs every 5 minutes (inside it has a SP that is doing a MERGE).

It ran perfectly for the last couple of years… But the issue started when the SOURCE for the MERGE changed from 1,000 rows to 200,000 rows…

Did anyone go through something similar? This job used to take at most 2 minutes… Now sometimes it takes 2,3 hours to run…

Below a screenshot of the execution plan for the biggest merge (the SP has in total has 4 merges…) I try to use the execution plan to troubleshoot, but it was misleading… As even though it says (relative to the batch)= 0% this is the portion of the code that takes 90% of the time.
[![d](https://i.stack.imgur.com/PIOyC.jpg)](https://i.stack.imgur.com/PIOyC.jpg)